SQL Server Schleife durch Tabellenzeilen ohne Cursor

Von: Daniel Farina / Aktualisiert: 2019-09-12 / Kommentare (2) | Verwandte: Mehr > T-SQL

Problem

Youhave immer gehört, dass sie vermeiden sollten cursor inyour T-SQL code als SQL Server best practice, becausecursors sind schädlich für performanceund manchmal probleme verursachen. Aber manchmal gibt es eine Notwendigkeit, durch die zu schleifendaten eine Zeile zu einer Zeit, so in diesem Tipp werden wir einen Vergleich betrachten, wie man eine Schleife ohne Cursor macht.,

Lösung

Wir alle wissen, dass SQL Server, wie jede relationale Datenbank ermöglicht es dem Benutzer toperform Satz basierte Operationen. Wie viele Datenbankanbieter enthält auch SQL Servereine prozedurale Erweiterung, die die T-SQL-Sprache ist. Es fügt Konstrukte hinzu, die inprozedurale Sprachen ermöglichen eine einfachere Codierung für Entwickler. Diese Konstruktionen wurden aus einem bestimmten Grund hinzugefügt, und manchmal ist dies die einzige Herangehensweise an die vorliegende Aufgabe.,

Verwenden einer While-Schleife anstelle von Cursorn in SQL Server

Wenn Sie jemals mit Cursorn gearbeitet haben, können Sie diesen Titel etwas verwirrend findenweil Cursor while Konstrukte verwenden, um zwischen Zeilen zu iterieren. Abgesehen davon möchte ich Ihnen zeigen, dass wir unter bestimmten Umständen, wenn wir einen Cursor verwenden, um eine Reihe von Zeilen zu iterieren, ihn in eine while-Schleife ändern können. In solchen Fällen besteht die einzige Herausforderungwird sein, eine richtige Ausgangsbedingung zu wählen.,

Vor-und Nachteile der Verwendung von Cursors zum Durchlaufen von Tabellenzeilen in SQL Server

Nicht alles stimmt mit Cursors nicht, sie haben auch einige Vorteileüber anderen Schleifentechniken.

• Cursor sind aktualisierbar: Wenn Sie einen Cursor erstellen, verwenden Sie eine Abfrage, um ihn mithilfe der Anweisung DECLARE CURSOR zu definieren. Mit der UPDATE-Option in der cursorcreation-Anweisung können Sie die Spalten innerhalb des Cursors aktualisieren.,
• Sie können sich mit einem Cursor vorwärts und rückwärts bewegen: Mit der Option SCROLLIN der Anweisung DECLARE CURSOR können Sie mit den Abrufoptionen FIRST, LAST, PRIOR, NEXT, RELATIVE undABSOLUTE über die Cursordatensätze in beide Richtungen navigieren. Beachten Sie, dass die SCROLL-Option nicht mit den forward_only-und FAST_FORWARD-Optionen kompatibel ist.
• Cursor können an gespeicherte Prozeduren übergeben werden: Wenn Sie die GLOBALE Option zum Erstellen eines Cursors verwenden, kann er in jeder gespeicherten Prozedur oder in einem Stapel verwendet werden, der in derselben Verbindung ausgeführt wird. Auf diese Weise können Sie Cursor für verschachtelte gespeicherte Prozeduren verwenden.,
• Cursor haben viele verschiedene Optionen: Mit Cursorn haben Sie die Möglichkeit, verschiedene Optionen zu verwenden, die sich darauf auswirken, wie sie sich beim Sperren verhalten.
• Cursor benötigen keine Bedingung: Durch die Verwendung von Cursorn Sie eine Reihe von Zeilen als Datensatz. Auf diese Weise können Sie sich über den Cursor bewegen ohnedie Notwendigkeit einer Booleschen Bedingung. Sie können beispielsweise einen Cursor mit dem Namen der Datenbanken erstellen, die sich auf einer SQL Server-Instanz befinden, ohne dass ein Ersatzschlüssel wie in einer WHILE-Schleife als Testbedingung verwendet wird.,

Es gibt auch einige negative Aspekte, die Sie beachten sollten, wenn Sie cursors anstelle anderer Schleifenoptionen verwenden.

• Wenn Sie globale Cursor in Ihrem Code verwenden, riskieren Sie facingerrors, da ein Cursor durch eine in Ihrem Code verschachtelte gespeicherte Prozedur geschlossen wird.
• Normalerweise haben Cursor eine geringere Leistung als eine äquivalente Schleife mit einem WHILEloop oder CTE.

Vor-und Nachteile der Verwendung einer While-Schleife zum Durchlaufen von Tabellenzeilen in SQL Server

Es gibt auch Vorteile, eine WHILE-Schleife im Vergleich zu einem Cursor zu verwenden.,

• Während Schleifen schneller sind als Cursor.
• Während Schleifen weniger Sperren als Cursor verwenden.
• Weniger-Nutzung von Tempdb: While-Schleifen nicht, erstellen Sie eine Kopie der Daten, die intempdb als cursor wird. Denken Sie daran, dass Cursor abhängig von den Optionen, mit denen Sie sie erstellen, dazu führen können, dass die temporären Tabellen erstellt werden.

In der nächsten Liste werden die negativen Aspekte von WHILE-Schleifen aufgeführt.

• Vorwärts oder rückwärts zu bewegen ist komplex: Um sich auf einen Schlag vorwärts oder rückwärts zu bewegen, müssen Sie die Iterationsbedingung innerhalb der Schleife dynamisch ändern.,Dies erfordert zusätzliche Sorgfalt; sonst können Sie in einer Endlosschleife enden.
• Das Risiko einer Endlosschleife: Im Vergleich zu einem Cursor haben Sie keinen festen Datensatz in einer Schleife (dh die von der SELECT Statementin der Cursordeklaration zurückgegebenen Daten), stattdessen müssen Sie bei Verwendung einer WHILE-Schleife eine Grenze definierenmit einem Ausdruck, der als true oder false ausgewertet wird.

Erstellen der Testumgebung für Cursor und Schleifen

Um dies zu testen, verwende ich eine Tabelle mit einer Identitätsspalte (CursorTestID),einer Varchar-Spalte (Filler) und einer Bigint-Spalte (runningTotal).,

CREATE TABLE CursorTest( CursorTestID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Filler VARCHAR(4000), RunningTotal BIGINT )GO 

Die Idee ist, die durch die CursorTestID-Spalte geordneten Tabellenzeilen zu durchlaufen und die runningTotal-Spalte mit der Summe des CursorTestID-Spaltenwerts zu aktualisierenund der Wert der runningTotal-Spalte der vorherigen Zeile.

Aber bevor wir beginnen, müssen wir zuerst einige Testzeilen mit dem nächsten Skript generieren.,

INSERT INTO dbo.CursorTest ( Filler, RunningTotal )VALUES ( REPLICATE('a', 4000), 0 )GO 500000

Im obigen Skript werden Sie feststellen, dass ich nur eine einzelne Einfügestatementund ich habe das Batch-Trennzeichen (den Befehl GO 500000) als Shortcut verwendet, um diese Einfügeanweisung 500000 mal auszuführen. Sie können mehr über diese Methode lesen, um die Stapelausführung mit diesem Tipp zu wiederholen: Führen Sie einen T-SQL-Stapel mehrmals mit GO aus.

Beispiel eines einfachen Cursors zum Durchlaufen von Tabellenzeilen in SQL Server

Erstellen wir einen Cursor, um die runningTotal-Spalte zu füllen. Beachten Sie auf der nächsten Seite, dass ich den Cursor mit der Option FAST_FORWARD deklariert habe., Dies geschieht, um die Leistung des Cursors zu verbessern, da laut Microsoft theFAST_FORWARD Argument „Gibt einen FORWARD_ONLY, READ_ONLY Cursor mit performanceoptimizations aktiviert“. Mit anderen Worten, wir weisen SQL Server an, zu verwendenein schreibgeschützter Cursor, der sich nur vorwärts bewegen und von der ersten bis zur letzten Zeile gescrollt werden kann.

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

Das nächste Bild ist eine Bildschirmaufnahme, die die Ausführung des Skripts zeigt above.As sie können sehen, es dauerte drei Minuten und fünf Sekunden, um die 500.000 Zeilen unserer Testtabelle zu aktualisieren.,

Beispiel für eine grundlegende While-Schleife zum Durchlaufen von Tabellenzeilen in SQL Server

Jetzt werde ich das vorherige Skript neu schreiben, um die Verwendung eines Cursors zu vermeiden. Sie willnotice, dass es enthält eine While-Schleife, die ist fast identisch zu dem in thecursor Skript. Dies ist, wie ich bereits sagte, denn auch bei der Arbeit mit Cursorensie müssen eine iterative Steuerstruktur verwenden.

Das nächste Bild ist eine Bildschirmaufnahme der Ausführung des obigen Skripts. Es brauchte keine Zeit, um die while-Schleife als den Cursor auszuführen.,

Ein weiteres SQL Server Cursorbeispiel

Nehmen wir zum Beispiel den Cursor im tipStandardize SQL Server Daten mit Text Lookup und replace Funktion. Ein Wort vonrat, um diesen Code auszuführen, sollten Sie die Schritte im Tipp befolgen, um zu erstellendie Testumgebung.

Und hier ist der Cursorcode:

Wenn wir diesen Code sezieren, können wir sehen, dass es einen Cursor gibt, der durchgehtdie Tabelle Produkte, die ich unten kopiert habe.,

DECLARE load_cursor CURSOR FOR SELECT ProductID, ProductName FROM dbo.Products 

SQL Server Cursorbeispiel Konvertiert in eine While-Schleife

Um diesen Cursor durch eine WHILE-SCHLEIFE zu ersetzen, müssen wir eine temporarytable erstellen, um eine Tally-Tabelle zu implementieren. Für alle, die nicht wissen, was eine Tallytable ist, können wir sie als Tabelle definieren, die ein Spaltenpaar enthält, das aus einem Schlüssel und seinem Wert besteht. In unserem speziellen Fall verwenden wir einen sequentiellen Ganzzahlschlüssel ab 1, damit wir ihn als Iterator verwenden können. Dieser Schlüssel wird einer ProductID aus der Tabelle Products zugeordnet.,

Da in der Produkttabelle zunächst der ProductID-Schlüssel als identityyou definiert ist, können Sie versucht sein, diesen Schritt zu umgehen, aber Sie müssen berücksichtigen, dass in einem realen Falleine Zeile gelöscht worden sein könnte, daher können Sie die identitycolumn nicht als Iterator verwenden. Zusätzlich kann eine Zeile gelöscht werden, während wir unser Programm ausführen, und dies kann zu Ausführungsfehlern führen. Um dies zu vermeiden, werden wir aTRY-CATCHblock hinzufügen. Ich werde weiter darauf eingehen.

Bevor wir die WHILE-Schleife starten, müssen wir die Start-und Stoppbedingung festlegen., In dieser Angelegenheit habe ich zwei neue ganzzahlige Variablen namens @Iterator und @MaxIterator hinzugefügt.Die Variable @MaxIterator wird verwendet, um die Anzahl der Elemente in #TallyTabletable beizubehalten, und wir setzen ihren Wert nur einmal, bevor wir die Schleife starten. Die @ Iterator-Variableist auf 1 initialisiert, da wir sie als Startnummer in der Sequenz definiert haben undWir werden ihren Wert bei jeder Iteration erhöhen.

Nächste Schritte

• Sind Sie neu in Cursor und brauchen etwas Übung? Im Nexttip finden Sie eine Erklärung, ein leicht verständliches Cursorbeispiel und weitere empfohlene Messwerte:SQL Server Cursorbeispiel.,
• Wenn Sie die vorhandenen Cursor in Ihrem Code in Set-basierte Abfragen konvertieren möchten, schauen Sie sich dieses Kapitel ansql Server Convert Cursorto Set Basierend auf theSQL Server Database Design Best Practices Tutorial.
• Benötigen Sie ein weiteres Beispiel für die Verwendung einer While-Schleife? Schauen Sie sich diesen Tipp andas zeigt Ihnen, wie Sie DML-Anweisungen in Stapel aufteilen:Optimieren Sie große SQL ServerInsert, aktualisieren und löschen Sie Prozesse mithilfe von Stapeln.
• Falls Sie nicht wissen, wie man TRY benutzt…FANGEN exceptionhandling, werfen Sie einen Blick auf diesen Tipp:SQL Server Try-und Catch-Exception Handling.,
• Bleiben Sie dran an theSQL Server T-SQL Tipscategory, um weitere Codierungsideen zu erhalten.

Zuletzt aktualisiert: 2019-09-12

Über den Autor

Daniel Farina wurde in Buenos Aires, Argentinien geboren. Selbst ausgebildet, zeigte er seit seiner Kindheit eine Leidenschaft für das Lernen.
Alle meine Tipps

Verwandte Ressourcen

• Weitere Tipps für Datenbankentwickler…