By: Daniel Farina | Updated: 2019-09-12 | Comments (2) | Related: More > T-SQL
Problem
zawsze słyszałeś, że powinieneś unikać kursorów w kodzie T-SQL jako najlepszej praktyki serwera SQL, ponieważ kursy są szkodliwe do wykonania i czasami powodować problemy. Ale czasami istnieje potrzeba zapętlenia danych po jednym wierszu na raz, więc w tej poradzie przyjrzymy się porównaniu Jak zrobić pętlę bez użycia kursora.,
rozwiązanie
wszyscy wiemy, że SQL Server, jak każda relacyjna baza danych pozwala użytkownikowi na wykonywanie operacji opartych na Ustawieniach. Ponadto, podobnie jak wielu dostawców baz danych, SQL Server zawiera rozszerzenie proceduralne, które jest językiem T-SQL. Dodaje konstrukcje Znalezione w językach procesowych, pozwalając programistom na prostsze kodowanie. Budowle te zostały dodane nie bez powodu i czasami jest to jedyne podejście do danego zadania.,
używanie pętli While zamiast kursorów w SQL Server
Jeśli kiedykolwiek pracowałeś z kursorami, ten tytuł może być nieco mylący, ponieważ w końcu Kursory używają konstrukcji while do iteracji między wierszami. Ale poza tym, chcę ci pokazać, że w pewnych okolicznościach, gdy używamy kursora do iterateover zbiór wierszy, możemy zmienić go na pętlę while. W takich przypadkach jedynym wyzwaniem będzie wybór odpowiedniego warunku wyjścia.,
plusy i minusy używania kursorów do iteracji wierszy tabeli w SQL Server
nie wszystko jest źle z kursorami, mają też pewne zalety w innych technikach zapętlania.
- Kursory można aktualizować: podczas tworzenia kursora, można użyć zapytania do zdefiniowania go za pomocą instrukcji DECLARE CURSOR. Korzystając z opcji UPDATE w instrukcji cursorcreation, można zaktualizować kolumny znajdujące się w kursorze.,
- możesz poruszać się do przodu i do tyłu za pomocą kursora: za pomocą opcji przewijania w instrukcji DECLARE CURSOR możesz poruszać się po rekordach kursora w innych kierunkach za pomocą opcji fetch FIRST, LAST, PRIOR, NEXT, RELATIVE andabsolute. Należy pamiętać, że opcja przewijania jest niezgodna z opcjami FORWARD_ONLYand FAST_FORWARD.
- Kursory mogą być przekazywane do procedur składowanych: jeśli użyjesz opcji GLOBAL, aby utworzyć kursor, można go użyć w dowolnej procedurze składowanej lub wsadowej wykonywanej w tym samym połączeniu. Pozwala to na używanie kursorów w zagnieżdżonych procedurach składowanych.,
- Kursory mają wiele różnych opcji: z kursorami masz możność używać różnych opcji, które wpływają na ich zachowanie w odniesieniu do blokowania.
- Kursory nie potrzebują warunku: używając kursorów, obsługujesz zestaw wierszy jako rekord. Pozwala to na poruszanie się po kursorze bez konieczności posiadania warunku logicznego. Na przykład, można utworzyć kursor z nazwą baz danych znajdujących się na instancji SQL Server bez predyspozycji klucza zastępczego do pracy jako warunek testowy, jak w pętli WHILE.,
są też pewne negatywne aspekty, o których powinieneś pamiętać używając kursorów, a nie innych opcji zapętlania.
- Jeśli używasz globalnych kursorów w swoim kodzie, ryzykujesz facingerrors z powodu zamknięcia kursora przez jakąś procedurę składowaną zagnieżdżoną w Twoim kodzie.
- Zwykle Kursory mają mniejszą wydajność niż równoważna pętla przy użyciu WHILEloop lub CTE.
plusy i minusy używania pętli While do iteracji wierszy tabeli w SQL Server
istnieją również zalety używania pętli WHILE w porównaniu z kursorem.,
- podczas gdy pętle są szybsze od kursorów.
- podczas gdy pętle używają mniej blokad niż Kursory.
- mniejsze wykorzystanie Tempdb: podczas gdy pętle nie tworzą kopii danych intempdb, jak robi to kursor. Pamiętaj, że kursory, w zależności od opcji, których używasz do ich tworzenia, mogą spowodować utworzenie tabel tymczasowych.
Następna lista opisuje negatywne aspekty pętli WHILE.
- poruszanie się do przodu lub do tyłu jest złożone: aby poruszać się do przodu lub do tyłu w aloop musisz dynamicznie zmieniać stan iteracji wewnątrz pętli.,Wymaga to dodatkowej ostrożności; w przeciwnym razie możesz skończyć w nieskończonej pętli.
- ryzyko nieskończonej pętli: w porównaniu z kursorem, nie masz ustalonego zestawu danych do pętli (tzn. danych zwracanych przez stan SELECT w deklaracji kursora), zamiast tego, gdy używasz pętli WHILE, musisz zdefiniować granicę z wyrażeniem, które jest obliczane na true lub false.
budowanie środowiska testowego dla kursorów i pętli
aby to przetestować, użyję tabeli z kolumną tożsamości (CursorTestID),kolumną varchar (Filler) i kolumną bigint (RunningTotal).,
CREATE TABLE CursorTest( CursorTestID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Filler VARCHAR(4000), RunningTotal BIGINT )GO
chodzi o to, aby zapętlić wiersze tabeli uporządkowane według kolumny CursorTestID i zaktualizować kolumnę RunningTotal o sumę wartości CursorTestID column oraz wartość kolumny RunningTotal poprzedniego wiersza.
ale przed uruchomieniem, najpierw musimy wygenerować kilka wierszy testowych z następnym skryptem.,
INSERT INTO dbo.CursorTest ( Filler, RunningTotal )VALUES ( REPLICATE('a', 4000), 0 )GO 500000
na powyższym skrypcie zauważysz, że użyłem tylko jednego polecenia insert i skorzystałem z separatora wsadowego (polecenie GO 500000) jako skrótu do wykonania tej instrukcji insert 500000 razy. Możesz przeczytać więcej o tej metodzie, aby powtórzyć wykonanie wsadowe w tej poradzie:Wykonywanie partii T-SQL wiele razy za pomocą GO.
przykład podstawowego kursora do pętli przez wiersze tabeli w SQL Server
stwórzmy kursor do wypełnienia kolumny RunningTotal. Zwróć uwagę na nextscript, że zadeklarowałem kursor za pomocą opcji FAST_FORWARD., Odbywa się to w celu zwiększenia wydajności kursora, ponieważ według Microsoft argument theFAST_FORWARD „określa kursor FORWARD_ONLY, READ_ONLY z włączoną performanceoptimizations”. Innymi słowy, instruujemy SQL Server, aby korzystał z kursora tylko do odczytu, który może poruszać się tylko do przodu i przewijać od pierwszego do ostatniego wiersza.
DECLARE @CursorTestID INT;DECLARE @RunningTotal BIGINT = 0; DECLARE CUR_TEST CURSOR FAST_FORWARD FOR SELECT CursorTestID RunningTotal FROM CursorTest ORDER BY CursorTestID; OPEN CUR_TESTFETCH NEXT FROM CUR_TEST INTO @CursorTestID WHILE @@FETCH_STATUS = 0BEGIN UPDATE dbo.CursorTest SET RunningTotal = @RunningTotal + @CursorTestID WHERE CursorTestID = @CursorTestID; SET @RunningTotal += @CursorTestID FETCH NEXT FROM CUR_TEST INTO @CursorTestIDENDCLOSE CUR_TESTDEALLOCATE CUR_TESTGO
następnym obrazkiem jest Screen capture pokazujący wykonanie skryptu above.As jak widzicie, aktualizacja 500 000 rzędów naszego stołu testowego zajęła 3 minuty i 5 sekund.,
przykład podstawowej pętli While Do przełączania wierszy tabeli w SQL Server
teraz napiszę poprzedni skrypt unikając używania kursora. Nie zauważysz, że zawiera pętlę While, która jest prawie identyczna z tą w skrypciekursora. Jest tak, jak wcześniej powiedziałem, ponieważ nawet podczas pracy z kursorami musisz użyć iteracyjnej struktury sterowania.
następnym obrazkiem jest Screen capture z wykonaniem powyższego skryptu. Czas na uruchomienie pętli while niż kursora.,
kolejny przykład kursora SQL Server
Weźmy na przykład kursor w danych tipStandardize SQL Server za pomocą funkcji wyszukiwania tekstu i zastępowania. Słowo rady, aby uruchomić ten kod, należy postępować zgodnie z instrukcjami w poradzie, aby utworzyć środowisko testowe.
i tu jest kod kursora:
Jeśli rozrysujemy ten kod, widzimy, że jest jeden kursor, który przechodzi przez produkty tabeli, które skopiowałem poniżej.,
DECLARE load_cursor CURSOR FOR SELECT ProductID, ProductName FROM dbo.Products
przykład kursora SQL Server przekonwertowany na pętlę While
aby zastąpić ten kursor pętlą WHILE, musimy utworzyć tymczasową tabelę do implementacji tabeli tally. Dla wszystkich, którzy nie wiedzą, co to jest tallytable, możemy zdefiniować go jako tabelę, która zawiera parę kolumn składających się z klucza i jego wartości. W naszym konkretnym przypadku użyjemy sekwencyjnego klucza liczbowego zaczynającego się od 1, więc możemy go użyć jako iteratora. Ten klucz będzie powiązany z identyfikatorem produktu z tabeli produktów.,
na początku, ponieważ w tabeli produktów znajduje się klucz ProductID zdefiniowany jako identitymożesz pokusić się o obejście tego kroku, ale musisz wziąć pod uwagę, że w prawdziwym przypadku wiersz mógł zostać usunięty, dlatego nie będziesz mógł użyć identitycolumn jako iteratora. Dodatkowo wiersz może zostać usunięty podczas uruchamiania ourcode i może to prowadzić do błędów wykonania. Aby tego uniknąć, dodamy blokadę prób. Zajmę się tym dalej.
przed uruchomieniem pętli WHILE musimy ustawić jej stan start i stop., W związku z tym dodałem dwie nowe zmienne całkowite o nazwach @ Iterator i @MaxIterator.Zmienna @ MaxIterator służy do zachowania liczby pozycji w # TallyTabletable i ustawiamy jej wartość tylko raz przed uruchomieniem pętli. Zmienna @ Iterator jest zainicjalizowana do 1, ponieważ zdefiniowaliśmy ją jako liczbę początkową w sekwencji i będziemy zwiększać jej wartość przy każdej iteracji.
Następne kroki
- jesteś nowy w kursorach i potrzebujesz trochę praktyki? W następnej podpowiedzi znajdziesz Wyjaśnienie, łatwy do zrozumienia przykład kursora i więcej zalecanych odczytów:przykład kursora SQL Server.,
- Jeśli chcesz przekonwertować istniejące kursory w kodzie,aby ustawić zapytania oparte, spójrz na ten rozdział QL Server Convert Cursorto Set Based from theSQL Server Database Design Best Practices Tutorial.
- potrzebujesz innego przykładu użycia pętli While? Zapoznaj się z tą wskazówką, która pokaże Ci, jak podzielić instrukcje DML w partiach: Optymalizacja dużych SQL ServerInsert, Aktualizacja i usuwanie procesów za pomocą partii.
- w przypadku, gdy nie wiesz, jak używać TRY…CATCH exceptionhandling, spójrz na tę wskazówkę:SQL Server spróbuj złapać obsługę wyjątków.,
- Stay tuned to Thesql Server T-SQL Tipscategory to get more coding ideas.
Ostatnia aktualizacja: 2019-09-12
o autorze
Daniel farina urodził się w Buenos Aires w Argentynie. Samokształcony, od dzieciństwa wykazywał pasję do nauki.Zobacz wszystkie moje porady
- Więcej porad programistów baz danych…