Porównanie trzech sposobów na PIVOT w TSQL

TsqlPivot_14

Dane w relacyjnych bazach danych posiadają formę tabelaryczną tj. składają się z kolumn oraz wierszy. Zdarza się jednak dosyć często, że chcemy wiersze zamienić na kolumny lub kolumny na wiersze – operację taką zwyczajowo nazywamy piwotowaniem. W ramach tego artykułu chciałbym przedstawić trzy różne sposoby na osiągnięcie zamierzonego rezultatu. Oczywiście nie są to wszystkie możliwe sposoby – znajdą się inne metody na odwrócenie wyniku zarówno w samym kodzie TSQL, kodzie zewnętrznym czy w SSIS – jednakże w ramach tego artykułu chciałbym się ograniczyć do tych trzech metod, które mimo wszystko wydają się najprostsze i najbardziej popularne. Zapraszam do lektury.

Na samym początku przedstawmy nasz zestaw testowy którym będzie następujące zapytanie:

Przypuśćmy, że naszym celem jest stworzenie czegoś na wzór matrixa gdzie Id faktury, linii faktury oraz rozmiar będą znajdować się po stronie wierszy, w kolumnach będzie kolor, a ilość będzie odpowiednio wyliczona na przecięciach tych składowych. Cała koncepcja wygląda następująco:

Pierwszym sposobem jaki postaram się przedstawić jest chyba ten najbardziej oczywisty, a mianowicie użycie składni PIVOT dostępnej w TSQL. Składnia ta jest specyficzna i zazwyczaj trzeba się jej chwilę przyjrzeć aby dobrze ją zaimplementować. W naszym przypadku zapytanie źródłowe otoczymy CTE co zwiększy jego czytelność i dopiero na rezultacie tego działania wykonamy pożądaną operację:

Składnia wygląda tak, że rozszerzamy nasze podzapytanie (w tym przypadku o nazwie invoices) o PIVOT sumy dla konkretnych kolorów. Rezultat jest dokładnie tym czego oczekiwaliśmy i przedstawia się następująco:

Mimo, że całe zapytanie nie wydaje się zbyt skomplikowane to takowe w gruncie rzeczy jest – wystarczy rzucić okiem na plan zapytania, który prezentuje się całkiem okazale:

W każdym bądź razie cel osiągnięty stosunkowo niewielkim nakładem sił. Kolejną metodą na osiągnięcie tego samego rezultatu będzie metoda roboczo przeze mnie nazwana SUMIF. Będzie się ona opierała o sumy warunkowe tj. będziemy sumować tylko te wiersze, które są równe określonemu kolorowi. W poniższym przykładzie użyłem funkcji IIF ale w praktyce możemy również użyć CASE, które w gruncie rzeczy będzie wykonywany wewnętrznie w ten sam sposób. Zapytanie w tej metodzie wygląda następująco:

Zapis stosunkowo prosty i w moim mniemaniu dużo czytelniejszy niż poprzednio. Plan wykonania również prezentuje się lepiej jeśli chodzi o poziom skomplikowania – co prawda mogli byśmy jeszcze w niektórych aspektach go poprawić ale póki co zostawimy go tak jak jest:

Trzecia metoda o jakiej chciałbym wspomnieć jest metoda o roboczej nazwie Monster Query(zaraz się przekonacie dlaczego), która polega na dołączaniu tego samego zbioru danych tylko tym razem z innym kolorem. Zapytanie wygląda następująco (nie przeraźcie się:)):

Rezultat znów po raz kolejny jest poprawny:

Plan zapytania podobnie jak samo zapytanie jest bardzo mocno skomplikowany i rozbudowany (wiem, że jest on nieczytelny jednakże nie interesują nas jego konkretne operatory tylko pogląd co do poziomu skomplikowania)

Nie wiem jak u Was ale gdy widzę taki plan zapytania to na mojej twarzy pojawia się uśmiech. Pytacie jak to możliwe żeby zbudować coś takiego? Odpowiedź nie jest mi znana ale muszę Wam przyznać, że miałem okazję spotkać każde z tych podejść w praktyce z czego to ostatnie zmobilizowało mnie do napisania niniejszego artykułu. Porównajmy sobie każde z podejść pod kątem czasu wykonania, statystyk IO oraz kosztu.

Porównanie estymowanego kosztu przedstawia się następująco:

Zaskoczeni? Ja na początku trochę byłem ponieważ to co powinno być najlepiej zoptymalizowane do tego typu operacji czyli PIVOT okazał się najbardziej kosztowny! Kosztowniejszy nawet od monstrualnego zapytania z ostatniego przykładu. Najmniej kosztowny okazał się SUMIF.

Przejdźmy dalej i zobaczmy jak wygląda sprawa odczytu statystyk IO:

PIVOT

SUMIF

Monster Query:

Wnioski są dosyć oczywiste, nasze monstrualne zapytanie dało nam najwięcej odczytów, PIVOT oraz SUMIF miały podobne statystyki jednakże znów SUMIF okazał się najbardziej wydajnym rozwiązaniem. Ostatnią statystyką jaką sobie zbadamy jest oczywiście czas wykonania całego zapytania chociaż nie jest ona zbyt miarodajna z oczywistych względów to mogę powiedzieć, że różnica była na tyle nieznacząca pomiędzy każdym z tych zapytań, że możemy uznać ją za w przybliżeniu taką samą.

Która zatem z tych metod jest najlepsza? Odpowiedź jak zawsze jest niejednoznaczna bo zależy pod jakim kątem je porównujemy. Jeśli chodzi o wydajność to z powyższych testów i mojego osobistych doświadczeń wynika, że to SUMIF daje najlepsze rezultaty, a podejście zaprezentowane jako “Monster Query” najgorsze. Pod kątem czytelności również powiedziałbym, że ranking kształtuje się podobnie. Co ciekawe w zdecydowanej ilości przypadków funkcja PIVOT działała niewiele ale jednak gorzej od SUMIF co powiem szczerze było dla mnie małą niespodzianką. W przypadku operacji odwrotnej czyli UNPIVOT mamy już tylko dwa podstawowe podejścia, a mianowicie składnię UNPIVOT i zestaw poleceń UNION, których porównanie postaram się przedstawić w ramach osobnego artykułu. Na ten moment polecam testowanie PIVOT oraz SUMIF w przypadku operacji przestawiania kolumn i wierszy i wybrania tego podejścia, które daje nam najlepsze rezultaty i najlepszą czytelność kodu.

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

Latest posts by Adrian Chodkowski (see all)

2 Comments

  1. Jesse

    Great post, thank you for sharing. It is good to keep this in mind when developing.

    Reply
  2. Szymon

    Bardzo ciekawy artykuł i przydatne wskazówki.
    Pozdrawiam.

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *