Maksimum z kolumn w SQL Server.

MAX jako agregacja jednej kolumny

Zajmę się dzisiaj tematem dość powszechnym - wyliczaniem maksymalnej wartości. Można to robić dobrze lub źle - żeby nie powiedzieć niestosownie, tak jak w dowcipie. Przyjęło się, bo tak zdefiniowano w standardzie, że maksimum wylicza się na poziomie jednej kolumny. Popatrzmy na tabelę testową:

SELECT * INTO MultiMax FROM
(
  VALUES ('A', 'B', 'C'),
         ('F', 'E', 'D'),
         ('H', NULL, 'G'),
         (NULL, NULL, NULL)
) T(A,B,C)

SELECT * FROM MultiMax

Popatrzmy jeszcze na wynik zapytania przedstawiony w formie tabeli:

I w końcu popatrzmy na wartości agregacji wyliczane dla kolumn:

SELECT MAX(A) A, MAX(B) B, MAX(C) C FROM MultiMax

Użycie standardowych agregacji jest tak proste, że szkoda się nimi zajmować. To nie takimi wartościami maksymalnymi wypełnię ten wpis. Wpis będzie dotyczył wartości maksymalnych dla poszczególnych wierszy.

Wartość maksymalna z dwóch kolumn

Zanim przejdę do bardziej zaawansowanych przykładów popatrzmy na przykład prosty. Potrzebujemy maksymalną wartość z dwóch kolumn dla każdego z wierszy. Moglibyśmy zapisać to w takiej postaci:

SELECT CASE WHEN A > B THEN A ELSE ISNULL(B, A) END Maximum FROM MultiMax

Po uruchomieniu kwerendy otrzymamy następujący rezultat:

Wydaje się, że można w tym momencie zakończyć cały wpis - to jednak dopiero początek! Gdy konstrukcję wyliczającą mamy użyć w jednym zapytaniu - nie ma problemu. Gdy podobny kod mamy napisać w wielu miejscach, staje się to irytujące. W takich przypadkach najczęściej kierujemy się w stronę funkcji użytkownika.

Tradycyjne funkcja użytkownika

Funkcje można nazywać różnie, można je również umieszczać w różnych miejscach. Ja uznałem, że umieszczę je w przestrzeni Math, poprzez analogię do podobnej funkcji w .NET. Funkcje w SQL Server mogą przyjmować postać tradycyjną, skalarną, zwaną czasem inline, oraz uproszczoną, tablicową. W poniższym przykładzie obie oznaczyłem odpowiednim komentarzem.

CREATE SCHEMA Math

GO

--Funkcja tablicowa
CREATE FUNCTION Math.Max(@value1 int, @value2 int)
RETURNS TABLE
AS RETURN
(
  SELECT CASE WHEN @value1 > @value2 THEN @value1 ELSE ISNULL(@value2, @value1) END Maximum
)

GO

--Funkcja tradycyjna, inline
CREATE FUNCTION Math.IMax(@value1 int, @value2 int)
RETURNS INT
AS
BEGIN
  RETURN CASE WHEN @value1 > @value2 THEN @value1 ELSE ISNULL(@value2, @value1) END
END

Pierwsza różnica polega na sposobie traktowania wartości takiej funkcji. Funkcja inline zwraca wartość w postaci zdefiniowanego typu. Funkcja tablicowa, jak sama nazwa wskazuje zwraca tablicę. Determinuje to sposób wykorzystania ich w zapytaniach:

SELECT * FROM Math.Max(1,2)

SELECT Math.IMax(1,2)

Oba wywołania, choć nieco inne, zwracają wartość 2. Jakie są konsekwencje stosowania każdej z metod napiszę później. Większy problem stanowi rozszerzenie każdej z funkcji do trzech argumentów.

Maksimum z trzech i większej liczby elementów

W tym podpunkcie wrócę do typów, które wykorzystywane są przez tabelę z przykładu na początku - char. Trzeba wiedzieć, że SQL Server, przynajmniej w wersji 2012 i wcześniejszych, nie dopuszcza czegoś takiego jak przeciążanie funkcji, to znaczy stosowania tej samej nazwy funkcji dla różnych zestawów argumentów. Tymczasowo rozdzielę też funkcje tablicowe od funkcji tradycyjnych.

