SQL Server Optimizing Update Queries for Large Data Volumes
Das Aktualisieren sehr großer Tabellen kann zeitaufwändig sein und manchmal Stunden dauern. Darüber hinaus kann es auch zu Blockierungsproblemen kommen.
Hier sind einige Tipps zu SQL Server Optimierung der Updates auf große Datenmengen.
- Entfernen des Index für die zu aktualisierende Spalte.
- Ausführen des Updates in kleineren Batches.
- Deaktivieren von Lösch-Triggern.
- Ersetzen der Update-Anweisung durch eine Bulk-Insert-Operation.
Nachdem dies gesagt wurde, wenden wir die obigen Punkte an, um eine Aktualisierungsabfrage zu optimieren.
Der folgende Code erstellt eine Dummy-Tabelle mit 200.000 Zeilen und erforderlichen Indizes.
CREATE TABLE tblverylargetable ( sno INT IDENTITY, col1 CHAR(800), col2 CHAR(800), col3 CHAR(800) ) GO DECLARE @i INT=0 WHILE( @i < 200000 ) BEGIN INSERT INTO tblverylargetable VALUES ('Dummy', Replicate('Dummy', 160), Replicate('Dummy', 160)) SET @i=@i + 1 ENDGOCREATE INDEX ix_col1 ON tblverylargetable(col1) GO CREATE INDEX ix_col2_col3 ON tblverylargetable(col2) INCLUDE(col3)
Betrachten Sie die folgende Update-Abfrage, die optimiert werden soll. Es ist eine sehr einfache Abfrage, um eine einzelne Spalte zu aktualisieren.
UPDATE tblverylargetable SET col1 = 'D'WHERE col1 = 'Dummy'
Die Ausführung der Abfrage dauert 2:19 Minuten.
Schauen wir uns den Ausführungsplan der unten gezeigten Abfrage an. Zusätzlich zum Clustered Index Update wird auch der Index ix_col1 aktualisiert. Die Indexaktualisierung und die Sortieroperation machen zusammen 64% der Ausführungskosten aus.
1. Entfernen des Index für die zu aktualisierende Spalte
Dieselbe Abfrage dauert 14 bis 18 Sekunden, wenn in Spalte 1 kein Index vorhanden ist. Daher wird eine Aktualisierungsabfrage schneller ausgeführt, wenn die zu aktualisierende Spalte keine Indexschlüsselspalte ist. Der Index kann immer nach Abschluss der Aktualisierung erstellt werden.
2. Update in kleineren Batches ausführen
Die Abfrage kann weiter optimiert werden, indem sie in kleineren Batches ausgeführt wird. Dies ist in der Regel schneller. Der folgende Code aktualisiert die Datensätze in Stapeln von 20000.
DECLARE @i INT=1 WHILE( @i <= 10 ) BEGIN UPDATE TOP(20000) tblverylargetable SET col1 = 'D' WHERE col1 = 'Dummy' SET @i=@i + 1 END
Die Ausführung der obigen Abfrage dauert 6-8 Sekunden. Selbst wenn die Aktualisierung fehlschlägt oder gestoppt werden muss, werden beim Aktualisieren in Stapeln nur Zeilen aus dem aktuellen Stapel zurückgesetzt.
3. Deaktivieren von Delete-Triggern
Trigger mit Cursor können die Leistung einer Delete-Abfrage extrem verlangsamen. Das Deaktivieren von After delete-Triggern erhöht die Abfrageleistung erheblich.
4. Ersetzen der Update-Anweisung durch eine Bulk-Insert-Operation
Eine Update-Anweisung ist eine vollständig protokollierte Operation und wird daher sicherlich viel Zeit in Anspruch nehmen, wenn Millionen von Zeilen aktualisiert werden sollen.Der schnellste Weg, um die Aktualisierungsabfrage zu beschleunigen, besteht darin, sie durch eine Masseneinfügeoperation zu ersetzen. Es ist eine minimal protokollierte Operation in einem einfachen und massenprotokollierten Wiederherstellungsmodell. Dies kann einfach erfolgen, indem Sie eine Masseneinfügung in eine neue Tabelle durchführen und die Tabelle dann in die ursprüngliche Tabelle umbenennen. Die erforderlichen Indizes und Einschränkungen können bei Bedarf für eine neue Tabelle erstellt werden.
Der folgende Code zeigt, wie das Update in einen Masseneinfügevorgang konvertiert werden kann. Die Ausführung dauert 4 Sekunden.
SELECT sno, CASE col1 WHEN 'Dummy' THEN 'D' ELSE col1 END AS col1, col2, col3 INTO tblverylargetabletemp FROM tblverylargetable
Der Bulk-Insert kann dann weiter optimiert werden, um eine zusätzliche Leistungssteigerung zu erzielen.
Hoffe das hilft!!!Facebook | Twitter | Join the fastest growing SQL Server group on FaceBook
Wie Sie uns auf FaceBook / Folgen Sie uns auf Twitter / Join the fastest growing SQL Server group on FaceBook