Kursory w SQL Server
Podejście proceduralne w świecie zbiorów
Relacyjne bazy danych, w tym SQL Server, Oracle i wiele innych, oparte są na algebrze zbiorów. To zbiór, jako nieuporządkowany zestaw rekordów, jest tutaj podmiotem. Dla programistów i administratorów, wychowanych na proceduralnych lub proceduralno-obiektowych jęzkach programowania, logika oparta na zbiorach wydaje się trochę nieintuicyjne. To problem wszystkich tych, którzy wychowywali się na asemblerze, C, C++, Pascalu, Javie, C# i innych podobnych językach. Sposoby myślenia w kategoriach relacyjnych i proceduralnych są od siebie mocno oddalone. Istnieje wiele znanych algorytmów proceduralnych, których wersja relacyjna albo jest skomplikowana, albo nie potrafimy jej odpowiednio przełożyć. Swoją drogą, jak pokazuje doświadczenie, większość rozwiązań opartych na kursorach da się przerobić na równoważną postać obiektową, preferowaną przez silniki relacyjnych baz danych. Coś jak w załączonym obok dowcipie - znajdujemy rozwiązanie, ale czy jest to rozwiązanie najlepsze z możliwych?
- ... ofiara wypadku leżała na
Po czym przesunął ciało i spokojnie dokończył:
- ... na ulicy.
Pomijając kwestie czystości kodu, wydajności, innego sposobu myślenia i kilku innych czynników, w relacyjnych bazach danych istniała pewna luka. Pewien obszar, który należało zagospodarować. To właśnie ten obszar opanowały kursory. Przyjrzyjmy się zatem, jak one działają w SQL Server.
Przykład prostego kursora w SQL Server
Nie zamierzam przedłużać, bo pewnie większość czeka na konkretny przykład. Przygotujmy sobie prostą tabelę i wypełnijmy ją danymi:
(
ID int NOT NULL,
Name varchar(20)
);
WITH N10 AS
(
SELECT N FROM
(VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) N(N)
),
N100 AS
(
SELECT N1.N
FROM N10 N1
CROSS JOIN N10 N2
)
INSERT INTO CursorTest
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) N,
'Record '+CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS varchar(20))
FROM N100 N1
CROSS JOIN N100 N2
CROSS JOIN N100 N3
SELECT TOP 10 * FROM CursorTest
Może nie jest to takie oczywiste, ale powyższy fragment uzupełnia tabelę kolejnymi numerami porządkowymi wraz z odpowiadającymi im wartościami tekstowymi Record N:
| ID | Name |
|---|---|
| 1 | Record 1 |
| 2 | Record 2 |
| 3 | Record 3 |
| 4 | Record 4 |
| 5 | Record 5 |
| ... | ... |
Tabela nie ma większego sensu, ale nadaje się do pokazania operacji na kursorze. Zadaniem kursora jest wskazanie, ile jest rekordów, których identyfikator jest podzielny przez 1000. Popatrzmy na poniższy listing:
DECLARE CounterCursor CURSOR FOR
SELECT * FROM CursorTest
--Zmienne wykorzystywane w obliczeniach
DECLARE @ID int, @name varchar(20)
DECLARE @counter int=0
--Otworzenie kursora i pobranie pierwszego rekordu
OPEN CounterCursor
FETCH NEXT FROM CounterCursor INTO @ID, @name
--Jeżeli kursor zwrócił dane, wykonuj operacje
WHILE @@FETCH_STATUS = 0
BEGIN
--Operacja na kolejnych rekordach
IF @ID % 1000 = 0
SET @counter += 1
--Pobierz kolejne dane z kursora
FETCH NEXT FROM CounterCursor INTO @ID, @name
END
--Zamknij i zlikwiduj kursor.
CLOSE CounterCursor
DEALLOCATE CounterCursor
--Pobierz rezultat
SELECT @counter
Dość skomplikowany zapis jak na tak prostą operację, ale, powiedzmy to sobie szczerze, tak prostych operacji na kursorze nikt nie wykonuje. Chyba, że to przykład. Odpowiednikiem powyższej operacji jest taka oto instrukcja SELECT:
Jak można się przekonać zwykły SELECT jest nie tyle krótszy, co szybszy. Ale o tym później. Po napisaniu kilku kursorów można zauważyć, że mają one bardzo podobny schemat.
Szablon kursora
Każdy kursor musi wykonać pewne z góry określone operacje. Każdy kursor musi być zdefiniowany przez instrukcję SELECT, każdy musi być otwarty, każdy jest przetwarzany w jakiejś pętli i każdy powinien być na końcu zamknięty i usunięty. Przyjrzyjmy się pokazanemu poniżej fragmentowi:
SELECT ...
OPEN Kursor
FETCH NEXT FROM Kursor INTO (zmienne)
WHILE @@FETCH_STATUS = 0
BEGIN
(operacje)
FETCH NEXT FROM Kursor INTO (zmienne)
END
CLOSE Kursor
DEALLOCATE Kursor
Pokazany poniżej fragment można wręcz skopiować, zmienić Kursor na pożądaną nazwę kursora, zadeklarować wymagane do obliczeń zmienne i użyć ich w instrukcji FETCH. Nie należy oczywiście zapominać o kodzie właściwym, który coś z tymi danymi, zwróconymi z kursora, robi. Ten, jak to nazwałem, kod właściwy, może wykonywać cokolwiek - może to być proste wyrażenie, operacja SELECT, INSERT, UPDATE, DELETE, MERGE, może to być wywołanie procedury lub funkcji, czy... kolejny kursor. Nie jesteśmy w żaden sposób ograniczeni.
Kursor oparty na szablonie
Wiadomo nie od dziś, że najlepszy do nauki jest odpowiedni przykład. Przyjrzyjmy się zatem drugiemu przykładowi, którego zadaniem jest wypisanie pięciu pierwszych nazw spełniających podane kryteria. Kryteria będą proste - identyfikator musi być podzielny przez tysiąc, a nazwa nie może być równa NULL. Listing pokazany jest poniżej:
SELECT Name FROM CursorTest
WHERE ID % 1000 = 0 AND Name IS NOT NULL
DECLARE @name varchar(20)
DECLARE @output varchar(120)
DECLARE @totalAccumulated int=0
OPEN Kursor
FETCH NEXT FROM Kursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @output = COALESCE(@output+', '+@name, @name)
SET @totalAccumulated += 1
IF (@totalAccumulated = 5)
BEGIN
SET @output += ' ...'
BREAK
END
FETCH NEXT FROM Kursor INTO @name
END
CLOSE Kursor
DEALLOCATE Kursor
SELECT @output Raport
Wynikiem działania powyższego kodu będzie taki oto łańcuch znaków:
Warto wiedzieć, że jest to rezultat przykładowy. Nie określiliśmy nigdzie sposobu sortowania, więc treść i kolejność nazw może się różnić. Zależy ona tylko i wyłącznie od użytego planu wykonania, czyli wewnętrznego algorytmu zastosowanego przez SQL Server.
Wydajność kursora
Temat wydajności kursorów jest dość złożony i zależy od wielu czynników. Można jednak śmiało powiedzieć i przyjąć jako żelazną zasadę - kursor jest znacznie, znacznie wolniejszy niż proste zapytanie SELECT. Kursor ma duże możliwości: można przeglądać rekordy w przód, w tył, można tworzyć kursory globalne, można wymusić blokowanie rekordów, na których operuje kursor. To wszystko wymaga obliczeń procesora i współudziału pamięci operacyjnej, a czasem również masowej. Generalnie najszybszy jest taki kursor, który porusza się tylko w jedną stronę i zakłada, że nikt przy jego udziale nie będzie modyfikował danych. Taki kursor definiujemy z opcją FAST_FORWARD. Jest druga opcja, która definiuje dane kursora jako dane tylko do odczytu - READ_ONLY, ale jest ona domyślnie ustawiana w ramach opcji FAST_FORWARD. Popatrzmy na drobną modyfikację pierwszego przykładu:
SELECT * FROM CursorTest
DECLARE @ID int, @name varchar(20)
DECLARE @counter int=0
OPEN CounterCursor
FETCH NEXT FROM CounterCursor INTO @ID, @name
WHILE @@FETCH_STATUS = 0
BEGIN
IF @ID % 1000 = 0
SET @counter+=1
FETCH NEXT FROM CounterCursor INTO @ID, @name
END
CLOSE CounterCursor
DEALLOCATE CounterCursor
SELECT @counter
Żeby nie być gołosłownym: wykonanie tej operacji na 1000000 rekordów zajęło na testowym serwerze około 10 sekund (bez opcji FAST_FORWARD blisko 20 sekund). Prosta instrukcja SELECT mieściła się w czasie poniżej 0,1 sekundy! Oznacza to ponadstukrotną różnicę! Warto o tym pamiętać, gdy zechcemy zastosować kursor. Warto zastanowić się, czy nie lepiej zastosować jakieś rozwiązanie oparte na zbiorach. Nawet kosztem dodatkowych operacji i tablic tymczasowych. Pole do optymalizacji jest kilkadziesiąt razy większe, niż nam się wydaje.
Temat kursorów jest obszerny i zostanie pewnie rozszerzony.
Zachęcam do dzielenia się swoimi problemami związanymi z kursorami w komentarzach. Może się okazać, że ktoś inny zna lepszą metodę, "niekursorową", pozwalającą rozwiązać dany problem.
Kategoria:KursorySQL Server
Komentarze:
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
CHECKPOINT;
bo gdy zwykły "select" był kilkakrotnie uruchamiany korzystając z danych z bufora i już przygotowanych planów zapytań, to też leciał przez 0,1 sekundy. A jak to Ty robiłeś, uruchamiałeś czyszczenie bufora i planów zapytań (czyli czysty odczyt danych z dysku), czy korzystałeś z danych zbuforowanych w pamięci operacyjnej?
SELECT - 78 ms
Kursor - 20954 ms
Dla kursora pętlę puściłem ponownie tylko 10x, bo nie chciałem czekać całego dnia na wynik ;) :
przy czyszczeniu średnia to 26463 ms z bardzo zbliżonymi wartościami, a z pamięci średnia 24122 ms też z bardzo zbliżonymi wartościami. Ciekawe że czyszczenie pamięci i planów nie wpływa jakoś znacząco na szybkość kursora. Widocznie jest on tak niewydajnym obiektem, że nic mu nie pomoże ;).
Wszystkie testy przeprowadzałem na SQL Server 2012 SP3 Express Edition.
Powiem Ci, że gdy po raz pierwszy czytałem o kursorach, to miałem trochę problemów z ich zrozumieniem, tzn ich idei. I to nie było związane z nawykami OOP, czy proceduralnymi, bo nie jestem programistą. Natomiast chodzi o to, że gdy dawniej uczyłem się SQL-a, to byłem od początku przyzwyczajony do myślenia w kategoriach operacji na kolumnach, a nie na wierszach. Dobrym przykładem takiego myślenia jest idea polecenia UPDATE, w której zmieniamy wartość w danej kolumnie, gdy np. w innej kolumnie jest coś innego. Czyli jak od początku od razy wyrośnie się na myśleniu "kolumnowym", to potem myślenie "rzędowe" przychodzi z trudnością. Zresztą chyba to właśnie myślenie kolumnowe jest naturalne dla SQL, a nie rzędowe.
Natrafiłem na ten artykuł przypadkiem, temat jest dość ciekawy ale i trudny. O ile rozumiem co dzieje w przykładach poniżej to zastanawia mnie ta składnia.
SELECT N FROM
(VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) N(N). Konkretnie zapis N(N). Będę wdzięczny za wyjaśnienia.
pozdrawiam