Spis treści:

Kategoria:AgregacjeFunkcje oknaOptymalizacja SQLSQL Server


Sumy bieżące w SQL Server

Wiele sposobów, wiele problemów

Pisanie zapytań SQL, ale nie tylko, wymaga myślenia. Powiem więcej - wymaga tego programowanie oraz jeszcze szersza gałąź - pisanie skryptów. Niektórzy wszak pisanie skryptów PowerShell i automatyzowania zadań systemów nie uznają za programowanie. Idąc dalej, niektórzy nie uznają PHP jako języka programowania...

Nie o to jednak chodzi. Chodzi o to, że w każdym z tych obszarów istnieje wiele wiele sposobów na rozwiązanie poszczególnych zadań. Żeby nie szukać za daleko, większość języków oferuje kilka rodzajów pętli które w ostateczności i tak zostaną przetłumaczone na instrukcję skoku w procesorze. Po co zatem jest ich aż tyle? Istnieje wiele algorytmów sortowania: przez wybieranie, bąbelkowe, algorytm QuickSort, algorytm sortowania przez zliczanie czy też bardziej ogólny, kubełkowy. Po co ich aż tyle? Każdy z nich ma swoje obszary zastosowań i każdy jest przydatny. Trzeba tylko wiedzieć kiedy. To tutaj zaczyna się owe myślenie. Myślenie, bez którego może i jakieś rozwiązanie uda się wymyślić, ale będzie ono odstawało od optymalnego.

Jeszcze jedno słowo wstępu. Podejrzewam, z doświadczenia, że, czytając nazwę bąbelkowe, wiele osób zapewne się uśmiechnie. Trochę to niesprawiedliwe, bo algorytm ten uważany jest za gorszy. Nie przeszkadza to jednak w tym, aby algorytm ten na dobre zadomowił się w... SQL Server! Ma on dwie szczególne właściwości, które pozwalają mu zająć niszowe, ale pewne miejsce w szeregu. Po pierwsze, sortowanie listy posortowanej wykonywane jest w czasie liniowym. Po drugie, sortowanie można w każdym momencie przerwać i wznowić w dowolnym innym momencie. Algorytm po wznowieniu bardzo szybko wykryje miejsce, od którego ma zacząć przestawiać. Temat jest na tyle interesujący, że być może zajmę się nim osobno. Wtedy dokładniej wyjaśnię gdzie i dlaczego warto go stosować. Wróćmy jednak do sum bieżących.

Tabela testowa

Popatrzmy na tabelę, na której wyliczane będą proste sumy bieżące. Później, gdy będę się zajmował złymi rozwiązaniami i wydajnością, tabela będzie nieco inna - przede wszystkim większa.

CREATE TABLE Orders
(
    Article char(4),
    OrderDate DateTime2(2),
    Units int,
    TotalPrice decimal(9,2)
)

INSERT INTO Orders VALUES
('CARS', '20120101', 2, 200.0),
('FOOD', '20120105', 5, 50.0),
('TREE', '20120107', 2, 70.0),
('BOOK', '20120111', 1, 44.0),
('COMP', '20120202', 1, 1200.0),
('FISH', '20120206', 6, 17.0)

W tabeli powinny się znajdować następujące dane:

ArticleOrderDateUnitsTotalPrice
CARS2012-01-01 00:00:00.002200.00
FOOD2012-01-05 00:00:00.00550.00
TREE2012-01-07 00:00:00.00270.00
BOOK2012-01-11 00:00:00.00144.00
COMP2012-02-02 00:00:00.0011200.00
FISH2012-02-06 00:00:00.00117.00

Przystąpmy zatem do liczenia sum bieżących.

Sumy bieżące w najprostszym wydaniu

Popatrzmy na najprostsze i przy okazji sugerowane rozwiązanie problemu wyliczania sum bieżących:

SELECT TotalPrice,
       OrderDate,
       SUM(TotalPrice) OVER (ORDER BY OrderDate) [Running Total]
FROM Orders

Po wykonaniu skryptu ujrzymy następujący rezultat:

TotalPriceOrderDateRunning Total
200.002012-01-01 00:00:00.00200.00
50.002012-01-05 00:00:00.00250.00
70.002012-01-07 00:00:00.00320.00
44.002012-01-11 00:00:00.00364.00
1200.002012-02-02 00:00:00.001564.00
17.002012-02-06 00:00:00.001581.00

Widać wyraźnie, jak w trzeciej kolumnie wartości są kumulowane. Składnia funkcji okna OVER z wyrażeniem ORDER BY jest zdefiniowana w standardzie jako suma bieżąca, więc powinna być wspierana przez wszystkie bazy, które tym standardem się chwalą. Jest to niestety standard SQL:2011, więc pewnie jeszcze trochę wody w Wiśle upłynie, zanim będzie w pełni wspierany. Składnię wspierają zatem dopiero wersje od SQL Server 2012 (także SQL Server Express).

Sumy bieżące dla zadanych okresów

Suma bieżąca dla całości wydaje się prosta. Równie prosta jest suma bieżąca liczona dla poszczególnych grup, na przykład dla poszczególnych kategorii towarów lub miesięcy. Popatrzmy na przykład:

SELECT TotalPrice,
       OrderDate,
       SUM(TotalPrice) OVER (PARTITION BY YEAR(OrderDate), MONTH(OrderDate) ORDER BY OrderDate) [Running Total]
FROM Orders

Tym razem otrzymamy następujące wyniki:

TotalPriceOrderDateRunning Total
200.002012-01-01 00:00:00.00200.00
50.002012-01-05 00:00:00.00250.00
70.002012-01-07 00:00:00.00320.00
44.002012-01-11 00:00:00.00364.00
1200.002012-02-02 00:00:00.001200.00
17.002012-02-06 00:00:00.001217.00

Ostatnie rekordy mają inny miesiąc i rok - zostaną wobec tego potraktowane jako oddzielne okno. Suma bieżąca dla lutego w roku 2012 będzie liczona niezależnie od sumy bieżącej dla stycznia, niezależnie od sum bieżących dla innych lat i miesięcy.

Wiele sum bieżących w jednym zapytaniu

Zapytania mogą liczyć jednocześnie wiele sum bieżących i operować na wielu oknach jednocześnie. Gdybyśmy oprócz sumy bieżącej zechcieli wskazać liczbę sprzedanych jednostek (o ile ma to sens biznesowy, trudno sumować rybę z komputerem o ile nie są to jakieś znormalizowane jednostki, na przykład rozmiar skrzynki w firmie transportowej), zastosowalibyśmy dwa okna, w tym przypadku jednakowe:

SELECT TotalPrice,
       SUM(TotalPrice) OVER (ORDER BY OrderDate) [Running Total],
       Units,
       SUM(Units) OVER (ORDER BY OrderDate) [Running Units]
FROM Orders

Tym razem wynik byłby następujący:

TotalPriceRunning TotalUnitsRunning Units
200.00200.0022
50.00250.0057
70.00320.0029
44.00364.00110
1200.001564.00111
17.001581.00617

Przetwarzanie zapytania przez SQL Server

Pisząc zapytanie warto zawsze zastanowić się, jak zrealizowalibyśmy je w proceduralnym języku programowania i jaki byłby koszt takiej operacji. Z pewnością przetwarzalibyśmy rekordy względem daty i zapisywali w zmiennej pomocniczej bieżącą sumę. Ta bieżąca suma byłaby dokładana do każdego z rekordów wynikowych. Popatrzmy na plan wykonania wybrany przez SQL Server i porównajmy z naszymi wyobrażeniami:

rysunek przedstawiający plan wykonania operacji liczenia sumy bieżącej
Plan wykonania instrukcji liczącej sumę bieżącą

Widać wyraźnie, że jedynymi wartymi uwagi instrukcjami są skanowanie tabeli i sortowanie. Jest to zatem bardzo optymalny plan. Jak ten plan wypada w porównaniu z innymi?

Podzapytanie skorelowane

Jednym ze stosowanych rozwiązań jest wyliczanie sumy dla każdego kolejnego rekordu. Popatrzmy na poniższy listing:

SELECT TotalPrice,
       OrderDate,
       (SELECT SUM(TotalPrice) FROM Orders X WHERE X.OrderDate<=O.OrderDate)
FROM Orders O

Suma bieżąca wyliczana jest jako suma wszystkich poprzednich rekordów.

Zapis jest nieco dłuższy, ale nie to stanowi problem. Popatrzmy na plan wykonania:

rysunek przedstawiający plan wykonania operacji liczenia sumy bieżącej
Plan wykonania sumy bieżącej dla zapytania skorelowanego.

Widać wyraźnie, że dla każdego ze zwracanych rekordów wykonywane jest pełne skanowanie tabeli wyliczające sumę. Można to poprawić stosując odpowiednie indeksy, ale i tak koszt będzie duży. Co się stanie, gdy takich zapytań skorelowanych będzie więcej? Popatrzmy na poniższy przykład i pomyślmy:

SELECT TotalPrice,
       (SELECT SUM(TotalPrice) FROM Orders X WHERE X.OrderDate<=O.OrderDate),
       Units,
       (SELECT SUM(Units) FROM Orders X WHERE X.OrderDate<=O.OrderDate)
FROM Orders O

Tym razem dla każdego ze zwracanych rekordów wykonywane są dwa pełne skanowania tabeli wyliczające poszczególne sumy. Gdy rekordów jest bardzo dużo, baza danych zamiera. Autorzy zapytań zaczynają kombinować.

Samozłączenie nierównościowe tabeli

Jednym z rozwiązań jest zastosowanie odpowiedniego złączenia nierównościowego (theta) połączonego z grupowaniemNiektórzy stosują tu nawet pełne złączenie CROSS JOIN i odpowiednio nakładają warunki.. Zarys techniki pokazany jest na poniższym listingu:

SELECT O1.TotalPrice,
       O1.OrderDate,
       SUM(O2.TotalPrice)
FROM Orders O1
JOIN Orders O2 ON O2.OrderDate<=O1.OrderDate
GROUP BY O1.OrderDate, O1.TotalPrice
ORDER BY O1.OrderDate

Zapytanie trochę się rozrosło, ale nie wygląda strasznie. Popatrzmy, co się dzieje z planem wykonania:

rysunek przedstawiający plan wykonania operacji liczenia sumy bieżącej
Plan wykonania sumy bieżącej dla samozłączenia theta.

Już sam plan wykonania pokazuje, że to zapytanie nie może być wydajne. Znów można skorzystać z indeksów, które nieco ten stan rzeczy poprawią. Nie zmienia to jednak faktu, że dla każdego rekordu wykonywane jest pełne, lub częściowe z indeksem, skanowanie tabeli. Rzeczywiste czasy i koszty zapytań poszczególnych rozwiązań zbiorę na końcu w tabeli. Przejdźmy tymczasem do kolejnego rozwiązania.

Stare dobre kursory

Intuicja podpowiada, że dałoby się uniknąć skanowania tabeli stosując kursory. Podpowiada słusznie. Okazuje się, że te kursory będą dla dużej liczby rekordów działały lepiej niż pokazane wcześniej techniki! Oczywiście poza pierwszą, wykorzystującą funkcje okna. Problem w tym, że trzeba się strasznie opisać:

DECLARE @temp TABLE
(
    TotalPrice decimal(9,2),
    OrderDate DateTime2(2),
    RunningTotal decimal(9,2)
);
 
DECLARE
    @TotalPrice decimal(9,2),
    @OrderDate datetime2(2),
    @RunningTotal decimal(9,2) = 0.0;
 
DECLARE cur CURSOR FAST_FORWARD FOR
    SELECT TotalPrice, OrderDate
    FROM Orders
    ORDER BY OrderDate;
 
OPEN cur;
 
FETCH NEXT FROM cur INTO @TotalPrice, @OrderDate;
 
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @RunningTotal = @RunningTotal + @TotalPrice;
 
    INSERT INTO @temp(TotalPrice, OrderDate,  RunningTotal)
        VALUES (@TotalPrice, @OrderDate, @RunningTotal);
 
    FETCH NEXT FROM cur INTO @TotalPrice, @OrderDate;