Maksimum z wielu wartości dla funkcji tablicowej

Popatrzmy na sposób definiowania funkcji tablicowych dla typu char dla dwóch i trzech argumentów. Stosując pokazaną technikę można rozszerzać zestaw metod do dowolnej liczby argumentów:

CREATE FUNCTION Math.MaxChar(@value1 char, @value2 char)
RETURNS TABLE
AS RETURN
(
  SELECT CASE WHEN @value1 > @value2 THEN @value1 ELSE ISNULL(@value2, @value1) END Maximum
)

GO

CREATE FUNCTION Math.MaxChar3(@value1 char, @value2 char, @value3 char)
RETURNS TABLE
AS RETURN
(
  SELECT * FROM Math.MaxChar(CASE WHEN @value1 > @value2 THEN @value1 ELSE ISNULL(@value2, @value1) END, @value3) Maximum
)

W przykładzie z liczbą całkowitą pobieraliśmy dane z funkcji, do któej przekazałem stałe. Gdy chcemy zastosować funkcję tablicową do każdego z rekordów, musimy wykonywać operacje tablicowe. Jednym z łatwiejszych rozwiązań jest zastosowanie operatora CROSS APPLY:

SELECT M.Maximum FROM MultiMax
CROSS APPLY Math.MaxChar(A,B) M

SELECT M.Maximum FROM MultiMax
CROSS APPLY Math.MaxChar3(A,B,C) M

Wykonując powyższe zapytania otrzymamy dwa zestawy danych: (B, F, H, NULL) oraz (C, F, H, NULL). Gdyby stosowanie operatora CROSS APPLY raniło czyjeś uczucia, może pójść w innym kierunku.

Maksimum z wielu wartości dla funkcji tradycyjnej

Czy dla funkcji zwracającej wartość skalarną warto stosować funkcję tablicową? Można przecież zastosować taką funkcję, która zwróci dokładnie to, czego potrzebujemy! Popatrzmy na poniższy przykład:

CREATE FUNCTION Math.IMaxChar(@value1 char, @value2 char)
RETURNS CHAR
AS
BEGIN
  RETURN CASE WHEN @value1 > @value2 THEN @value1 ELSE ISNULL(@value2, @value1) END
END

GO

CREATE FUNCTION Math.IMaxChar3(@value1 char, @value2 char, @value3 char)
RETURNS CHAR
AS
BEGIN
  RETURN Math.IMaxChar(@value1, Math.IMaxChar(@value2, @value3))
END

Tak zdefiniowane funkcje można wywołać w sposób bardziej przejrzysty, bardziej naturalny. Popatrzmy zatem na przykładowe wywołania:

SELECT Math.IMaxChar(A,B) Maximum FROM MultiMax

SELECT Math.IMaxChar3(A,B,C) Maximum FROM MultiMax

Wykonując zapytania otrzymamy dokładnie takie same jak w przypadku funkcji tablicowych wyniki: (B, F, H, NULL) oraz (C, F, H, NULL). Dlaczego zatem pokazałem obie metody? Nie było to dziełem przypadku. Chciałem pokazać coś, co trudno na pierwszy rzut oka dostrzec. Przejdźmy teraz do planów wykonania.

Plany wykonania

Ktoś, kto przeanalizował kilka wpisów z analizą planów wykonania podejrzewa już do czego zmierzam. Okazuje się, że plany nie są identyczne. Jest jeden drobny szczegół, który różni dwa obrazki. Przeanalizujmy najpierw maksimum z dwóch liczb i popatrzmy na plan dla funkcji tablicowej:

Gdy przeanalizujemy plan wykonania funkcji skalarnej okaże się, że oprócz pokazanych powyżej bloków/operatorów wyświetlone zostaną jeszcze dwa dodatkowe:

To tutaj jest źródło pewnych problemów wydajnościowych. Zanim jednak o rozmiarze tych problemów, popatrzmy na dodatkowe operacje wykonywane przez SQL Server w przypadku szukania maksimum z trzech wartości:

