Rozwiązania oparte na zbiorach opierają się na zasadach modelu relacyjnego. model relacyjny jest oparty na matematycznej teorii zbiorów. Rozwiązania zbiorowe używają zapytań T-SQL które operują na tabelach jako zbiorach rekordów. Takie rozwiązania są przeciwieństwem rozwiązań iteracyjnych które używają kursorów lub innych iteracyjnych konstrukcji to obsługi jednego wiersza na raz. Zgodnie z teorią zbiorów , zbiór powinien być rozpatrywany jako całość. Oznacza to, że uwaga powinna być zwrócona na cały zbiór a nie na jego indywidualne elementy. W rozwiązaniach iteracyjnych łamiemy tę zasadę operując na jednym elemencie na raz. Ponadto, zbiór nie ma określonej kolejności jego elementów. Więc jeżeli używamy rozwiązania bazującego na zbiorach, nie można dokonywać żadnych założeń co do kolejności danych. podobnie jeżeli nie dodamy klauzuli ORDER BY do zapytania nie mamy gwarancji, że dane zostaną zwrócone w określonej kolejności. W rozwiązaniach iteracyjnych można przetwarzać jeden wiersz na raz i można to zrobić w określonej kolejności. Generalnie rekomenduje się użycia rozwiązań na zbiorach i pozostawienie rozwiązań iteracyjnych dla specjalnych wyjątków. jednym z powodów jest to, że język SQL został stworzony  w oparciu o teorię zbiorów. Używając rozwiązań iteracyjnych nie stosujemy się do podstaw języka. Gdy używamy rozwiązania opartego na zbiorach podajemy zapytanie jako prosty deklaratywny język zapytań. Pozostawiamy silnikowi bazy danych na opracowanie tego jak zwrócić rozwiązanie problemu który zdefiniowaliśmy za pomocą zapytania. Używając metod iteracyjnych musimy zaimplementować jak zwrócić rozwiązanie co jest dużo dłuższe i trudniejsze do śledzenia i utrzymania. Inną przyczyną użycia rozwiązań bazujących na zbiorach jest wydajność. Iteracyjne konstrukcje w T-SQL są mało wydajne. Pętle w T-SQL nie są tak szybkie jak w językach programowania. pobranie rekordu z kursora za pomocą komendy FETCH NEXT na duży narzut z nim związany. Nie ma takiego narzutu kiedy SQL Serwer przetwarza rozwiązanie oparte na zbiorach, nawet jeżeli wewnętrzny plan wykonania zawiera iteracje. należy pamiętać, że są sytuacje w których rozwiązania iteracyjne są wydajniejsze, nawet pomimo narzutu spowodowanego racą na pojedynczych rekordach. może się tak zdarzyć kiedy optymalizatorowi nie uda się stworzyć wydajnego planu dla zapytania i nie znamy sposobu na lepsze dostrojenie zapytania.

Iteracje dla operacji, które muszą być wykonane na każdym wierszu

Niektóre zadania musza być wykonane z użyciem metod iteracyjnych. Przykładowo zadania administracyjne które muszą być wykonane na każdym obiekcie w zbiorze obiektów jak np. zbiór baz danych, tabel czy też indeksów. trzeba odpytać obiekt systemowy aby zwrócił zbiór szukanych obiektów, iterować na zbiorze wynikowym wiersz po wierszu i wykonywać zadanie na każdym z otrzymanych obiektów. Przykładem takiego zadania jest przebudowa indeksów które mają za wysoki stopień fragmentacji. Innym przykładem iteracyjnego zadania jest sytuacja w której mamy procedurę składowaną która wykonuje jakieś zadania dla klienta wprowadzonego na wejściu. W ciele procedury zaimplementowane jest wiele zadań. Logika nie może być zaimplementowana dla wielu klientów jednocześnie. Oto przykład takiej procedury:

USE TSQL2012;
IF OBJECT_ID('Sales.ProcessCustomer') IS NOT NULL
DROP PROC Sales.ProcessCustomer;
GO
CREATE PROC Sales.ProcessCustomer (@custid AS INT)
AS
PRINT 'Processing customer ' + CAST(@custid AS VARCHAR(10));
GO

Instrukcja PRINT stanowi część w której normalnie zaimplementowalibyśmy logikę która musi być wykonana dla klienta. Załóżmy teraz, że musimy napisać skrypt który wykona tę procedurę dla każdego klienta w tabeli Sales.Customers. Aby tego dokonać trzeba iterować po wierszach tabeli z klientami, pobrać identyfikator pojedynczego klienta w każdej iteracji i wykonać procedurę dla tego identyfikatora. Takie rozwiązanie można zaimplementować za pomocą kursora. Poniżej przedstawiony jest sposób deklaracji kursora:

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]

