Jest to próba usprawnienia pracy Maxa Vernona . W swoim rozwiązaniu sugeruje użycie 2 indeksów w widoku i obiektu statystycznego.
Pierwszy indeks jest klastrowany, co jest faktycznie wymagane, ponieważ w przeciwieństwie do indeksu nieklastrowanego w tabeli, błąd zostanie wygenerowany, jeśli spróbuje się utworzyć indeks nieklastrowany w widoku bez uprzedniego posiadania indeksu klastrowanego.
Drugi indeks jest indeksem nieklastrowanym, który jest używany jako indeks kwerendy. W sekcji komentarzy jego odpowiedzi zapytałem, co by się stało, gdyby zamiast indeksu nieklastrowanego użyto indeksu klastrowego.
Poniższa analiza próbuje odpowiedzieć na to pytanie.
Używam jego dokładnie tego samego kodu, z tym wyjątkiem, że nie tworzę indeksu nieklastrowanego w widoku.
Nie tworzę też obiektu statystycznego. Jeśli śledzisz i używasz programu SQL Server Management Studio (SSMS), aby wprowadzić poniższy kod, powinieneś być świadomy, że mogą pojawić się pewne czerwone linie - które wyglądają jak błędy. Nie są to (prawdopodobnie) błędy, ale dotyczą problemu z intellisense.
Możesz albo wyłączyć intellisense, albo po prostu zignorować błędy i uruchomić polecenia. Powinny zostać wypełnione bez błędów.
-- Create the test table that uses a computed column.
USE tempdb;
CREATE TABLE dbo.PersistedViewTest
(
PersistedViewTest_ID INT NOT NULL
CONSTRAINT PK_PersistedViewTest
PRIMARY KEY CLUSTERED
IDENTITY(1,1)
, SomeData VARCHAR(2000) NOT NULL
, TestComputedColumn AS (PersistedViewTest_ID - 1) PERSISTED
);
GO
-- Insert some test data into the table.
INSERT INTO dbo.PersistedViewTest (SomeData)
SELECT o.name + o1.name + o2.name
FROM sys.objects o
CROSS JOIN sys.objects o1
CROSS JOIN sys.objects o2;
GO
Następujący plan wykonania (bez widoku / widoku indeksu) jest tworzony po uruchomieniu następującego zapytania dla tabeli:
SELECT pv.PersistedViewTest_ID, pv.TestComputedColumn
FROM dbo.PersistedViewTest pv
WHERE pv.TestComputedColumn = CONVERT(INT, 26)
GO
Daje to podstawę do porównania. Zauważ, że po zakończeniu zapytania utworzono obiekt statystyki (_WA_Sys_00000003_1FCDBCEB). Obiekt statystyki PK_PersistedViewTest został utworzony podczas tworzenia indeksu tabeli klastrowej.
Następnie tworzony jest filtrowany widok i indeks klastrowy w tym widoku:
-- Create filtered view on the computed column.
CREATE VIEW dbo.PersistedViewTest_View
WITH SCHEMABINDING
AS
SELECT PersistedViewTest_ID, SomeData, TestComputedColumn
FROM dbo.PersistedViewTest
WHERE TestComputedColumn < CONVERT(INT, 27);
GO
-- Create unique clustered index to persist the values, including the computed column.
CREATE UNIQUE CLUSTERED INDEX IX_PersistedViewTest
ON dbo.PersistedViewTest_View(PersistedViewTest_ID);
GO
Teraz spróbujmy ponownie uruchomić zapytanie, ale tym razem w widoku:
SELECT pv.PersistedViewTest_ID, pv.TestComputedColumn
FROM dbo.PersistedViewTest_View pv
WHERE pv.TestComputedColumn = CONVERT(INT, 26)
GO
Nowy plan wykonania jest teraz:
Jeśli wierzyć nowemu planowi, po dodaniu widoku i indeksu klastrowego w tym widoku statystyki wydają się wskazywać, że czas wymagany do wykonania zapytania podwoił się. Zauważ też, że po uruchomieniu zapytania nie został utworzony nowy obiekt statystyczny do obsługi nowego indeksu, który różni się od zapytania w tabeli.
Plan zapytań nadal sugeruje, że utworzenie nieklastrowanego indeksu byłoby bardzo pomocne w poprawie wydajności zapytania. Czy to oznacza, że do widoku należy dodać indeks nieklastrowany, aby uzyskać pożądaną poprawę wydajności? Jest jeszcze jedna ostatnia rzecz do wypróbowania. Zmodyfikuj zapytanie, aby użyć opcji „WITH NOEXPAND”:
SELECT pv.PersistedViewTest_ID, pv.TestComputedColumn
FROM dbo.PersistedViewTest_View pv WITH (NOEXPAND)
WHERE pv.TestComputedColumn = CONVERT(INT, 26)
GO
Powoduje to następujący plan zapytań:
Ten plan wykonania wygląda dość podobnie do tego, który został utworzony z indeksem nieklastrowanym podanym w odpowiedzi Maxa Vernona. Ale ten jest wykonywany z jednym mniejszym (nieklastrowanym) indeksem i jednym mniejszym obiektem statystycznym.
Okazuje się, że opcji NOEXPAND należy używać z ekspresową i standardową wersją SQL Server, aby właściwie wykorzystać widok indeksowany. Paul White ma znakomity artykuł, który wyjaśnia korzyści płynące z używania opcji NOEXPAND. Zaleca także, aby tę opcję stosować z wersją Enterprise, aby zapewnić, że optymalizator zastosuje gwarancję unikalności zapewnianą przez indeksy widoków.
Powyższą analizę wykonano z ekspresową wersją SQL Sever 2014. Próbowałem również z edycją programistyczną SQL Server 2016. Opcja NOEXPAND nie wydaje się być wymagana w przypadku wersji rozwojowej do osiągnięcia wzrostu wydajności, ale nadal jest zalecana .
Niecałe 5 miesięcy temu Microsoft udostępnił wersje programistyczne za darmo . Licencja ogranicza użycie wyłącznie do programowania, co oznacza, że bazy danych nie można używać w środowisku produkcyjnym. Tak więc, jeśli chciałeś przetestować tabele zoptymalizowane pod kątem pamięci, szyfrowanie, R itp., Nie masz już wymówki bez licencji. Z powodzeniem zainstalowałem go na moim komputerze kilka dni temu obok SQL Server 2014 Express bez żadnych problemów.
WHERE (sintMarketID = 2 AND strType = 'CARD' AND strTier1 LIKE 'GG%').