Cursor können wie Verknüpfungen zu einem Entwickler aussehen. Wenn Sie einen komplexen Job ausführen müssen und die Zeilen in einer Tabelle bearbeiten müssen, scheint der schnellste Weg, die Zeilen nacheinander mit einem Transact-SQL-Cursor zu durchlaufen. Da Sie Datenstrukturen in Ihrem eigenen Code auf der Clientseite durchlaufen müssen, sind Sie möglicherweise versucht, dasselbe zu tun, wenn Sie mit SQL Server-Daten arbeiten., Das Durchlaufen von Daten mit Transact-SQL-Cursorn lässt sich jedoch häufig nicht gut skalieren, und ich hoffe, Sie davon zu überzeugen, dass dies auch kein gutes Design oder architektonische Praxis ist.

Eine Cursorerfahrung

Ich bringe dies auf, weil ich mich vor einigen Monaten mit dem Transact-SQL-Skript eines Anbieters befassen musste, das seine Datenbankkomponente auf eine neue Version der Anwendung des Anbieters aktualisiert hat. Sie haben das Skript so konzipiert, dass es eine sehr große Tabelle schwenkt und die relevanten Daten als verkettete Zeichenfolgen horizontal in einer neuen Tabelle speichert., Der Anbieter wollte die Leistung verbessern, indem er die Tabelle verkleinerte, und beschloss, die Detaildaten horizontal als durch Kommas getrennte Zeichenfolgen für jede übergeordnete ID zu speichern. Die Clientanwendung konnte die resultierenden durch Kommas getrennten Zeichenfolgen schneller abfragen, als jede von ihnen als einzelne Zeilen abzurufen, und im Kontext machte die Änderung Sinn und verbesserte die Leistung der Anwendung.

Das Transact-SQL-Skript des Anbieters zum Pivot der Daten während des Upgrades dauerte jedoch 16 Stunden, um auf einem Testcomputer ausgeführt zu werden, und der Kunde konnte sich nicht mehr als ein paar Stunden Ausfallzeit für das Upgrade leisten., Als wir das Skript des Anbieters untersuchten, stellten wir fest, dass der Entwickler den Schwenkvorgang in zwei Schritten codiert hatte: einen Cursor, um alle übergeordneten Tabellen-IDs zu durchlaufen, um eine leere vorformatierte Tabelle zu erstellen, und dann ein anderes Skript, um die Zeichenfolgen erneut mit einem Cursor zu verketten.

Mit einem set-basierten Ansatz konnten wir die Bearbeitungszeit von 16-plus Stunden auf weniger als fünf Minuten reduzieren. Wir haben die ursprüngliche Strategie des Entwicklers befolgt, die leere Tabelle mithilfe von SELECT-Anweisungen erstellt und die Zeit für diesen Schritt auf weniger als zwei Minuten verkürzt., Wir haben dann die Zeichenfolgen mit einer UPDATE-Anweisung verkettet, die pro übergeordneter ID ausgeführt wird. Unsere Iteration durch die übergeordneten IDs verwendete eine WHILE-Schleife und wurde in weniger als drei Minuten abgeschlossen.

Die Unvermeidlichkeit der Iteration

Viele Zugriffe auf Datenbankdaten müssen in gewisser Weise iterativ sein, um die Daten für weitere Manipulationen vorzubereiten. Sogar die SQL Server-Engine durchläuft Daten, wenn sie Daten mithilfe der verschiedenen Arten von Joins scannt oder verbindet, die ihr zur Verfügung stehen. Sie können dies sehen, wenn Sie den SQL Server-Abfrageplan für eine Abfrage untersuchen, die viele Zeilen aus einem großen Datensatz zurückgibt., Für einen Join sehen Sie am häufigsten eine verschachtelte Schleife, manchmal aber auch einen Merge-oder Hash-Join. Bei einfacheren Abfragen wird möglicherweise ein Clustered-oder Nicht Clustered-Index-Scan angezeigt. Nur in den Fällen, in denen SQL Server eine einzelne Zeile oder einen kleinen Satz von Zeilen zurückgeben kann und die Tabelle über einen entsprechenden Index verfügt, wird eine Suche mit einem Index angezeigt.

Denken Sie darüber nach: Microsoft hat die SQL Server Engine jahrelang optimiert und optimiert, um die verfügbaren Daten so effizient wie möglich zu durchlaufen., Stellen Sie sich vor, wenn Sie die Zeit hätten und bereit wären, die Energie aufzuwenden, könnten Sie wahrscheinlich Low-Level-Zugriffe auf Datenbankdatendateien schreiben, die ziemlich effizient wären. Es wäre jedoch nur für die einzelne Aufgabe vor Ihnen effizient, und Sie müssten es debuggen und möglicherweise vollständig umschreiben, wenn sich der Umfang Ihres Datenzugriffs ändern würde. Es würde wahrscheinlich Jahre dauern, bis der Code vollständig optimiert und verallgemeinert ist, und selbst dann wären Sie der Effizienz des Codes in der SQL Server-Speicher-Engine nicht nahe.,

Wo ist also der Gewinn, das Rad neu zu erfinden? Nur weil die SQL Server-Engine so gut optimiert und debuggt ist, ist es besser, sie die Iteration für Sie durchführen zu lassen und die umfangreiche Entwicklung und Prüfung zu nutzen, die bereits in die Datenbank eingebettet ist.

Wenn Sie sich Ihre Datenverarbeitungsaufgaben genauer ansehen, werden Sie feststellen, dass es wirklich nur sehr wenige Fälle gibt, in denen Cursor erforderlich sind. Zunächst können Sie Ihr Ziel häufig erreichen, indem Sie sich auf die set-basierten SQL-Befehle in Transact-SQL verlassen und die Reihenfolge der Zeilen einer Tabelle ignorieren., Zweitens sind Transact-SQL-Cursor nur eine Möglichkeit, eine Tabelle Zeile für Zeile zu durchlaufen. Wenn Sie jede Zeile einer Tabelle, die Sie iterieren müssen, eindeutig identifizieren können, können Sie eine WHILE-Schleife anstelle eines Cursors verwenden und möglicherweise eine bessere Leistung erzielen. Lassen Sie mich Sie durch ein Beispiel führen, um Ihnen zu zeigen, warum.

Iterationsstrategien vergleichen

Angenommen, Sie können jede Zeile einer Tabelle eindeutig identifizieren, da die Tabelle über einen eindeutigen Schlüssel oder eine eindeutige Spaltengruppe verfügt., In einer WHILE-Schleife müssen Sie nur den niedrigsten Wert der eindeutigen Bedingung finden und dann bei jeder Iteration den nächsthöchsten Wert finden. Hier ist ein Beispiel aus der SQL Server 2005 AdventureWorks-Beispieldatenbanken Produktion.TransactionHistory-Tabelle. Es hat einen gruppierten Index für den Primärschlüssel, und die WHILE-Schleife kann jedes Mal in die Zeile suchen.,

USE AdventureWorksGODECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int SET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory)WHILE @TransactionID IS NOT NULLBEGINSET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory WHERE TransactionID > @TransactionID)END

Hier ist die gleiche Schleife mit einem SCHNELLVORLAUF-Cursor, der die effizienteste Art von Transact-SQL-Cursor zum Lesen von Daten ist:

DECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int DECLARE AW_Cursor CURSOR FORWARD_ONLYFORSELECT TransactionID, TransactionType, QuantityFROM Production.TransactionHistory OPEN AW_Cursor FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @Quantity WHILE @@FETCH_STATUS = 0BEGIN FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @QuantityEND CLOSE AW_Cursor DEALLOCATE AW_Cursor

Auf meinem Laptop, nachdem ich es einige Male ausgeführt habe, um sicherzustellen, dass sich die Daten alle im Cache befinden, dauert die WHILE-Schleife neun Sekunden und der Cursor 17 Sekunden. Ihre eigene Dauer kann variieren. Beachten Sie, dass die WHILE-Schleife schneller ist, obwohl das Beispiel wirklich nichts mit den Daten zu tun hat. Der Cursor fügt offensichtlich mehr Overhead hinzu.,

Der Cursor benötigt auch zusätzliche Befehle, die den Code überladen aussehen lassen. Beachten Sie, dass Sie bei Verwendung einer WHILE-Schleife nichts deklarieren, öffnen, schließen und freigeben müssen, ohne auf die Details der Funktionsweise von Cursors einzugehen, die Microsoft in Microsoft SQL Server 2005 Books Online vollständig erklärt. Die Logik ist einfacher und Sie können sogar Zeilen auf dem Weg frei aktualisieren. Um die Zeilen mit dem Cursor zu aktualisieren, müssen Sie den Cursortyp ändern.

Sogar eine WHILE Schleife fügt den Overhead der Iteration hinzu., Möglicherweise können Sie es durch einen set-basierten SELECT-Befehl ersetzen oder alle Updates, die Sie in Ihrer Schleife ausführen möchten, durch den set-basierten UPDATE-Befehl ersetzen und die Iteration der SQL Server-Engine überlassen. Eine einfache SELECT-Anweisung zum Abrufen der gleichen Daten wie unser Cursor und die obige WHILE-Schleife dauert weniger als 3 Sekunden und gibt die Zeilen an den Client zurück, was mehr Arbeit ist als die beiden vorherigen Schleifen.

SELECT *FROM Production.TransactionHistory

Diese Auswahl basiert auf SQL Server, um die Daten zu durchlaufen, und ist bei weitem die schnellste der drei Methoden des Datenzugriffs, die wir uns angesehen haben.,

