Ta strona używa cookies. Dowiedz się więcej o celu ich używania i zmianie ustawień cookies w przeglądarce.
Korzystając ze strony wyrażasz zgodę na używanie cookies, zgodnie z aktualnymi ustawieniami przeglądarki.

Zamknij X
Strona główna  Baza Wiedzy   T-SQL bez tajemnic cz. 1
Logo SOFTRONIC

Ośrodek Szkoleniowy i Egzaminacyjny

T-SQL bez tajemnic cz. 1

02.03.17r.

Podziel się:

        

Dlaczego to tak długo trwa?! Czy może być szybciej?

Język T-SQL jest podstawowym językiem serwera bazodanowego firmy Microsoft. Używany jest przez wiele osób, niekoniecznie pracujących w działach IT. T-SQL jest bardzo istotną bronią w rękach analityków danych lub osób zajmujących się raportowaniem. Trudność posługiwania się SQL’em nie polega na jego skomplikowanej składni (poznanie składni jest jak pozyskiwanie wiedzy encyklopedycznej – wymaga czasu oraz dobrej encyklopedii – czyli w naszym przypadku dokumentacji) lecz na odpowiednim „podejściu”, doborze dobranych funkcji czy technik pisania zapytań. Dla przykładu możemy napisać zapytanie w dwóch wersjach, które dają ten sam wynik, jednak ich czasy wykonania są biegunowo odległe. Napisanie zapytania, które jest wydajne jest już trudnym zadaniem. 
 
W tym artykule chciałbym pokazać kilka sposobów pisania „tego samego” zapytania przy użyciu różnych technik. Dzięki temu porównamy ich wydajność i ocenimy, które jest najlepsze. Poniższe przykłady wykonywane są na SQL 2014 i standardowej bazie szkoleniowej AdventureWorks2014, którą można pobrać z https://msftdbprodsamples.codeplex.com/downloads/get/880661.
 
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

 

« powrót