END
 
CLOSE cur;
DEALLOCATE cur;
 
SELECT * FROM @temp

Algorytm będzie wprawdzie wolny, ze względu na kursory, ale jego złożoność będzie liniowa. Inny problem to kod, którego jest dużo i który w przypadku jakiejś zmiany w strukturze tabeli musi być zmieniany. Skrypt komplikuje się też wtedy, gdy trzeba zrobić agregacje na kilku kolumnach i dla różnych okien. Czasami jednak nie ma innego wyjścia.

Inne metody

Istnieją wprawdzie inne metody, ale zostały przeze mnie pominięte. W Internecie można spotkać technikę wykorzystującą wyrażenia CTE, ale dość szybko natrafiamy na ograniczenie zagłębień rekurencji wynoszącą 215 - 1 = 32767. Wymagają one też ciągłości na jakimś polu (na przykład ciągłości i jednostajności dat, ciągłości pól identity). Są też rozwiązania wykorzystujące wyzwalacze, ale również wymagają dużego nakładu pracy i dużych przygotowań. Są rozwiązania wykorzystujące własne agregacje CLR (przykład inne agregacji tutaj: Własne agregacje w SQL Server), ale i one mają swoje ograniczenia. Istnieją pewnie jeszcze bardziej wymyślne rozwiązania, z którymi nie miałem okazji się zetknąć. Na szczęście SQL Server 2012 raz na zawsze rozwiązuje ten problem.

Porównanie czasów różnych rozwiązań

Obiecałem wcześniej, że na końcu zamieszczę tabelkę z porównaniem poszczególnych metod. Żeby wynik był bardziej precyzyjny, pomiary wykonam na nieco większej porcji danych. Poniżej przedstawiam skrypt uzupełniający tabelę Orders:

SET NOCOUNT ON
DECLARE @i int=0;
WHILE @i<10000
BEGIN
    INSERT INTO Orders VALUES('AAAA', DATEADD(HOUR, @i, '20000101'), 1, 1.0);
    SET @i+=1;
END

Popatrzmy teraz na rezultaty, które udało mi się uzyskać:

MetodaScan countScan count (Worktable)Logical readsLogical reads (Worktable)CPU TimeElapsed Time
SUM() OVER1100144560176125180
Zapytanie skorelowane210000901832351277612768
Samozłączenie theta101000090183235308875017
KursorPomiar liczby skanowań dla kursora wykonywany był nieco inaczej. Kod kursora składa się z wielu drobnych instrukcji i pomiar każdego z tych składników miałby znaczący wpływ na czas samej operacji. Wartości z kolumn Scan count, Scan count (Worktable), Logical reads, Logical reads (Worktable) są podane teoretycznie. Należy pamiętać, że kursor oprócz pobierania i przetwarzania danych tworzy tabelę tymczasową, do której zapisuje. Wpływ na czas końcowy ma także ten zapis.1150581N/A249427

Ostatnia kolumna to całkowity czas operacji na serwerze testowym. Czas ten może być niższy niż czas potrzebny dla procesora (przedostatnia kolumna). Wynika to z zastosowania równoległych obliczeń i wielu rdzeni. Popatrzmy jeszcze na prosty wykres:

Porównanie czasów wykonania różnych metod liczenia sum bieżących.

Różnice są widoczne gołym okiem. Jeżeli jeszcze na te różnice nałożymy złożoność samych instrukcji, będziemy mogli wybrać zwycięzcę. W SQL Server 2012 i nowszych ten zwycięzca jest oczywisty. W starszych wersjach trzeba rozważyć wszystkie za i przeciw.

Kategoria:AgregacjeFunkcje oknaOptymalizacja SQLSQL Server

, 2014-02-26

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !
a z innej strony - co gdybym ciąg znaków chciał mieć rozbity nie na wiersze a na kolumny? Czyli ciąg ABCD: 1. kolumna: A, 2. kolumna: B, 3. kolumna: C, 4 kolumna: D?
Ciekawy artykuł.
Czy można za pomocą EF wysłać swoje zapytanie?
Czy lepiej do tego użyć ADO.net i DataTable?