Von Taschen-Sets

Manchmal Cursor könnte notwendig sein scheinen. Wenn Sie einfach Datenbankdaten Zeile für Zeile in ihrer physischen Reihenfolge durchlaufen müssen, funktioniert manchmal nur ein Cursor. Dies geschieht am häufigsten, wenn Sie doppelte Zeilen haben und es keine Möglichkeit gibt, eine bestimmte Zeile in der Tabelle eindeutig zu identifizieren. Diese Tabellen sind Taschen, keine Mengen von Daten, da ein „Beutel“ keine doppelten Werte eliminiert, wie es ein Satz tut.

Solche Datenpakete treten normalerweise auf, wenn Sie Daten aus einer externen Quelle importieren, und Sie können den Daten nicht vollständig vertrauen., Wenn unsere AdventureWorks-Transaktionsverlaufstabelle beispielsweise keine Gruppe von Spalten enthält, die Sie als eindeutig bezeichnen könnten, und/oder doppelte Zeilen enthält, müssen Sie möglicherweise einen Cursor verwenden.

Sie können jedoch immer eine Tüte Zeilen in eine normalisierte Tabelle verwandeln. Selbst wenn Sie doppelte Zeilen in einer Tabelle haben oder keine Gruppe von Spalten, auf die Sie sich für die Eindeutigkeit verlassen können, können Sie der Tabelle eine Identitätsspalte hinzufügen und die Identität so setzen, dass die Nummerierung mit 1 beginnt. Dadurch wird der Tabelle ein eindeutiger Schlüssel hinzugefügt, sodass Sie anstelle eines Cursors eine WHILE-Schleife verwenden können., Sobald Sie einen eindeutigen Schlüssel haben, können Sie Duplikate mit dem Befehl Transact-SQL set-based UPDATE entfernen.

Die logische API für Datenbankdaten

Mit Set-Base-Operationen ist besser als die Daten selbst auf mindestens zwei Arten zu iterieren.

Erstens sind Set-basierte SQL-Befehle effizienter, da Sie die hochoptimierte SQL Server-Engine für Ihre Iteration verwenden. Wenn Sie Daten selbst durchlaufen, verwenden Sie die SQL Server Storage Engine nicht optimal. Stattdessen pfeffern Sie es mit Befehlen, um jeweils nur eine einzelne Zeile abzurufen., Jedes Mal, wenn Sie eine einzelne Zeile anfordern, muss Ihr Befehl den SQL Server-Optimierer durchlaufen, bevor er zur Speicher-Engine gelangen kann, und Sie verwenden am Ende nicht den optimierten Code der SQL Server-Speicher-Engine. Wenn Sie sich selbst iteriert haben, verlassen Sie sich bei der Verarbeitung der Daten auch auf fremde physische Informationen über die Tabelle, nämlich die Reihenfolge der Zeilen. Mit den Befehlen set-base Transact-SQL SELECT, UPDATE und DELETE können Sie die Reihenfolge der Zeilen ignorieren und sie nur basierend auf den Eigenschaften der Daten beeinflussen-und sie sind schneller.,

Zweitens sind Set-basierte Befehle logischer, da das Nachdenken über Daten in Sets Sie von fremden Details abstrahiert, die sich mehr mit der tatsächlichen Reihenfolge der Daten befassen. Tatsächlich bringen Set-basierte Befehle wie SELECT, UPDATE und DELETE Sie logisch näher an Ihre Daten heran, wenn Sie direkt und nicht in einer Cursor-oder WHILE-Schleife auf Tabellen angewendet werden, gerade weil Sie die Reihenfolge der Daten ignorieren können.,

Hier ist eine andere Möglichkeit, über diesen zweiten Punkt nachzudenken-so wie gespeicherte Prozeduren die natürlichste API für Anwendungen sind, die programmgesteuert mit SQL Server verbunden sind, sind Set-basierte SQL-Befehle die geeignete API für den Zugriff auf relationale Daten. Gespeicherte Prozeduren entkoppeln Ihre Anwendung von Datenbankinternalen und sind effizienter als Ad-hoc-Abfragen. In ähnlicher Weise bieten Ihnen die Set-Base-SQL-Befehle in Transact-SQL eine logische Schnittstelle zu Ihren relationalen Daten, und sie sind effizienter, da Sie sich beim Durchlaufen von Daten auf die SQL Server-Speicher-Engine verlassen.,

Das Endergebnis ist nicht, dass das Durchlaufen von Daten schlecht ist. Eigentlich ist es oft unvermeidlich. Der Punkt ist vielmehr, lassen Sie die Storage Engine dies für Sie tun und verlassen Sie sich stattdessen auf die logische Schnittstelle der set-basierten Transact-SQL-Befehle. Ich denke, Sie werden nur wenige Situationen finden, in denen Sie tatsächlich einen Transact-SQL-Cursor verwenden müssen.