Spis treści:

Kategoria:SQL Server


Jak wymusić stosowanie konkretnego indeksu w SQL Server

SQL Server wykonuje naprawdę kawał dobrej i najczęściej potrzebnej roboty obmyślając plany wykonania poszczególnych operacji. My piszemy zapytanie, on musi wymyślić, w jaki sposób to wszystko połączyć i pofiltrować. Co więcej - musi tak to rozpracować, żeby sposób wyliczania był jak najbardziej optymalny. Ktoś mógłby zakrzyknąć: "A niechajże liczy, tyle ma do roboty, po to jest on!". Jest to zdrowe podejście, bo rzeczywiście po to on jest, po to jest on! Co jednak wtedy, gdy zrobi się nam go żal? Co wtedy, gdy uznamy, że biedak musi myśleć i nic z tego nie ma, a nas zaczyna gryźć sumienie? Możemy mu podpowiedzieć jak realizować konkretne zapytanie! Niech ma lżej!

Przekonajmy się zatem jak to wszystko działa i przygotujmy sobie niewielkie pole do popisu. Będzie to skromna tabela z dwoma kolumnami typu int wypełniona niewielką ilością rekordów. Poniżej znajduje się skrypt:

--tworzymy tabelę
CREATE TABLE T1
(
  A int,
  B int
)

--wypełniamy danymi
SET NOCOUNT ON
BEGIN TRANSACTION
DECLARE @i int = 0
WHILE @i<5000
BEGIN
  INSERT INTO T1 VALUES (@i,@i)
  SET @i = @i + 1
END
COMMIT

Plany wykonania będziemy testować bardzo prostym skryptem (będzie on nieznacznie modyfikowany):

SELECTFROM T1 WHERE A=5 AND B=5

Wszystko idzie gładko, ale żeby SQL Server mógł wybierać między dwoma różnymi indeksami musi być spełniony szalenie ważny warunek. Przede wszystkim muszą być dwa indeksy! Poniżej znajduje się odpowiedni skrypt:

CREATE INDEX IX_A ON T1(A)
CREATE INDEX IX_B ON T1(B)

A teraz część właściwa, czyli odpowiedź na pytanie, w jaki sposób zmusić SQL Server do skorzystania z konkretnego indeksu. Wystarczy skorzystać ze składni WITH(INDEX(MyIndex)). Ten fragment skryptu umieszcza się po nazwie tabeli w następujący sposób:

SELECTFROM T1 WITH(INDEX(IX_A)) WHERE A=5 AND B=5

Wymuszanie indeksu przykład 1
Rys. 1. Przykład wymuszenia indeksu IX_A.

A teraz spróbujmy skorzystać z drugiego indeksu:

SELECTFROM T1 WITH(INDEX(IX_B)) WHERE A=5 AND B=5

Wymuszanie indeksu przykład 2
Rys. 1. Przykład wymuszenia indeksu IX_B.

Jak widać na załączonych zrzutach, SQL Server dość sprawnie radzi sobie z naszymi podpowiedziami. Bez zająknięcia się korzysta dokładnie z tych, które mu wskazaliśmy.

Na koniec bardzo ważna uwaga. SQL Server bardzo sprawnie wylicza plany wykonania i zwykle wybiera najlepszy. Osobiście odradzam wymuszanie użycia konkretnego indeksu. Pomimo tego, że może on chwilowo działać lepiej, to zmiany w strukturze danych, rozkładach statystycznych wartości i rozmiarów tabel mogą sprawić, że wymuszony indeks nie będzie już najlepszym rozwiązaniem. Bardzo łatwo zapomnieć, że gdzieś, kiedyś w zapytaniu użyliśmy WITH(INDEX(KiedysTenIndexDzialalNajlepiej)). Wymuszanie użycia indeksów powinno być rozważane jako jedna z ostatnich opcji optymalizacji i tylko przez doświadczonych programistów oraz administratorów baz danych.

Kategoria:SQL Server

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !