sobota, 18 października 2008

Kolejność robi różnicę

Pisząc kwerendy w T-SQL bardzo często decyzja na temat optymalizacji zapytań pozostawiana jest optymalizatorowi. Prawdopodobnie w 9 na 10 przypadków optymalizator wybierze odpowiedni plan wykonania, dzięki któremu zapytanie wykona się w zadowalającym czasie.

Tak jest prawdopodobnie w 9 na 10 przypadków. Jednakże od każdej reguły są wyjątki, z którymi optymalizator póki co sobie nie radzi.


Jednym z takich wyjątków jest kolejność predykatów. Zademonstruje to na przykładzie.
Na początek utworzę funkcje, która będzie miała zauważalny czas wykonania.


CREATE FUNCTION dbo.usn_waitfor()
RETURNS INT
AS
BEGIN
DECLARE @i INT
SET @i = 1
WHILE @i < 10000000
BEGIN
SET @i = @i + 1
END
RETURN 1
END
GO


Funkcja ta zwraca wartość 1, jednakże zanim to zrobi inkrementuje 10000000 razy wartość zmiennej @i. Naturalnie funkcja ta mogłaby wykonywać każdą inną czynność, np. odczytywać dane z kilku tabel czy wykonywać operacje arytmetyczne.

Wykorzystajmy teraz tę funkcję w zapytaniu. Na początek włączę jednakże trochę statystyk.

SET STATISTICS TIME ON

W celu demonstracji potrzebna będzie jeszcze jedna zmienna.

DECLARE @i INT
SET @i = 1


A teraz bardzo proste zapytanie.

SELECT 1
WHERE @i = 1 OR dbo.usn_waitfor() = 0


Wykonując to zapytanie wynik otrzymany jest natychmiast. Statystyki w tym przypadku nic nie wykazały.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

Teraz to samo zapytanie, jednakże ze zmienioną kolejnością predykatów. Najpierw będzie sprawdzany wynik funkcji a później wartość zmiennej.

SELECT 1
WHERE dbo.usn_waitfor() = 0 OR @i = 1


Wynik zwrócony przez to zapytanie jest taki sam jak w poprzednim przypadku, natomiast czas jaki upłynął aby go otrzymać już nie. W moim przypadku na drugie zapytanie serwer potrzebował 18 sekund. Poniżej trochę statystyk.

SQL Server Execution Times:
CPU time = 12657 ms, elapsed time = 17921 ms.


Skąd więc taka różnica pomiędzy czasami wykonania obu zapytań? Otóż różnica te leży właśnie w kolejności sprawdzania predykatów. Aby nasz warunek był prawdziwy, i zapytanie zwróciło wynik, przynajmniej jeden predykatów musi być prawdziwy. W pierwszym przypadku najpierw sprawdzona została wartość zmiennej, i warunek @i = 1 był prawdziwy. Jako, że przynajmniej jeden z warunków musi być prawdą, drugi z warunków nie musiał być już sprawdzany.

W przypadku drugiego zapytania to funkcja, która potrzebuje trochę czasu aby została wykonana, brała udział w pierwszym warunku. Dopiero po jej wykonaniu sprawdzany był kolejny warunek, który zwraca prawdę, dzięki czemu otrzymany został wynik zapytania.

Jak widać warto czasami pomyśleć samemu i nie zdawać się na łaskę optymalizatora. Zmienienie kolejności predykatów może mieć kolosalne znaczenie dla czasu w jakim wykonane zostanie zapytanie.

Prześlij komentarz