SQL Server wykrywa jeszcze jedno, pośrednie wywołanie. Jak duży wpływ na wydajność mają te niewielkie bloki na planie wykonania? O tym za chwilę.

Konstruktor tablicowy

Zanim przejdę do testów wydajnościowych pokażę jeszcze jeden sposób wyliczenia wartości maksymalnej z dwóch lub większej liczby kolumn. Technika wykorzystuje pewną funkcję dostępną od SQL Server 2 wersji 2008. To operator VALUES, który z kilku wyrażeń reprezentujących wiersze tworzy tablicę. Te wyrażenia mogą być między innymi kolumnami. Jak wykorzystać ten mechanizm? Popatrzmy na poniższy listing:

SELECT
(
  --Podzapytanie z konstruktorem tablicowym
  SELECT MAX(Col) FROM (VALUES(A), (B), (C)) T(Col)
) Maximum
FROM MultiMax

Kwerendy z konstruktorem tablicowym wymagają wprawy, ale mają szereg zastosowań - między innymi do obliczania agregacji na danych z jednego wiersza. Analizując dokładniej całe zapytanie można dostrzec inne zalety:

• Kwerendę bardzo łatwo rozszerzyć dostosowując liczbę argumentów. Nie ma problemu z dodaniem czwartej, piątej czy nawet dziesiątej kolumny.
• Zapytanie pozwala na zastosowanie innych wbudowanych agregacji. Możliwe jest między innymi wyliczenie średniej z kolumn, a nawet wykorzystanie niestandardowej agregacji CLR.
• W związku z wykorzystaniem standardowych agregacji automatycznie otrzymujemy narzędzia do obsługi wartości NULL.
• Poszczególne kolumny mogą być jawnie rzutowane na wybrane typy bez dużego wpływu na czytelność rozwiązania.
• Możliwość zastosowania kilku niestandardowych agregacji jednocześnie (konstruktor tablicowy może tworzyć tabelę z wieloma kolumnami).

Nieco zaskakujący na pierwszy rzut oka może być plan wykonania takiej instrukcji. Pokazałem go na poniższym rysunku:

Plan może i jest złożony, ale koszty poszczególnych bloczków, oprócz wspólnego dla wszystkich metod bloku skanowania tabeli, jest minimalny. Swoją drogą, koszt wywołań funkcji skalarnych też jest na planie oznaczony wartością 0% - bez znaczenia. Jak to się ma do rzeczywistości? Czas na najważniejszy test - test sprawnościowy.

Testy wydajnościowe liczenia maksimum w większej skali

Plany wykonania dają dużo informacji, ale nie dają pewności. Dopiero rzeczywiste czasy wykonania potwierdzają to, co plan może sugerować. Przyjrzyjmy się dokładniej trzem metodom do wyliczania maksimum z trzech kolumn na nieco większej tabeli, zawierającej milion wierszy.

CREATE TABLE CharMaxTest
(
  A char,
  B char,
  C char
)

SET NOCOUNT ON
BEGIN TRANSACTION
  DECLARE @i int=0;
  WHILE @i<1000000
  BEGIN
    INSERT INTO CharMaxTest VALUES('X', 'Y', 'Z');
    SET @i += 1;
  END
COMMIT

Testom poddane zostaną następujące metody:

--Funkcja skalarna
SELECT Math.IMaxChar3(A,B,C) Maximum FROM CharMaxTest

--Funkcja tablicowa
SELECT M.Maximum FROM CharMaxTest CROSS APPLY Math.MaxChar3(A,B,C) M

--Funkcja z konstruktorem tablicowym
SELECT (SELECT MAX(Col) FROM (VALUES(A), (B), (C)) T(Col)) Maximum FROM CharMaxTest

Popatrzmy teraz na wyniki uzyskane w środowisku testowymWartości w kolumnach CPU time oraz Elapsed time podawane są w milisekundach.:

Może to zaskakujące, ale najlepszy wynik osiągnęła metoda z konstruktorem tablicowym - pomimo pozornie najbardziej skomplikowanego planu wykonania. Co prawda czas procesora jest blisko dwukrotnie wyższy niż w przypadku metody tablicowej, ale może to być z łatwością zniwelowane poprzez zastosowanie kilku rdzeni procesora i zrównolegleniu obliczeń. Procesory wielordzeniowe to w zasadzie żaden luksus - teraz to normalność. Najsłabiej w tym teście wypadły funkcje skalarne. W pokazanych przykładach duży wpływ na rezultat końcowy ma samo odbieranie przez klienta dużej porcji danych - jest to przecież 1000000 wartości! Między innymi z tego powodu przygotowałem inny test - maksimum z maksimum. Popatrzmy na, tym razem cztery porównywane skrypty:

--Funckja skalarna
SELECT MAX(Math.IMaxChar3(A,B,C)) Maximum FROM CharMaxTest

--Funkcja tablicowa
SELECT MAX(M.Maximum) FROM CharMaxTest CROSS APPLY Math.MaxChar3(A,B,C) M

--Funkcja z konstruktorem tablicowym
SELECT MAX(M) Maximum FROM (SELECT (SELECT MAX(Col) FROM (VALUES(A), (B), (C)) T(Col)) M FROM CharMaxTest) X

--Pojedyncze wywołanie funkcji dla wbudowanych agregacji
SELECT Math.IMaxChar3(MAX(A), MAX(B), MAX(C)) FROM CharMaxTest

Tym razem różnice będą bardziej widoczne:

Ten wynik może zaskakiwać jeszcze bardziej. Po pierwsze, czas funkcji skalarnej jest dramatyczny, poniżej jakiejkolwiek krytyki. Skąd takie wartości? Właśnie z zaznaczonych na planie wykonania wywołań. Takie wywołania wiążą się ze zmianami kontekstu, przygotowaniem argumentów, samym wywołaniem, posprzątaniem stosu, wznowieniem głównej pętli - wszystko to, przemnożone przez 1000000, daje pokazany wynik. Pozostałe metody wykonywane są w ramach jednej iteracji. Jak natomiast wytłumaczyć słabszy wynik wbudowanych agregacji? Wychodzi na to, że im wcześniej zawęzimy liczbę agregacji tym lepiej (pojedyncza agregacja wbudowana daje około CPU time: 187, Elapsed time:194). To przez koszt samych agregacji, ale nie tylko. Okazuje się, że plan wykonania dla konstruktora tablicowego jest ekstremalnie wręcz zrównoleglony. Sugeruje to między innymi wartość czasu procesora. Nie zamierzam sugerować jednej metody i pozostawiam tę decyzję czytelnikom. To, która metoda okaże się ostatecznie lepsza zależy od konfiguracji sprzętowej - od wydajności procesorów i pamięci masowych serwera. W innych konfiguracjach rezultaty mogą się przecież różnić. Gdy zasobów zaczyna brakować, każda decyzja wymaga przemyśleń.

Przedstawione powyżej wyniki pomiarów pokazują, że plan wykonania nie zastąpi praktycznych testów. Na koniec jeszcze słowo o algorytmach równoległych - osoby zajmujące się algorytmami równoległymi wiedzą, że niektóre rzeczy łatwo zrównoleglić, inne trudniej, jeszcze inne wręcz nie nadają się do przetwarzania równoległego. Ot kolejna szeroka dziedzina badań.

Podsumowanie

Wyliczanie maksimum wydaje się być prostym tematem. Jak napisałem na początku, nawet potencjalnie najprostsze problemy można zrobić dobrze i źle, a zrobione źle potrafią przysporzyć wielu problemów. Oglądane w odpowiednim powiększeniu, w większej, milionowej skali, pokazują pazury. Stosunek najsłabszego z zaprezentowanych rozwiązań do najlepszego wynosi 12163/180 - około 67 razy szybciej dostajemy odpowiedź od serwera. Mam nadzieję, że pokazałem, oprócz różnych technik pobierania wartości maksymalnych, zagrożenia i pułapki wynikające z zastosowania funkcji skalarnych. Nie są one złe i nie należy ich przekreślać, bo są one najbardziej czytelne. Należy do nich jednak pochodzić ostrożnie, zwłaszcza w sąsiedztwie dużej ilości rekordów.

Kategoria:AgregacjeOptymalizacja SQLSQL Server