Ich habe eine UPDATE -Anweisung, die mehr als Millionen Datensätze aktualisieren kann. Ich möchte sie in Chargen von 1000 oder 10000 aktualisieren. Ich habe versucht mit @@ROWCOUNT , aber ich bin nicht in der Lage, das gewünschte Ergebnis zu erhalten.
Nur zu Testzwecken habe ich eine Tabelle mit 14 Datensätzen ausgewählt und eine Zeilenanzahl von 5 gesetzt. Diese Abfrage soll die Datensätze in 5, 5 und 4 aktualisieren, aktualisiert aber nur die ersten 5 Datensätze.
>Abfrage - 1:
%Vor%Abfrage - 2:
%Vor%Was fehlt mir hier?
Sie sollten keine 10k Zeilen in einem Satz aktualisieren, es sei denn, Sie sind sicher, dass der Vorgang Seitenschlösser erhält (weil mehrere Zeilen pro Seite Teil der Operation UPDATE sind). Das Problem besteht darin, dass Lock Escalation (aus Zeilen- oder Page-to-Table-Sperren) bei 5000 Sperren auftritt. Es ist also am sichersten, wenn Sie den Wert unter 5000 halten, nur für den Fall, dass die Operation Zeilensperren verwendet.
Sie sollten nicht SET ROWCOUNT verwenden, um die Anzahl zu begrenzen Anzahl der Zeilen, die geändert werden. Hier gibt es zwei Probleme:
Es ist veraltet, seit SQL Server 2005 veröffentlicht wurde (vor 11 Jahren):
Die Verwendung von SET ROWCOUNT wirkt sich nicht auf DELETE-, INSERT- und UPDATE-Anweisungen in einer zukünftigen Version von SQL Server aus. Vermeiden Sie die Verwendung von SET ROWCOUNT mit DELETE-, INSERT- und UPDATE-Anweisungen in neuen Entwicklungsarbeiten, und planen Sie, Anwendungen zu ändern, die es derzeit verwenden. Verwenden Sie für ein ähnliches Verhalten die TOP-Syntax
Es kann mehr als nur die Aussage beeinflussen, mit der Sie es zu tun haben:
Wenn Sie die Option SET ROWCOUNT festlegen, werden die meisten Transact-SQL-Anweisungen nicht mehr verarbeitet, wenn sie von der angegebenen Anzahl von Zeilen betroffen sind. Dies beinhaltet Trigger. Die Option ROWCOUNT wirkt sich nicht auf dynamische Cursor aus, begrenzt jedoch das Rowset von Keyset- und unempfindlichen Cursors. Diese Option sollte mit Vorsicht verwendet werden.
Verwenden Sie stattdessen die TOP () -Klausel.
Es hat keinen Sinn, hier eine explizite Transaktion durchzuführen. Dies verkompliziert den Code und Sie haben keine Behandlung für ein ROLLBACK, das nicht einmal benötigt wird, da jede Anweisung eine eigene Transaktion ist (d. H. Automatisches Commit).
Angenommen, Sie finden einen Grund, die explizite Transaktion beizubehalten, dann haben Sie keine TRY / CATCH-Struktur. Bitte beachten Sie meine Antwort auf DBA.StackExchange für eine TRY / CATCH Vorlage, die Transaktionen behandelt:
Ist es erforderlich, Transaktionen sowohl im C # -Code als auch im Store-Verfahren zu behandeln
?Ich vermute, dass die echte WHERE-Klausel nicht in dem Beispielcode in der Frage gezeigt wird. Wenn man sich einfach auf das stützt, was gezeigt wurde, wäre ein besseres Modell:
%Vor% Indem Sie @Rows gegen @BatchSize testen, können Sie diese letzte UPDATE-Abfrage vermeiden (in den meisten Fällen), weil die letzte Menge normalerweise eine bestimmte Anzahl von Zeilen kleiner als @BatchSize ist. In diesem Fall wissen wir, dass es keine gibt mehr zu verarbeiten (was Sie in der Ausgabe Ihrer Antwort sehen). Nur in den Fällen, in denen die letzte Gruppe von Zeilen gleich @BatchSize ist, wird dieser Code ein letztes UPDATE ausführen, das sich auf 0 Zeilen auswirkt.
Ich fügte der Klausel WHERE auch eine Bedingung hinzu, um zu verhindern, dass Zeilen, die bereits aktualisiert wurden, erneut aktualisiert werden.
Ich möchte meine Erfahrung teilen. Vor ein paar Tagen muss ich 21 Millionen Datensätze in Tabelle mit 76 Millionen Datensätzen aktualisieren. Mein Kollege schlug die nächste Variante vor. Zum Beispiel haben wir die nächste Tabelle 'Personen':
%Vor%Aufgabe : Aktualisieren Sie Personen auf den neuen Jobtitel: 'Software Developer' - & gt; 'Web Developer'.
1. Erstellen Sie temporäre Tabelle 'Persons_SoftwareDeveloper_To_WebDeveloper (Id INT Primärschlüssel)'
2. Wählen Sie in temporäre Tabelle Personen, die Sie mit dem neuen Job-Titel aktualisieren möchten:
%Vor%Abhängig von der Anzahl der Zeilen benötigt diese Anweisung einige Zeit, um Ihre temporäre Tabelle zu füllen, aber es würde Sperren vermeiden. In meiner Situation dauerte es etwa 5 Minuten (21 Millionen Zeilen).
3. Die wichtigste Idee besteht darin, micro sql statements zu generieren, um die Datenbank zu aktualisieren. Also, lasst uns sie ausdrucken:
%Vor%Nach Ausführung dieses Skripts erhalten Sie Hunderte von Batches, die Sie in einem Tab von MS SQL Management Studio ausführen können.
4. Führen Sie die gedruckten SQL-Anweisungen aus und prüfen Sie, ob die Tabelle gesperrt ist. Sie können den Prozess jederzeit anhalten und mit @pageSize spielen, um die Aktualisierung zu beschleunigen oder zu beschleunigen (vergessen Sie nicht, @i nach dem Pausieren des Skripts zu ändern).
5. Drop Persons_SoftwareDeveloper_To_AspNetDeveloper. Entfernen Sie die temporäre Tabelle.
Minor Anmerkung: Diese Migration kann einige Zeit dauern und neue Zeilen mit ungültigen Daten können während der Migration eingefügt werden. Fixiere also zuerst die Orte, an denen deine Zeilen hinzugefügt werden. In meiner Situation habe ich UI, 'Software Developer' - & gt; 'Web Developer'.
Dein print macht alles kaputt, weil es @@ROWCOUNT zurücksetzt. Immer wenn du @@ROWCOUNT verwendest, ist mein Ratschlag, immer sofort auf eine Variable zu setzen. Also:
Und noch ein nettes Feature ist, dass Sie den Code update nicht wiederholen müssen.
Zunächst einmal vielen Dank für Ihre Eingaben. Ich ändere meine Query - 1 und bekomme mein gewünschtes Ergebnis. Gordon Linoff hat recht, PRINT hat meine Anfrage durcheinander gebracht, also habe ich sie wie folgt geändert:
Geänderte Abfrage - 1:
%Vor%Ausgabe:
%Vor%Tags und Links sql-server