Więcej szczegółów na temat deklaracji kursora można znaleźć w Books Online. Podstawowa deklaracja wymaga użycia komendy DECLARE, podania nazwy kursora, słowa kluczowego CURSOR po którym można dodać opcje kursora. Następnie podaje się słowo kluczowe FOR po którym używa się wyrażenia T-SQL którego wynik ma być iterowany. Można użyć opcji FAST_FORWARD która sprawi, że kursor jest tylko do odczytu i wykonuje się tylko w przód. Po deklaracji kursora otwieramy go za pomocą komendy OPEN. Po otworzeniu kursora używamy komendy FETCH NEXT do pobrania pierwszego rekordu kursora. Do pobrania rekordów używa się zadeklarowanych zmiennych do których zapisujemy wartości kolumn w odpowiedniej kolejności. Po pobraniu pierwszego rekordu należy iterować do następnych rekordów kursora za pomocą pętli WHILE aż do momentu kiedy funkcja @@FETCH_STATUS zwróci 0.Możliwe wartości zwracane to: 0 kiedy pobranie się powiodło, -1 gdy wiersz jest poza zestawem wyników i -2 kiedy brakuje pobranego wiersza. W ciele pętli wykonujemy pożądane operacje dla danego rekordu i wymuszamy pobranie następnego rekordu za pomocą komendy FETCH NEXT. Kiedy pętla się zakończy należy użyć komendy CLOSE do zamknięcia kursora i DEALLOCATE do dealokacji kursora. Oto kod realizujący kursor wykonujący procedurę dla każdego klienta:

SET NOCOUNT ON;
DECLARE @curcustid AS INT;
DECLARE cust_cursor CURSOR FAST_FORWARD
FOR
SELECT custid
FROM Sales.Customers;
OPEN cust_cursor;
FETCH NEXT
FROM cust_cursor
INTO @curcustid;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC Sales.ProcessCustomer @custid = @curcustid;
FETCH NEXT
FROM cust_cursor
INTO @curcustid;
END;
CLOSE cust_cursor;
DEALLOCATE cust_cursor;
GO

Można również rozwiązać to zadanie za pomocą innej metody iteracyjnej nie używającej kursora. Możemy użyć opcji zapytania TOP(1) dla posortowanego zapytania SELECT po czym wykonywać pętlę aż zostanie zwrócone NULL. Tak jak w rozwiązaniu z kursorem w każdej iteracji wykonujemy procedurę. Aby otrzymać kolejne identyfikatory używamy zapytania które w każdej pętli pobiera najmniejszy identyfikator który jest większy od poprzedniego. oto skrypt:

SET NOCOUNT ON;
DECLARE @curcustid AS INT;
SET @curcustid = (
SELECT TOP (1) custid
FROM Sales.Customers
ORDER BY custid
);
WHILE @curcustid IS NOT NULL
BEGIN
EXEC Sales.ProcessCustomer @custid = @curcustid;
SET @curcustid = (
SELECT TOP (1) custid
FROM Sales.Customers
WHERE custid > @curcustid
ORDER BY custid
);
END;
GO

Jest to rozwiązanie iteracyjne ponieważ nie traktuje zbioru jako całości tylko wykonuje operacje na pojedynczych rekordach. Ponadto rozwiązania na zbiorach nie używają kolejności danych. Z perspektywy wydajności rozwiązanie z kursorem nie wymaga żadnych indeksów. Drugie rozwiązanie wymaga indeksu na kolumnie custid. Bez tego indeksu zapytanie za każdym razem będzie skanować całą tabelę i będzie używać sortowania TOP N w planie wykonania.

Kursory kontra działania na zbiorach dla zadań związanych z przetwarzaniem danych

Jak wspomniano wcześniej zasadniczo należy używać działań na zbiorach i rozważać iteracyjne rozwiązania w wyjątkowych sytuacjach. W tej sekcji porównamy rozwiązania obu typów. Stwórzmy funkcję dbo.GetNums w bazie danych TSQL2012 która pomoże nam wygenerować przykładowe dane. Funkcja ta przyjmuje dwie liczby całkowite na wejściu i zwraca ciąg liczb między nimi jako zbiór:

IF OBJECT_ID('dbo.GetNums', 'IF') IS NOT NULL DROP FUNCTION dbo.GetNums;
GO
CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM L5)
SELECT @low + rownum - 1 AS n
FROM Nums
ORDER BY rownum
OFFSET 0 ROWS FETCH FIRST @high - @low + 1 ROWS ONLY;
GO

