Spis treści:

Kategoria:KursorySQL Server


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?

Policjant spisuje raport:
- ... ofiara wypadku leżała na chodni... hodni...
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:

CREATE TABLE CursorTest
(
  ID int NOT NULL,
  Name varchar(20)
);

WITH N10 AS
(
  SELECTFROM
  (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:

IDName
1Record 1
2Record 2
3Record 3
4Record 4
5Record 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:

--Deklaracja kursora
DECLARE CounterCursor CURSOR FOR
SELECTFROM 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:

SELECT COUNT(*) FROM CursorTest WHERE ID % 1000 = 0

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:

DECLARE Kursor CURSOR FOR
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:

DECLARE Kursor CURSOR FOR
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:

Record 1000, Record 2000, Record 3000, Record 4000, Record 5000 ...

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:

DECLARE CounterCursor CURSOR FAST_FORWARD FOR
SELECTFROM 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

, 2013-12-20

Komentarze:

Gość (2016-08-03 21:15:20)
Przetestowałem szybkość Twojego kursora i zwykłego "selecta", puszczając je w pętlach po 10x. Owszem, kursor jest powolny, u mnie ten z "fast forward" leciał przez 26 sekund, ale zwykły "select" nie był wcale aż taki szybki, gdyż wykonywał się przez około 1 sekundę. Tylko że ja przy każdym obrocie pętli czyściłem bufor, plany zapytań, itd., czyli:
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?
PD (2016-08-04 18:18:27)
To było dość dawno i nie pamiętam. Szkoda, że nie zapisałem też wersji SQL Server na której to było wykonywane. Ciekawe byłoby również zestawienie wersji. Na środowisku, do którego mam teraz dostęp (SQL Server 2016), po wyczyszczeniu bufora podręcznego, wyszło:
SELECT - 78 ms
Kursor - 20954 ms
Gość (2016-08-04 22:29:19)
Dzięki za wyjaśnienia. Powtórzyłem zwykłego "selecta" dla 100 obrotów pętli, i przy czyszczeniu uzyskałem średnią wartość 997 milisekund (bardzo zbliżone wartości), a dla odczytu z pamięci 179 ms, przy czym dla tak krótkich czasów rozrzut wyników zawsze mam b.duży (im krótsze czasy tym większy ich rozrzut).
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.
Dodaj komentarz
Wyślij
Ostatnie komentarze
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !