Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc

[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.

image

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 :-)

 image image

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))
 
image

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). ***

Opublikowane 27 listopada 2008 08:09 przez brejk

Komentarze:

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

27 listopada 2008 09:11 by Procent

Mam szczerą nadzieję, że tak rozpoczęty cykl doczeka się przynajmniej tylu odcinków co Klan:). Temat zaiste strasznie interesujący i przydatny.

I śmiem twierdzić, że bardzo często wiedza w tym zakresie jest grubo poniżej przyzwoitego poziomu (oczywiście wśród developerów, w tym mnie, a nie sql-mastahs). Keep it comin' ;)

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

27 listopada 2008 09:43 by Wojciech Gebczyk

Przypomnialo mi sie z MTS (chyba) ze jak pisze sie funkcje (generalnie "koda") .NET do SQL, to mozna powiedziec czy funkcja jest stala wzgledem arg czy nie. moze sa takie "hinty" to SQLowych funkcji?

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

27 listopada 2008 10:08 by brejk

@Wojtek: Tak, takim hintem jest WITH SCHEMABINDING w definicji funkcji. Ale w tym przypadku dodanie tej opcji niczego nie zmieni.

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

27 listopada 2008 11:40 by Wojciech Gebczyk

Pawel, no jak nie? Przeciez wywolujesz z ta sama wartoscia, wiec powinno raz obliczyc wartosc a potem uzyc tego... Znaczy sie nie znam sie na SQL ale z opisu jak mialo by sie zachowywac z tym "hintem", o powinno raz wolac ta metode/funkcje... Znaczy zle zrozumialme te podpowiedzi czy SQL ignoruje je/nie wykorzystuje?

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

27 listopada 2008 12:47 by brejk

@Wojtek: Co Ci mogę napisać... W opisanym przypadku dodanie SCHEMABINDING w funkcji nic nie daje. Stwierdzono doświadczalnie :-)

Zresztą, przykład na AdventureWorks jest wyimaginowany. Tak naprawdę myk wziął się z czego innego - w ten sposób zoptymalizowaliśmy w firmie zapytanie, w którym w WHERE szukano zamówień z określoną datą (a podstawianą funkcją była funkcja wycinająca czas z GETDATE() - tu już o deterministyczności nie ma mowy, bo samo GETDATE() jest niedeterministyczne).

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

27 listopada 2008 15:00 by arkadiusz.wasniewski

Ja wymiękam normalnie.... Ostatni raz taki wstrząs przeżyłem kiedy w duuuużym zapytaniu raportowym zmienną tabelaryczną @ zastąpiłem tabelą dynamiczną #

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

27 listopada 2008 15:11 by brejk

@Arek: No, jeśli to było duuuże zapytanie, to @ się nie kwalifikuje, co udowadniał choćby Maciek Pilecki na C2C.

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

27 listopada 2008 15:29 by Wojciech Gebczyk

Pawel:

Ha! MSDN pisze ze: "The Database Engine automatically analyzes the body of Transact-SQL functions and evaluates whether the function is deterministic" [http://msdn.microsoft.com/en-us/library/ms187440.aspx]

Wiec jak twoja funkcja zostala nie wykryta jako niedeterministyczna to pewnie tak bylo. A WITH SCHEMACOSTAM nie pisze o determinizmie.

Wogole nie znalezlem opcji jak reczenie powiedziec SQLowi ze funkcja jest deterministyczna czy nie (w T-SQL)

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

27 listopada 2008 15:51 by brejk

@Wojtek: Masz rację. WITH SCHEMABINDING tego nie ustawia, ale jeśli daje się ustawić tę opcję, to znaczy, że ani nie ma w funkcji wywołania rozszerzonej procedury, ani nie ma w niej odwołań do niedeterministycznych funkcji. I teraz - po wpisaniu WITH SCHEMABINDING:

SELECT OBJECTPROPERTY(OBJECT_ID('ufnGetStock'),'IsDeterministic') -- zwraca 1

A jak wytnę WITH SCHEMABINDING, to dostaję 0!!!

Mam w to nie wierzyć? ;-)

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

27 listopada 2008 15:54 by Wojciech Gebczyk

Pawel, no zrozumialme ze praktyka co innego mowi a doce co innego :-)

Ciagle mnie zastanawia po kiego dali opcje/atrybut deterministycznosci jak go nie uzywaja... o kant tego-owego potluc to...

Pozdrawiam,

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

27 listopada 2008 16:03 by brejk

@Wojtek: Używają, ale niekoniecznie w tym miejscu :-) Chyba zrobię mały research, zbiorę materiały i napiszę kawałek o deterministyczności ;-) Dzięki za natchnienie :D

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

27 listopada 2008 22:25 by damian_widera

Paweł, super temat :). Chyba wpadliśmy obydwaj w to samo, pokaząłem Ci w poniedziałek czy wtorek co znalazłem u siebie - wypisz wymaluj to co ty :)

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

27 listopada 2008 22:30 by marekpow

Paweł, sprawdzałem to na wersji 2005 i 2008. Oba zapytania działają poniżej jednej sekundy. Skąd u Ciebie taka duża różnica w czasie wykonania? Uruchomiłem oba zapytania z właczoną statystyką SET STATISTICS PROFILE ON (na 2008 i 2005) i estimated CPU jest niższe dla pierwszego planu zapytania. Pozostałe wskaźniki są na tym samym poziomie.

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

27 listopada 2008 22:59 by brejk

@Marek: Możliwe, że na moim słabym sprzęcie różnice w tym konkretnym przypadku są aż nadto widoczne. Ale sam fakt, że funkcja wykonuje się w jednym przypadku ponad 1000 razy, a w drugim tylko raz, powoduje, że w przypadku większej liczby rekordów to po prostu wyjdzie (wygeneruj sobie do tej tabeli 100 tys. rekordów i sprawdź jeszcze raz).

# [PL] Przypadki optymalizacji. Odc. 2: Oczywiste bywa najtrudniejsze

5 marca 2009 12:35 by SQLGEEK

Zastanawiałem się ostatnio, z jakimi przypadkami źle napisanego - w sensie wydajności - kodu T-SQL spotykam

Komentarze anonimowe wyłączone

About brejk

MVP, MCT, SQL Server geek