Intro
In relationalen Datenbanken werden Operationen für eine Reihe von Zeilen ausgeführt. Beispielsweise gibt eine SELECT-Anweisung eine Reihe von Zeilen zurück, die als Ergebnismenge bezeichnet wird. Manchmal muss die Anwendungslogik mit jeweils einer Zeile und nicht mit der gesamten Ergebnismenge auf einmal arbeiten. In T-SQL besteht eine Möglichkeit darin, einen CURSOR zu verwenden.
Wenn Sie über Programmierkenntnisse verfügen, würden Sie wahrscheinlich eine Schleife wie FOR oder WHILE verwenden, um jeweils ein Element zu durchlaufen und etwas damit zu tun Die Daten und die Arbeit sind erledigt. In T-SQL ist ein CURSOR ein ähnlicher Ansatz und wird möglicherweise bevorzugt, da er derselben Logik folgt. Aber seien Sie gewarnt, nehmen Sie diesen Weg und es kann zu Problemen kommen.
Es gibt einige Fälle, in denen die Verwendung von CURSOR nicht so schlimm ist, aber im Allgemeinen sollten sie vermieden werden. Im Folgenden werden einige Beispiele gezeigt, bei denen die Verwendung eines CURSOR zu Leistungsproblemen führt, und wir werden feststellen, dass dieselbe Aufgabe auf viele andere Arten ausgeführt werden kann.
Für diese Demonstration verwenden wir die AdventureWorks2012-Datenbank und Nehmen wir an, wir möchten einige Daten von erhalten. Tabelle für jedes Produkt, dessen Herstellung weniger als einen Tag dauert, dh aus Tabelle.
Ein Cursor-Beispiel
Beginnen wir mit der Verwendung eines CURSOR und schreiben die folgende Syntax:
|
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
DECLARE @id int
DECLARE cursorT CURSOR
–LOCAL STATIC
–LOCAL FAST_FORWARD
–LOCAL READ_ONLY FORWARD_ONLY
FOR
SELECT ProductId
FROM AdventureWorks2012.Production.Product
WHERE DaysToManufacture < = 1
OPEN cursorT
FETCH NEXT FROM cursorT IN @id
WHILE @@ FETCH_STATUS = 0
BEGIN
SELECT * FROM Production.ProductInventory
WHERE ProductID = @ id
WEITER VON CursorT IN @id
ENDE
CursorT SCHLIESSEN
DEALLOCATE cursorT
|
Nach einer kurzen Kaffeepause Die Ausführung der Abfrage wurde beendet und es wurden 833 Zeilen in der unten angegebenen Zeit zurückgegeben. Es ist wichtig zu erwähnen, dass die oben ausgewählten Syntaxen nur für Demozwecke dienen, und ich habe keine Indexoptimierung vorgenommen, um die Dinge zu beschleunigen.
Bei der Cursorausführung haben wir zwei Schritte. Schritt eins, die Positionierung, wenn der Cursor seine Position auf eine Zeile aus der Ergebnismenge setzt. Schritt zwei, das Abrufen, wenn die Daten aus dieser bestimmten Zeile in einer Operation namens FETCH abgerufen werden.
In unserem Beispiel setzt der Cursor seine Position auf die erste Zeile, die vom ersten SELECT zurückgegeben wird, und ruft den ProductID-Wert ab, der der WHERE-Bedingung in der Variablen @id entspricht. Dann verwendet das zweite SELECT den Variablenwert, um Daten von abzurufen. und die nächste Zeile wird abgerufen. Diese Vorgänge werden wiederholt, bis keine Zeilen mehr zum Arbeiten vorhanden sind.
Schließlich gibt die CLOSE-Syntax die aktuelle Ergebnismenge frei und entfernt die Sperren aus den vom Cursor verwendeten Zeilen. DEALLOCATE entfernt die Cursorreferenz.
Unsere Demo-Tabellen sind relativ klein und enthalten ungefähr 1.000 und 500 Zeilen. Wenn wir Tabellen mit Millionen von Zeilen durchlaufen müssten, würde dies eine beträchtliche Zeit dauern und die Ergebnisse würden uns nicht gefallen.
Geben wir unserem Cursor eine weitere Chance und kommentieren Sie die Zeile – LOCAL STATIC aus. Es gibt viele Argumente, die wir in einer Cursordefinition verwenden können, mehr dazu unter diesem Link. CURSOR-Argumente, aber jetzt konzentrieren wir uns darauf, was diese beiden Wörter bedeuten.
Wenn wir das Schlüsselwort LOCAL angeben, ist der Bereich des Cursors lokal für den Stapel, in dem er erstellt wurde, und nur in diesem Bereich gültig. Nachdem der Stapel ausgeführt wurde, wird der Cursor automatisch freigegeben. Der Cursor kann auch von einer gespeicherten Prozedur, einem Trigger oder einer lokalen Cursor-Variablen in einem Stapel referenziert werden.
Das Schlüsselwort STATIC erstellt eine temporäre Kopie der vom Cursor in tempdb verwendeten Daten in einer temporären Tabelle . Hier haben wir einige Fallstricke. Ein STATIC-Cursor ist schreibgeschützt und wird auch als Snapshot-Cursor bezeichnet, da er nur mit den Daten ab dem Zeitpunkt des Öffnens funktioniert. Dies bedeutet, dass keine in der Datenbank vorgenommenen Änderungen an dem von der Mauszeiger.Grundsätzlich sind keine Aktualisierungen, Löschungen oder Einfügungen nach dem Öffnen des Cursors in der Cursor-Ergebnismenge sichtbar, es sei denn, wir schließen den Cursor und öffnen ihn erneut.
Beachten Sie dies, bevor Sie diese Argumente verwenden, und prüfen Sie, ob sie Ihren Anforderungen entsprechen. Aber mal sehen, ob unser Cursor schneller ist. Unten haben wir die Ergebnisse:
12 Sekunden sind viel besser als 4 Minuten und 47 Sekunden, aber denken Sie daran Einschränkungen, die oben erläutert wurden.
Wenn wir die Syntax mit diesem Argument LOCAL READ_ONLY FORWARD_ONLY ausführen, erhalten wir dieselben Ergebnisse. Der Cursor READ_ONLY FORWARD_ONLY kann nur von der ersten bis zur letzten Zeile gescrollt werden. Wenn das Schlüsselwort STATIC fehlt, ist der Cursor dynamisch und verwendet einen dynamischen Plan, falls verfügbar. Es gibt jedoch Fälle, in denen ein dynamischer Plan schlechter ist als ein statischer.
Was passiert, wenn wir – LOKAL FAST_FORWARD auskommentieren?
FAST_FORWARD entspricht dem Cursor READ_ONLY und FORWARD_ONLY, kann jedoch den besseren Plan entweder aus einem statischen oder einem dynamischen Plan auswählen.
LOCAL FAST_FORWARD scheint aufgrund seiner Flexibilität die beste Wahl zu sein Wählen Sie zwischen einem statischen oder einem dynamischen Plan, aber FORWARD_ONLY macht den Job auch sehr gut. Obwohl wir mit beiden Methoden das Ergebnis in durchschnittlich 12 Sekunden erhalten haben, sollten diese Argumente ordnungsgemäß getestet werden, bevor Sie eine davon auswählen.
Es gibt viele Möglichkeiten, dasselbe Ergebnis zu erzielen, viel schneller und mit weniger Einfluss auf die Leistung.
Cursor-Alternative
Eine Methode verwendet einen JOIN, und wie wir als nächstes sehen können, sind die Ergebnisse erheblich besser.
Erstens: Wir müssen die Statistikzeit aktivieren, um die Ausführungszeit von SQL Server zu messen und einen INNER JOIN zwischen zwei Tabellen zu erstellen. und . in der Spalte ProductID. Nachdem wir die EXECUTE-Taste gedrückt haben, haben wir in 330 ms die gleichen Ergebnisse erzielt, verglichen mit 04:47 Uhr mit der Cursormethode, und wir haben ein Lächeln im Gesicht.
|
1
2
3
4
5
6
7
8
9
|
USE AdventureWorks2012
GO
STATISTIKZEIT EINSTELLEN
SELECT * FROM Production.ProductInventory als pinv
INNER JOIN Production.Product as pp
ON pinv.ProductID = pp.ProductID
WHERE pp.DaysToManufacture < = 1
|
Wir müssen nicht jede Zeile durchlaufen, um das zu erhalten, was wir brauchen. Wir haben keine Schleifen mehr, keine while-Klausel, keine Iteration Wir arbeiten stattdessen mit Datensätzen, erhalten schneller das, was wir wollen, und schreiben weniger Code.
Eine angemessene Verwendung des Cursors
Jetzt, da wir gesehen haben, wie viel Schaden a Der Cursor kann dies tun. Sehen wir uns ein Beispiel an, in dem wir es verwenden können.
Nehmen wir an, wir möchten die Größe und Anzahl der Zeilen nur für bestimmte Tabellen aus einer Datenbank auswählen. Um dies zu erreichen, erhalten wir alle Tabellennamen basierend auf Kriterien aus information_schema.tables. Mit einem CURSOR durchlaufen wir jeden dieser Tabellennamen und führen die gespeicherte Prozedur sp_spaceuse aus, indem wir jeweils einen Tabellennamen übergeben, um die benötigten Informationen zu erhalten
Wir verwenden dieselbe AdventureWorks2012-Datenbank und rufen alle Tabellen aus dem Verkaufsschema ab, das den Namen ‚Sales‘ enthält. Für jeden zurückgegebenen Tabellennamen möchten wir alle Informationen aus information_schema.tables anzeigen.
Nachfolgend finden Sie die T-SQL-Syntax und die erhaltenen Ergebnisse:
|
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) – Tabellenname aus dem Schema „Sales“
DECLARE @Param VARCHAR (50) – Parameter für die Prozedur „sp_spaceused“
DECLARE db_cursor CURSOR FOR
– Wählen Sie nur „Sales“ -Tabl es
SELECT TABLE_NAME FROM information_schema.tables
WHERE TABLE_NAME wie „% Sales%“ und TABLE_TYPE = „BASE TABLE“
OPEN db_cursor
FETCH NEXT FROM db_cursor IN @TableName
WHILE @@ FETCH_STATUS = 0
BEGIN
– verketten Sie jeden Tabellennamen in einer Variablen und übergeben Sie ihn an die gespeicherte Prozedur.
SET @ Param = „Sales.“+ @ TableName
– gespeicherte Prozedur für jeden Tabellennamen gleichzeitig ausführen
EXEC sp_spaceused @Param
FETCH NEXT FROM db_cursor INTO @TableName – Ruft den nächsten Tabellennamen ab.
END
CLOSE db_cursor
DEALLOCATE db_cursor
|
Dies ist eine Methode, bei der CURSOR hilfreich ist, indem einige Daten zeilenweise durchlaufen werden und das erforderliche Ergebnis erzielt wird. In diesem speziellen Fall erledigt der Cursor die Aufgabe ohne Auswirkungen auf die Leistung und ist einfach zu verwenden.
Schlussfolgerungen
Da haben wir es. Wir haben einige Beispiele mit den guten, den schlechten und den hässlichen bei der Verwendung von Cursorn gezeigt. In den meisten Fällen können wir JOINS verwenden, sogar WHILE-Klauseln, SSIS Pakete oder andere alternative Methoden, um das gleiche Ergebnis schneller zu erzielen, die Leistung weniger zu beeinträchtigen und sogar weniger Syntaxzeilen zu schreiben.
Wenn wir Wenn es sich um eine OLTP-Umgebung und große zu verarbeitende Datenmengen handelt, sollte das Wort „CURSOR“ nicht gesprochen werden.
In SQL ist es empfehlenswert, Operationen an Datensätzen durchzuführen, anstatt nachzudenken auf programmatische Weise unter Verwendung von Iterationen oder Schleifen, da diese Art von Ansatz für diese Verwendung weder empfohlen noch vorgesehen ist. Der Versuch, Schleifen wie FOR oder FOREACH aus Programmiersprachen zu verwenden und diese Logik mit SQL-Operationen zu verknüpfen, ist ein Hindernis, um die richtige Lösung für unsere Anforderungen zu finden. Wir müssen eher an satzbasierte Operationen als an eine Zeile denken, um die benötigten Daten zu erhalten.
Cursor können in einigen Anwendungen für serialisierte Operationen verwendet werden, wie im obigen Beispiel gezeigt, sollten dies jedoch im Allgemeinen tun vermieden werden, da sie sich negativ auf die Leistung auswirken, insbesondere wenn große Datenmengen verarbeitet werden.
- Autor
- Letzte Beiträge
Ich habe einen Bachelor-Abschluss in Computertechnik und besitze auch Kenntnisse in .NET, C # und Windows Server. Ich habe Erfahrung in den Bereichen Leistungsoptimierung und -überwachung, T-SQL-Entwicklung, Sicherungsstrategie, Verwaltung und Konfiguration von Datenbanken.
Ich bin begeistert von IT-Inhalten, Videospielen, guten Filmen und Electro-House-Musik, die mir hilft, im Büro zu arbeiten laut.
Alle Beiträge von Sergiu Onet anzeigen
