Wprowadzenie
W relacyjnych bazach danych operacje są wykonywane na zbiorze wierszy. Na przykład instrukcja SELECT zwraca zestaw wierszy nazywany zbiorem wyników. Czasami logika aplikacji musi działać z wierszem na raz, a nie z całym zestawem wyników na raz. W T-SQL jednym ze sposobów jest użycie CURSORA.
Jeśli masz umiejętności programistyczne, prawdopodobnie użyłbyś pętli typu FOR lub WHILE do iteracji po jednym elemencie na raz, zrób coś z dane i praca jest wykonana. W T-SQL CURSOR jest podobnym podejściem i może być preferowany, ponieważ ma tę samą logikę. Ale pamiętaj, wybierz tę ścieżkę, a kłopoty mogą się pojawić.
W niektórych przypadkach używanie CURSOR nie powoduje takiego bałaganu, ale generalnie należy ich unikać. Poniżej pokażemy kilka przykładów, w których użycie CURSORa stwarza problemy z wydajnością i zobaczymy, że to samo zadanie można wykonać na wiele innych sposobów.
Do celów tej demonstracji użyjemy bazy danych AdventureWorks2012 i powiedzmy, że chcemy pobrać jakieś dane z. table, dla każdego produktu, którego wyprodukowanie zajmuje mniej niż jeden dzień, czyli z tabeli.
Przykład kursora
Zacznijmy od KURSORA i napiszmy następującą składnię:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
USE AdventureWorks2012
GO
ZADEKLARUJ @id int
ZADEKLARUJ kursorT KURSOR
–LOCAL STATIC
–LOCAL FAST_FORWARD
–LOCAL READ_ONLY FORWARD_ONLY
FOR
SELECT ProductId
Z AdventureWorks2012.Production.Product
GDZIE DaysToManufacture < = 1
OTWÓRZ kursorT
FETCH NASTĘPNY Z kursoraT INTO @id
WHILE @@ FETCH_STATUS = 0
BEGIN
SELECT * FROM Production.ProductInventory
WHERE ProductID = @ id
POBIERZ NASTĘPNY OD kursoraT DO @id
KONIEC
ZAMKNIJ kursorT
DEALLOCATE kursorT
|
Po krótkiej przerwie na kawę , zapytanie zakończyło wykonywanie, zwracając 833 wiersze w czasie pokazanym poniżej. Ważne jest, aby wspomnieć, że wybrane powyżej składnie służą tylko do celów demonstracyjnych i nie dostosowałem indeksów, aby przyspieszyć działanie.
W wykonaniu kursora mamy dwa kroki. Krok pierwszy, pozycjonowanie, gdy kursor ustawia swoją pozycję w wierszu z zestawu wyników. Krok drugi, pobieranie, kiedy pobiera dane z tego konkretnego wiersza w operacji zwanej FETCH.
W naszym przykładzie kursor ustawia swoją pozycję w pierwszym wierszu zwróconym przez pierwszy SELECT i pobiera wartość ProductID, która pasuje do warunku WHERE w zmiennej @id. Następnie drugi SELECT używa wartości zmiennej do pobrania danych. a następny wiersz jest pobierany. Te operacje są powtarzane, dopóki nie będzie już żadnych wierszy do pracy.
Wreszcie, składnia CLOSE zwalnia bieżący zestaw wyników i usuwa blokady z wierszy używanych przez kursor, a DEALLOCATE usuwa odniesienie do kursora.
Nasze tabele demonstracyjne są stosunkowo małe i zawierają około 1000 i 500 rzędów. Gdybyśmy musieli przeglądać tabele z milionami wierszy, zajęłoby to dużo czasu, a wyniki by nas nie zadowalały.
Dajmy naszemu kursorowi kolejną szansę i odkomentujmy wiersz –LOCAL STATIC. Istnieje wiele argumentów, których możemy użyć w definicji kursora, więcej na ten temat w tym linku Argumenty CURSOR, ale na razie skupmy się na tym, co oznaczają te dwa słowa.
Kiedy określimy słowo kluczowe LOCAL, zasięg kursora jest lokalny dla wsadu, w którym został utworzony i jest ważny tylko w tym zakresie. Po zakończeniu wykonywania partii kursor jest automatycznie zwalniany. Do kursora można również odwoływać się za pomocą procedury składowanej, wyzwalacza lub lokalnej zmiennej kursora w partii.
Słowo kluczowe STATIC tworzy tymczasową kopię danych używanych przez kursor w tempdb w tabeli tymczasowej . Tutaj mamy kilka pułapek. Kursor STATIC jest tylko do odczytu i jest również nazywany kursorem migawki, ponieważ działa tylko z danymi od momentu otwarcia, co oznacza, że nie będzie wyświetlał żadnych zmian wprowadzonych w bazie danych w zbiorze danych używanych przez kursor.Zasadniczo żadne aktualizacje, usunięcia lub wstawienia dokonane po otwarciu kursora nie będą widoczne w zestawie wyników kursorów, chyba że zamkniemy i ponownie otworzymy kursor.
Pamiętaj o tym przed użyciem tych argumentów i sprawdź, czy odpowiada Twoim potrzebom. Ale zobaczmy, czy nasz kursor jest szybszy. Poniżej mamy wyniki:
12 sekund to dużo lepsze niż 4 minuty i 47 sekund, ale pamiętaj, że ograniczenia wyjaśnione powyżej.
Jeśli uruchomimy składnię przy użyciu tego argumentu LOCAL READ_ONLY FORWARD_ONLY, otrzymamy te same wyniki. Kursor READ_ONLY FORWARD_ONLY można przewijać tylko od pierwszego wiersza do ostatniego. Jeśli brakuje słowa kluczowego STATIC, kursor jest dynamiczny i używa planu dynamicznego, jeśli jest dostępny. Ale są przypadki, w których plan dynamiczny jest gorszy od statycznego.
Co się stanie, gdy odkomentujemy – LOCAL FAST_FORWARD?
FAST_FORWARD jest odpowiednikiem kursora READ_ONLY i FORWARD_ONLY, ale ma możliwość wyboru lepszego planu spośród statycznego lub dynamicznego.
LOCAL FAST_FORWARD wydaje się być najlepszym wyborem ze względu na jego elastyczność wybierz między planem statycznym a dynamicznym, ale FORWARD_ONLY również bardzo dobrze spełnia swoje zadanie. Chociaż otrzymaliśmy wynik średnio w 12 sekund przy użyciu obu tych metod, argumenty te należy odpowiednio przetestować przed wybraniem jednej z nich.
Jest wiele sposobów uzyskania tego samego wyniku, znacznie szybciej i przy użyciu mniejszy wpływ na wydajność.
Alternatywa kursora
Jedną z metod jest użycie JOIN, a jak zobaczymy dalej, wyniki są znacznie lepsze.
Po pierwsze, musimy włączyć czas statystyki, aby zmierzyć czas wykonywania programu SQL Server i wykonać INNER JOIN między dwiema tabelami,. i . w kolumnie ProductID. Po naciśnięciu przycisku EXECUTE uzyskaliśmy te same wyniki w ciągu 330 ms, w porównaniu do czasu 04:47 z metody kursora, i mamy uśmiech na twarzy.
|
1
2
3
4
5
6
7
8
9
|
UŻYJ AdventureWorks2012
PRZEJDŹ
USTAW CZAS STATYSTYKI WŁĄCZONY
WYBIERZ * FROM Production.ProductInventory jako pinv
INNER JOIN Production.Product as pp
ON pinv.ProductID = pp.ProductID
WHERE pp.DaysToManufacture < = 1
|
Nie musimy iterować po każdym wierszu, aby uzyskać to, czego potrzebujemy, nie mamy więcej pętli, nie ma klauzuli while, nie ma iteracji Zamiast tego pracujemy z zestawami danych, uzyskując szybciej to, czego chcemy i pisząc mniej kodu.
Właściwe użycie kursora
Teraz, gdy widzieliśmy, ile szkód kursor może zrobić, zobaczmy przykład, w którym możemy go wykorzystać.
Załóżmy, że chcemy wybrać rozmiar i liczbę wierszy tylko dla niektórych tabel z bazy danych. Aby to osiągnąć, otrzymamy wszystkie nazwy tabel na podstawie kryteriów z information_schema.tables i za pomocą CURSOR przejdziemy w pętlę przez każdą z nazw tabel i wykonamy procedurę składowaną sp_spaceused, przekazując po jednej nazwie tabeli, aby uzyskać potrzebne informacje .
Użyjemy tej samej bazy danych AdventureWorks2012 i pobierzemy wszystkie tabele ze schematu Sales, który zawiera nazwę „Sales”. Dla każdej zwróconej nazwy tabeli chcemy zobaczyć wszystkie informacje z information_schema.tables.
Poniżej mamy składnię T-SQL i uzyskane wyniki:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
USE AdventureWorks2012
GO
DECLARE @TableName VARCHAR (50) – nazwa tabeli ze schematu „Sales”
DECLARE @Param VARCHAR (50) – parametr procedury „sp_spaceused”
ZADEKLARUJ db_cursor KURSOR DLA
– wybierz tylko tablicę „Sprzedaż” es
SELECT TABLE_NAME FROM information_schema.tables
WHERE TABLE_NAME, na przykład „% Sales%” i TABLE_TYPE = „BASE TABLE”
OTWÓRZ db_cursor
FETCH NEXT FROM db_cursor INTO @TableName
WHILE @@ FETCH_STATUS = 0
BEGIN
–concatenate każdą nazwę tabeli w zmiennej i przekaż ją do procedury składowanej
SET @ Param = „Sales.”+ @ TableName
– wykonaj procedurę składowaną dla każdej nazwy tabeli naraz
EXEC sp_spaceused @Param
FETCH NEXT FROM db_cursor INTO @TableName – pobiera nazwę następnej tabeli
END
CLOSE db_cursor
DEALLOCATE db_cursor
|
Jest to jedna z metod, w której CURSOR jest pomocna poprzez iterację niektórych danych po jednym wierszu na raz i uzyskuje wymagany wynik. W tym konkretnym przypadku kursor wykonuje zadanie bez wpływu na wydajność i jest łatwy w użyciu.
Wnioski
Mamy to. Pokazaliśmy kilka przykładów z dobrymi, złymi i brzydkimi przy użyciu kursorów. W większości przypadków możemy użyć JOINS, nawet WHILE, SSIS pakiety lub inne alternatywne metody, aby szybciej uzyskać ten sam wynik, z mniejszym wpływem na wydajność, a nawet pisząc mniej wierszy składni.
Kiedy mają do czynienia ze środowiskiem OLTP i dużymi zbiorami danych do przetworzenia, słowo „CURSOR” nie powinno być używane.
W SQL dobrym zwyczajem jest myślenie o wykonywaniu operacji na zbiorach danych, zamiast myśleć w sposób programistyczny, używając iteracji lub pętli, ponieważ tego rodzaju podejście nie jest zalecane ani przeznaczone do takiego użycia. Próba użycia pętli typu FOR lub FOREACH z języków programowania i skojarzenie tej logiki z operacjami SQL jest przeszkodą w znalezieniu odpowiedniego rozwiązania dla naszych potrzeb. Musimy myśleć o operacjach opartych na zbiorach, a nie o jednym wierszu na raz, aby uzyskać potrzebne dane.
Kursory mogą być używane w niektórych aplikacjach do operacji serializowanych, jak pokazano w powyższym przykładzie, ale generalnie powinny należy ich unikać, ponieważ mają one negatywny wpływ na wydajność, zwłaszcza podczas pracy z dużymi zbiorami danych.
- Autor
- Najnowsze posty
Posiadam licencjat z inżynierii komputerowej, a także umiejętności w zakresie .NET, C # i Windows Server. Mam doświadczenie w dostrajaniu i monitorowaniu wydajności, tworzeniu T-SQL, strategii tworzenia kopii zapasowych, administrowaniu i konfigurowaniu baz danych.
Pasjonuję się informatyką, grami wideo, dobrymi filmami, a muzyka electro-house pomaga mi pracować w biurze noisy.
Wyświetl wszystkie posty Sergiu Oneta
- Korzystanie z kursorów SQL Server – zalety i wady – 23 marca 2016 r.
