SQLGEEK

[PL] Przypadki optymalizacji. Odc. 1: Raz, a dobrze

W codziennej pracy z SQL Serverem dość często zdarzają się zaskakujące sukcesy na polu optymalizacji zapytań. Postanowiłem co jakiś czas dzielić się spostrzeżeniami w tym temacie – stąd początek sequela (hehe) pt. "Przypadki optymalizacji". Zgaduję, że część zagadnień i problemów opisanych w odcinkach będzie dość oczywista, ale kto wie. A przynajmniej liczę na to, że wiele osób mających na co dzień, podobnie jak ja, zadania optymalizacyjne do wykonania, podzieli się w ramach dyskusji na temat prezentowanych rozwiązań swoimi doświadczeniami i przemyśleniami. A więc do dzieła.

Weźmy następujący banalny kawałek kodu zawierający dwa zapytania do bazy AdventureWorks (lub AdventureWorks2008):

USE AdventureWorks
GO

SET STATISTICS IO ON
GO

SELECT *
FROM Production.ProductInventory
WHERE Quantity = dbo.ufnGetStock(1)

SELECT *
FROM Production.ProductInventory
WHERE Quantity = (SELECT dbo.ufnGetStock(1))
GO

SET STATISTICS IO OFF
GO

Z pozoru zapytania nie różnią się specjalnie między sobą. Ot, szukamy informacji o rozmieszczeniu produktów w magazynie, a konkretnie takich pozycji, w których ilość produktu jest taka, jak ilość zwracana przez funkcję ufnGetStock dla produktu o ProductID równym 1. Banał.

Pierwsza chwila refleksji przychodzi, gdy oba zapytania uruchomimy osobno. Pierwsze wykonuje się w zauważalnym czasie (od 3 do 6 sekund). Drugie śmiga w czasie poniżej 1 sekundy. Warto dodać, że statystyki I/O pokazują, że oba zapytania czytają po 9 stron (oba tyle samo).

Obejrzenie planów wykonania nie przynosi nam odpowiedzi na pytanie, czemu drugie zapytanie wykonuje się krócej. Ba, nawet może wprawić nas w konsternację, bo plan drugiego zapytania może nam się wydać gorszy.

Przy okazji po raz kolejny mamy dowód na to, że Query cost nie jest dla nas wskaźnikiem, który powinniśmy brać pod uwagę przy optymalizacji zapytań (a przynajmniej na pewno nie w pierwszej kolejności). Dokładniejsze przyjrzenie się temu planowi może nam zasugerować, co tak naprawdę sprawiło, że pierwsze zapytanie wykonywało się dłużej, ale niekoniecznie uzyskamy pewność, co do słuszności naszych przypuszczeń. Jeśli porównamy szczegóły operacji Filter z pierwszego planu i Nested Loops z drugiego planu, okaże się, że w drugim przypadku w predykacie pojawia się obliczona wartość wyrażenia (wartość zwracana przez funkcję ufnGetStock), zaś w pierwszym przypadku nadal będzie widniało wywołanie funkcji ufnGetStock. Czy to czegoś dowodzi? Chyba jeszcze nie :-)

A jednak wprawiony w bojach człowiek już w tym momencie czuje, czym pachnie to, co widać powyżej. Ale żeby dowieść "po obrazkach", że przeczucie go nie myli, uruchamia Profilera, włącza pokazywanie planów wykonania zapytań (klasa zdarzeń Performance, zdarzenie Showplan Text) i uruchamia oba zapytania. I wychodzi szydło z worka. W pierwszym ("gorszym") zapytaniu funkcja jest wywoływana tyle razy, ile jest wierszy na weściu (czyli ponad tysiąc razy, bo właśnie ponad tysiąc wierszy wchodzi po przeskanowaniu indeksu…). W drugim przypadku oczywiście funkcja wołana jest tylko raz (!).

Ten przypadek chyba pokazuje, jak mało trzeba czasem zrobić, by stał się cud optymalizacyjny :-) i jak bardzo trzeba uważać, gdy piszemy zapytania z użyciem funkcji skalarnych.

[EDIT: 2008-11-28]

W wyniku dyskusji z Markiem Powichrowskim postanowiłem dopisać wyniki naszych obserwacji. Uznałem bowiem, że starając się pokazać niecodzienne zachowanie optymalizatora w opisanym przypadku, przemknąłem nad dość oczywistym rozwiązaniem podobnych problemów (dzięki, Marek).

Po pierwsze, logicznym sposobem na uniknięcie przedstawionych powyżej problemów z wielokrotnym wykonywaniem funkcji wydaje się być użycie zmiennej, do której jeden raz podstawimy to, co zwraca funkcja, zaś do zapytania w klauzuli WHERE podajemy już gotową obliczoną wartość (patrz pierwsze zapytanie SELECT w kodzie poniżej). Proste i zdecydowanie zalecane! Co ciekawe, dodanie indeksu na kolumnie Quantity powoduje, że poniższe trzy zapytania wykonują się z planem zawierającym Index Seek.

USE AdventureWorks
GO

CREATE INDEX IX_Q ON Production.ProductInventory(Quantity)
GO

DECLARE @q int 
SELECT @q = dbo.ufnGetStock(1)
SELECT ProductID, LocationID, Quantity
FROM Production.ProductInventory
WHERE Quantity = @q

SELECT ProductID, LocationID, Quantity --, Shelf -- dodanie kolumny popsuje plan wykonania
FROM Production.ProductInventory
WHERE Quantity = dbo.ufnGetStock(1)

SELECT ProductID, LocationID, Quantity
FROM Production.ProductInventory
WHERE Quantity = (SELECT dbo.ufnGetStock(1))

Celowo zmieniłem listę wybieranych kolumn podstawiając zamiast gwiazdki indeksowaną kolumnę Quantity oraz kolumny indeksu clustered (te trzy kolumny można wyciągnąć z indeksu IX_Q). Tylko dzięki temu, że optymalizator używa nowego indeksu (wykonuje na nim Index Seek), nie zachodzi wielokrotne obliczanie wartości zwracanej przez funkcję w drugim zapytaniu. Wystarczy wrócić do poprzedniej wersji zapytania, by przekonać się, że jeżeli optymalizator dla takiego zapytania wybierze skanowanie indeksu, funkcja ponownie będzie ewaluowana ponad tysiąc razy…

PS. Dziękuję wszystkim za zainteresowanie tematem :-) Popychacie mnie do działania. Kto wie, może słowa Procenta z komentarza do tej notki będą prorocze (choć nie jestem fanem "Klanu") ;-)

*** I jeszcze uwaga – wszystkie zapytania uruchamiałem na SQL Server 2008 (10.00.1779). ***