Stwórzmy tabelę dbo.Transactions która będzie przechowywać informacje o transakcjach bankowych. Tabela ma trzy kolumny actid, tranid i val. Poniższy kod tworzy tabelę i wypełnia ją 1 milionem wierszy dla 100 kont po 10000 transakcji.

IF OBJECT_ID('dbo.Transactions', 'U') IS NOT NULL DROP TABLE dbo.Transactions;
CREATE TABLE dbo.Transactions
(
actid INT NOT NULL, -- partitioning column
tranid INT NOT NULL, -- ordering column
val MONEY NOT NULL, -- measure
CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid)
);
DECLARE
@num_partitions AS INT = 100,
@rows_per_partition AS INT = 10000;
TRUNCATE TABLE dbo.Transactions;
INSERT INTO dbo.Transactions WITH (TABLOCK) (actid, tranid, val)
SELECT NP.n, RPP.n,
(ABS(CHECKSUM(NEWID())%2)*2-1) * (1 + ABS(CHECKSUM(NEWID())%5))
FROM dbo.GetNums(1, @num_partitions) AS NP
CROSS JOIN dbo.GetNums(1, @rows_per_partition) AS RPP;
GO

Naszym zadaniem jest utworzenie rozwiązania które oblicza na każdej transakcji saldo rachunku w tym punkcie. Saldo konta po określonej transakcji jest obliczane jako suma wszystkich wartości transakcji od początku działalności w rachunku do bieżącej transakcji. Poniższy kod implementuje iteracyjne rozwiązanie tego problemu:

SET NOCOUNT ON;
DECLARE @Result AS TABLE (
actid INT
,tranid INT
,val MONEY
,balance MONEY
);
DECLARE @actid AS INT
,@prvactid AS INT
,@tranid AS INT
,@val AS MONEY
,@balance AS MONEY;
DECLARE C CURSOR FAST_FORWARD
FOR
SELECT actid
,tranid
,val
FROM dbo.Transactions
ORDER BY actid
,tranid;
OPEN C
FETCH NEXT
FROM C
INTO @actid
,@tranid
,@val;
SELECT @prvactid = @actid
,@balance = 0;
WHILE @@fetch_status = 0
BEGIN
IF @actid <> @prvactid
SELECT @prvactid = @actid
,@balance = 0;
SET @balance = @balance + @val;
INSERT INTO @Result
VALUES (
@actid
,@tranid
,@val
,@balance
);
FETCH NEXT
FROM C
INTO @actid
,@tranid
,@val;
END
CLOSE C;
DEALLOCATE C;
SELECT *
FROM @Result;
GO

Kursor jest oparty na zapytaniu zwracającym wiersze z tabeli  posortowanej wg identyfikatora konta i identyfikatora transakcji. Kod iteruje jedną transakcję w czasie. Dopóki konto nie ulega zmianie kod sumuje wartość transakcji do zmiennej @Balance i przechowuje wiersz z informacja o aktualnej transakcji i saldzie w zmiennej tabelarycznej @Result. jeżeli identyfikator konta jest inny niż poprzedni to oznacza, że aktualna transakcja należy już do innego konta więc zmienna @Balance jest ustawiana na 0. Po wykonaniu wszystkich iteracji wynik odczytujemy ze zmiennej tabelarycznej @Result. Ze względu na powolność iteracji i narzut dla każdego pobieranego z kursora rekordu operacja ta trwała kilkadziesiąt sekund. Poniżej przedstawione jest rozwiązanie bazujące na zbiorach używające funkcji agregujących:

SELECT actid, tranid, val,
SUM(val) OVER(PARTITION BY actid
ORDER BY tranid
ROWS UNBOUNDED PRECEDING) AS balance
FROM dbo.Transactions;

Zapytanie używa funkcji SUM sumującej wszystkie wartości dla  transakcji tego samego konta (PARTITION BY actid), od pierwszej transakcji do aktualnej (ORDER BY tranid ROWS UNBOUNDED PRECEDING). Zapytanie użyło jednego skanowania danych i zostało wykonane w kilka sekund. Kluczem do skuteczności tego rozwiązania jest to, że istnieje różnica między operacjami które powinny być wykonane logicznie a typ co optymalizator zapytań rzeczywiście wykonał. Logicznie dla każdego wiersza funkcja okna generuje ramkę dla wierszy transakcji należących do tego samego konta, od pierwszej transakcji w kolejności do aktualnie przetwarzanej. W rzeczywistości optymalizator zdaje sobie sprawę, że może on po prostu zeskanować dane raz i obliczyć aktualną sumę bieżącą, może on po prostu dodać wartość bieżącego wiersza do sumy bieżącej które została obliczona w poprzednim wierszu. Oznacza to, że to rozwiązanie skaluje się liniowo. Oznacza to, że jeżeli liczba wierszy dla konta zostanie zwiększona przez czynnik F to praca do wykonania również wzrośnie o czynnik F. W związku z tym czas wykonania wzrośnie w podobny sposób. Nie wszystkie rozwiązania oparte na zbiorach skalują się tak dobrze. Rozważmy poniższy przykład:

