02.03.17r.
Technik tworzenia zapytań SQL na przykładzie danych z bazy AdventureWorks2014, oceniając ich wydajność i skuteczność. Dlaczego to tak długo trwa?! Czy może być szybciej?
Jednym ze standardowych zapytań raportowych jest wyliczanie sum narastających, nazywanych też Running Totals. Kto z nas nie spotkał się z kolumnami typu YTD (Year To Day) lub raportami dziennego utargu? Do niedawna powszechne było używanie kursorów w tym celu – jednak owiane złą sławą kursory obecnie są unikane przez nawet początkujących programistów, dlatego poszukamy innych rozwiązań. Porównajmy cztery techniki pisania zapytań odwołując się do tabeli SalesOrderHeader. Dla lepszego efektu zapytania sumują wiersz po wierszu bez filtracji po całej tabeli. To i tak tylko 31 465 rekordów. Oczywiście w praktyce tego typu obliczenia wyliczane są dla dnia, miesiąca lub roku dla konkretnego produktu, klienta lub kategorii ale na bazie AdventureWorks2014 uzyskalibyśmy zbyt małą ilość rekordów co byłoby niemiarodajne. Każde zapytanie poprzedzone zostanie poleceniami czyszczącymi cache instancji SQL’a
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
oraz standardowymi ustawieniami środowiska do raportowania czasów wykonania i operacji wejścia wyjścia
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
A oto nasze zapytania:
Zapytania zagnieżdżone
Tego typu zapytanie wydaje się być bardzo intuicyjne i logiczne - dla każdego kolejnego wiersza
do wartości pola SubTotal dodajemy sumę wartości tego pola, wyliczoną z wartości wierszy o wartości numeru zamówienia mniejszej, niż obecny numer wiersza obliczanego. Jednak w T-SQL’u proste rozwiązanie niekoniecznie jest efektywne. Popatrzmy na czasy wykonania oraz statystyki I/O:
SQL Server Execution Times:
CPU time = 140969 ms, elapsed time = 206295 ms.
Table 'Worktable'. Scan count 31465, logical reads 1396345
Table 'SalesOrderHeader'. Scan count 2, logical reads 1378
Jak widać zapytanie utworzyło obiekt tymczasowy z dużą liczbą logicznych odczytów stronic z pamięci, co nie miało pozytywnego wpływu na czas wykonania tego zapytania.
Zapytanie z JOIN’nem
Powyższe zapytanie możemy przekształcić na rozwiązanie z JOIN’em. Jak widać pomiar czasu pokazał znaczną poprawę, ale pojawiły się dodatkowe operacje I/O typu read-ahead czyli zaczytywanie stronic do cache’u
SQL Server Execution Times:
CPU time = 273921 ms, elapsed time = 122509 ms.
Table 'SalesOrderHeader'. Scan count 31470, logical reads 10939227, physical reads 2, read-ahead reads 685
Wyrażenia rekursywne CTE (Common Table Expression)
Sprawdźmy jak poradzi sobie wyrażenie rekursywne, które rozpocznie od pierwszego numeru zamówienia wskazanego, jako tzw. kotwica i przejdzie po kolejnych wierszach aż do ostatniego. Zaletą wyrażeń CTE jest wykorzystanie pamięci instancji, co powinno przełożyć się na pomiary czasu i operacji I/O. A oto rezultaty:
SQL Server Execution Times:
CPU time = 297 ms, elapsed time = 579 ms.
Table 'SalesOrderHeader'. Scan count 1, logical reads 94398, physical reads 90
Table 'Worktable'. Scan count 2, logical reads 188791, physical reads
To rozwiązanie wydaje się rewelacyjne w porównaniu do poprzednich, niestety byłoby wielkim błędem użycie go w aplikacji na stałe - pytacie dlaczego? Przy prostych rozwiązaniach testowych rekursja nie zostanie przekroczona, ale jeżeli pojawi się większa liczba zamówień serwer zwróci błąd: Msg 310, Level 15. Proszę zwrócić uwagę na opcję MAXRECURSION maksymalna wartość tego parametru
to 32 767 - co oznacza, że akurat udało się zmieścić z liczbą wierszy 31 465 zwróconą przez zapytanie. Nasza rekursja nie jest naturalna i z przejściem do kolejnego wiersza poziom rekursji rośnie zawsze
o jeden. Takie rozwiązanie nie byłoby perspektywiczne i wcześniej czy później skończy się błędem. Wyrażenia rekursywne powinniśmy stosować tylko gdy mamy naturalne powiązania między rekordami, jak klasyczny przypadek pracownika, który może być przełożonym lub podwładnym innego pracownika z tej samej tabeli.
Funkcje okien
Funkcje okien są techniką znaną już w serwerze SQL 2005 i rozwijają się z każdą nową wersją SQL Server’a. W wersji SQL 2012 i 2014 pojawiły się nowe sposoby na dynamiczne odwoływanie się do rekordów na zasadzie offset’u. Takie rozwiązanie jest elastyczne i wydaje się być dedykowane dla takich zapytań jak nasze. Sprawdźmy zatem, jak serwer poradzi sobie z sumami narastającymi:
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 215 ms.
Table 'SalesOrderHeader'. Scan count 1, logical reads 689
Jak widać użycie funkcji okien okazało się strzałem w dziesiątkę. Porównajmy jeszcze plany wykonań zapytania z join’em oraz zapytania z użyciem funkcji okien w jednym batch’u:
Zwróćmy uwagę na koszt zapytania dla całego batch’a - porównanie jest bezkonkurencyjne podobnie, jak sam czas wykonania.
Ponieważ użycie funkcji okien okazało się bardzo ciekawym rozwiązaniem w następnym artykule postaram się przybliżyć to rozwiązanie.
------------------------------------------------------
Autor:
Adam Białas – MCT, MCSE: Data Platform, Business Intelligence