Der schnellste Weg, um 120 Millionen Datensätze zu aktualisieren
Muss ich initialisieren Sie ein neues Feld mit dem Wert -1 in der 120-Mio-Datensatz-Tabelle.
Update table
set int_field = -1;
Habe ich es laufen lassen für 5 Stunden vor dem Abbrechen.
Ich versucht, läuft es mit der Transaktion read uncommitted festgelegt mit dem gleichen Ergebnis.
Recovery Model = Simple.
MS SQL Server 2005
Einen Rat bekommen, diese schneller zu erledigen?
InformationsquelleAutor der Frage Bob Probst | 2010-09-14
Du musst angemeldet sein, um einen Kommentar abzugeben.
Die einzige vernünftige Möglichkeit zum aktualisieren einer Tabelle von 120M records ist mit einem
SELECT
Aussage, das füllt ein zweiten Tabelle. Sie haben zu kümmern, wenn dies zu tun. Die Anleitung unten.Einfachen Fall
Für eine Tabelle ohne einen gruppierten index in einer Zeit, w/out gleichzeitige DML:
SELECT *, new_col = 1 INTO clone.BaseTable FROM dbo.BaseTable
Wenn Sie können nicht erstellen Sie ein Klon-schema, eine andere Tabelle mit dem Namen im selben schema zu tun. Erinnern Sie sich, benennen Sie alle Ihre Einschränkungen und Trigger (falls zutreffend) nach dem Wechsel.
Nicht einfachen Fall
Erste, erstellen Ihre
BaseTable
mit dem gleichen Namen unter einem anderen schema, z.B.clone.BaseTable
. Mit einem separaten schema vereinfacht die rename-Vorgang später.Dinge minimal.
Dann testen Sie Ihre insert-w/1000 Zeilen:
Prüfen Sie die Ergebnisse. Wenn alles scheint in Ordnung:
Dies wird eine Weile dauern, aber nicht annähernd so lange wie ein update. Sobald es abgeschlossen ist, überprüfen Sie die Daten in die Klon-Tabelle, um sicherzustellen, dass es alles richtig ist.
Dann neu alle nicht gruppierten Primärschlüssel/unique-Einschränkungen/Indizes und foreign-key-constraints (in dieser Reihenfolge). Neu-default-und check-Einschränkungen, falls zutreffend. Neu alle Trigger. Neu jede Einschränkung, index oder einen trigger in einer separaten batch. eg:
Schließlich bewegen
dbo.BaseTable
auf ein backup-schema und dieclone.BaseTable
dem dbo-schema (oder wo auch immer Ihr Tisch soll Leben).Wenn Sie benötigen, um freien Speicherplatz, können Sie drop Ihre ursprüngliche Tabelle, in dieser Zeit, aber es kann sein, klug zu halten, um eine Weile länger.
Unnötig zu sagen, dies ist im Idealfall ein offline Betrieb. Wenn Sie haben Menschen, die Daten ändern, während Sie diesen Vorgang ausführen, müssen Sie eine true-up-Betrieb das schema wechseln. Ich empfehle die Erstellung eines triggers auf
dbo.BaseTable
anmelden alle DML-auf einen separaten Tisch. Aktivieren Sie diese Auslöser, bevor Sie Sie einfügen. Dann in der gleichen Transaktion, die Sie ausführen, die das schema übertragen, verwenden Sie die log-Tabelle zum führen einer true-up. Testen Sie diese zunächst auf eine Teilmenge der Daten! Deltas sind einfach zu vermasseln.InformationsquelleAutor der Antwort Peter Radocchia
Wenn Sie den Speicherplatz, den Sie verwenden konnten, WÄHLEN Sie IN, und erstellen Sie eine neue Tabelle. Es ist minimal protokolliert, so würde es viel schneller gehen
InformationsquelleAutor der Antwort Mike Forman
Breche ich die Aufgabe in kleinere Einheiten. Test mit verschiedenen batch-Größe der Intervalle für die Tabelle, bis Sie ein Intervall, das optimal abschneidet. Hier ist ein Beispiel, das ich in der Vergangenheit verwendet haben.
InformationsquelleAutor der Antwort KC.
Wenn Ihr int_field indiziert ist, entfernen Sie den index, vor dem ausführen der Aktualisierung. Erstellen Sie dann den index wieder...
5 Stunden scheinen, wie eine Menge für 120 Millionen recs.
InformationsquelleAutor der Antwort Pablo Santa Cruz
sehen, wie schnell das dauert, anzupassen und bei Bedarf wiederholen
InformationsquelleAutor der Antwort BlackTigerX
Was ich versuchen würde ist zuerst
fallen alle Einschränkungen, Indizes, Trigger und Volltext-Indizes ersten, bevor Sie aktualisieren.
Wenn oben war nicht performant genug, mein Nächster Schritt wäre
erstellen Sie eine CSV-Datei mit 12 Millionen Datensätze und bulk-import mithilfe von bcp.
Schließlich, ich würde erstellen Sie eine neue heap-Tabelle (dh Tabelle ohne primary key) und ohne Indizes auf einer anderen Dateigruppe, füllen Sie es mit -1. Partition der alten Tabelle, und fügen Sie die neue partition mit "switch".
InformationsquelleAutor der Antwort Sung Kim
Wenn hinzufügen eine neue Spalte ("initialisieren ein neues Feld") und die Einstellung eines einzelnen Wertes zu jeder vorhandenen Zeile, verwende ich die folgende Taktik:
Wenn die Spalte null-Werte zulässt und Sie nicht über einen "erklärt" - Einschränkung, die Spalte auf null gesetzt werden für alle Zeilen.
InformationsquelleAutor der Antwort Philip Kelley
Klingt wie eine Indizierung problem, wie Pabla Santa Cruz erwähnt. Seit dem update ist nicht Bedingung, DROP können Sie die Spalte und RE-FÜGEN Sie es mit einem DEFAULT-Wert.
InformationsquelleAutor der Antwort Brad
In der Allgemeinen Empfehlung weiter:
Aber im speziellen Fall sollten Sie die am besten geeignete Lösung oder deren Kombination.
Auch Bedenken, dass irgendwann ein index nützlich sein könnte, z.B. beim ausführen von update-nicht-indizierte Spalte eine Bedingung.
InformationsquelleAutor der Antwort alexber
Wenn die Tabelle einen index, mit dem man Durchlaufen würde ich
update top(10000)
- Anweisung in eine while-Schleife, die sich über die Daten. Die würde verhindern, dass das Transaktionsprotokoll schlank und haben nicht so einen großen Einfluss auf die disk-system. Auch würde ich empfehlen, spielen mitmaxdop
option (setzen Sie näher an 1).InformationsquelleAutor der Antwort Denis Valeev
InformationsquelleAutor der Antwort aads