SELECT T1.actid, T1.tranid, T1.val,
SUM(T2.val) AS balance
FROM dbo.Transactions AS T1
JOIN dbo.Transactions AS T2
ON T2.actid = T1.actid
AND T2.tranid <= T1.tranid
GROUP BY T1.actid, T1.tranid, T1.val;

Logicznie rozwiązanie to dopasowuje dla każdego wiersza z tabeli T1 wszystkie wiersze które mają ten sam identyfikator konta i identyfikator transakcji mniejszy lub równy od aktualnego. Problemem tego rozwiązania jest to, że fizyczny plan wykonania musi przetworzyć za dużo wierszy. optymalizator nie może użyć optymalizacji ścieżki która przeskanuje dane tylko raz. Zapytanie to skaluje się kwadratowo (N2). Zapytanie to wykonuje się kilkadziesiąt minut. Rozwiązanie z funkcją okna nie jest możliwe w wersjach mniejszych niż SQL Serwer 2012. Dlatego we wcześniejszych wersjach wydajniejsza była wersja z kursorem (poza sytuacją w której jest mało wierszy do przetworzenia).

Ćwiczenia

I. Agregacje za pomocą kursora

  1. Otwórz nowe okno SSMS i połącz się do bazy TSQL2012.
    USE TSQL2012;
    GO
    
  2. Utwórz indeks na kolumnach actid i val aby pomóc wyliczać maksymalną wartość.
    CREATE INDEX idx_actid_val ON dbo.Transactions(actid, val);
    
  3. Napisz rozwiązanie oparte o kursor które będzie obliczało maksymalną wartość transakcji dla danego konta:
    SET NOCOUNT ON;
    DECLARE @Result AS TABLE (
    	actid INT
    	,mx MONEY
    	);
    DECLARE @actid AS INT
    	,@val AS MONEY
    	,@prevactid AS INT
    	,@prevval AS MONEY;
    DECLARE tx_cursor CURSOR FAST_FORWARD
    FOR
    SELECT actid
    	,val
    FROM dbo.Transactions
    ORDER BY actid
    	,val;
    OPEN tx_cursor;
    FETCH NEXT
    FROM tx_cursor
    INTO @actid
    	,@val;
    SELECT @prevactid = @actid
    	,@prevval = @val;
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	IF @actid <> @prevactid
    		INSERT INTO @Result (
    			actid
    			,mx
    			)
    		VALUES (
    			@prevactid
    			,@prevval
    			);
    	SELECT @prevactid = @actid
    		,@prevval = @val;
    	FETCH NEXT
    	FROM tx_cursor
    	INTO @actid
    		,@val;
    END
    IF @prevactid IS NOT NULL
    	INSERT INTO @Result (
    		actid
    		,mx
    		)
    	VALUES (
    		@prevactid
    		,@prevval
    		);
    
    CLOSE tx_cursor;
    DEALLOCATE tx_cursor;
    SELECT actid
    	,mx
    FROM @Result;
    GO
    

II. Agregacje za pomocą operacji na zbiorach

  1. Napisz rozwiązanie oparte o operacje na zbiorach które będzie obliczało maksymalną wartość transakcji dla danego konta:
    SELECT actid, MAX(val) AS mx
    FROM dbo.Transactions
    GROUP BY actid;
    

Podsumowanie

  1. Rozwiązania oparte na zbiorach używają zapytań SQL które spełniają założenia modelu relacyjnego. Używają one tabel (zbiorów) jako całości. Rozwiązania te nie gwarantują, że dane zostaną przetworzone lub zwrócone w określonej kolejności.
  2. Niektóre zadania muszą być wykonywane w oparciu o metody iteracyjne np: zadania administracyjne wykonywane na pojedynczych obiektach lub procedury które muszą być wykonane dla poszczególnych wierszy w tabeli.
  3. Generalnie rekomenduje się użycie rozwiązań opartych na zbiorach i zachowanie rozwiązań iteracyjnych tylko dla wyjątkowych zadań.