Verbessern von INSERT INTO - VON SELECT, SQL Query

Derzeit habe ich diese Art von Abfrage durch Die Programmierung generiert (c)

INSERT INTO TableName (Field1, Field2, Field3)
SELECT Field1, Field2, Field3 FROM TableName2

Das Problem ist, dass die SELECT ein Ergebnis von vielen Datensätzen haben kann (wie eine Million), so dass es viele Male dauert und das Ergebnis ist ein Verbindungstimeout.

Auch, wenn ich alle Einfügen in einzelne Einfügung (für dieses Beispiel eine Million Insert-Abfragen) trennen, dauert es sehr lange, um auszuführen ... aber es funktioniert ...

Gibt es eine Möglichkeit, diesen Abfragetyp zu verbessern?

Ich verwende MSSQl 2005

Thx

Antwort auf "Verbessern von INSERT INTO - VON SELECT, SQL Query " 10 von antworten

Nun, wenn es eine vollständige Kopie ist, frage ich mich, ob Sie nicht in Massenlastwerkzeuge suchen sollten?

  • BULK INSERT (TSQL)
  • SqlBulkCopy (.NET)
  • bcp (Befehlszeile)
  • usw.

Wenn Sie eine Where clause, I'd check that it was suitably indexed... Klausel hatten, würde ich überprüfen, ob sie entsprechend indiziert war...

Zusätzlich:

  • möglicherweise Indexe und Trigger fallen, bevor Sie die INSERT (nachher erstellen)
  • erstellen sie die gesamte Tabelle und die Verwendung von SELECT INTO ? (siehe Kommentare)

Set CommandTimeout property of the Eigenschaft der SqlCommand you're using to a sensible value (10 minutes or something). Remember that Sie verwenden, um einen vernünftigen Wert (10 Minuten oder so). Denken Sie daran, dass CommandTimeout is in seconds. in Sekunden ist.

Ich habe herausgefunden, dass, wenn Sie eine Menge INSERT-Anweisungen haben, die nacheinander ausgeführt werden, Sie die Leistung verbessern können, indem Sie eine 'GO'-Anweisung nach jeder xxxx Anzahl von Insert-Anweisungen hinzufügen:

...
INSERT INTO Table ( ... ) VALUES ( ... )
INSERT INTO Table ( ... ) VALUES ( ... )
INSERT INTO Table ( ... ) VALUES ( ... )
GO
INSERT INTO Table ( ... ) VALUES ( ... )
INSERT INTO Table ( ... ) VALUES ( ... )
...

Eine andere Möglichkeit ist vielleicht, sicherzustellen, dass Ihre INSERT INTO .. SELECT FROM-Abfrage fügt nicht alles auf einmal ein, sondern verwendet eine Art Paging-Technik:

INSERT INTO Table ...
SELECT ...
FROM OtherTable WHERE Id > x and Id < y

Sie geben nicht an, welches Problem Ihre Lösung mit diesem Ansatz hat. Offensichtlich würde ein WHERE den Rekord verengen. Aber wenn das Resultset nicht in der neuen Tabelle geändert wird, warum dann die Daten überhaupt replizieren? Warum nicht direkt von der Quelle abfragen?

entweder Massenlast mit einer Datei und dann bcp/BULK INSERT oder Batch es in Chargen von 5K oder so

In erster Linie nie versuchen, eine Million Datensätze durch C. einfügen. Verarbeiten Sie niemals große Gruppen von Datensätzen nacheinander. Dies ist Arbeit, die in der Datenbank von der Datenbank durchgeführt werden sollte. USe Bulk Insert oder SSIS oder DTS, um dies zu tun. Und dann planen Sie es als Job während der Freien Stunden. Wenn es immer noch zu lange dauert, dann schlage ich vor, dass Sie es in Batches von mehreren tausend ausführen (Sie müssen mit Ihrer eigenen Datenbank spielen, um zu sehen, was die beste Wahl ist, da die Zahl, die Sie sicher verarbeiten können, stark von den Tabellen abhängt, die Indizierung, wie schnell Ihr Server ist und wie viele Benutzer auch versuchen, mit den gleichen Tabellen zu arbeiten.

Wählen Sie (...) aus der Quelltabelle aus, in der primaryKey nicht in (Primärschlüssel aus Destinationtable auswählen)

commit

set @stop = (select count(primaryKey) from tableName where primaryKey not in destinstiontable)

Eine andere Möglichkeit, die wir in der Vergangenheit verwendet haben, ist, eine temporäre Tabelle mit den Primärschlüsseln zu erstellen, die wir während der Schleife verschieben und verwenden möchten. Auf diese Weise können Sie dies in einer Art Block-Mode tun, so dass Sie den großen Transaktions-Overhead vermeiden, wenn Sie storniert haben und es zurücksetzen musste.

Grundsätzlich ist eine Einfügung in den Tabellennamen (...) aus dem Tabellennamen auswählen , in dem der Primärschlüssel einreicht (oben 10000-Taste aus temptable auswählen)

die Top 10000, die Sie in einem sekundären Resultset wünschen, damit Sie sie aus der temporären Tabelle entfernen können, damit sie nicht erneut verarbeitet werden.

Eine andere Möglichkeit wäre, Cursor zu verwenden, um die Anzahl der Datensätze zu reduzieren, die Sie gleichzeitig verarbeiten.

Eine andere Schleifenmethode wäre, so etwas in einer While-Schleife zu tun.

@stop als int Ende

Nicht die effizienteste, aber es würde funktionieren und sollte Ihnen erlauben, die Transaktion loggt zu halten. Wenn Sie es nicht benötigen, stellen Sie auch sicher, dass Sie das Schlüsselwort no lock verwenden, damit Sie andere Transaktionen nicht blockieren, wenn Sie diesen großen Umzug vornehmen (es sei denn, Sie verwenden BCP oder DTS, da sie viel schneller sind).

Einiges von dem, was gesagt wurde, ist wahrscheinlich Ihre beste Wette, obwohl. Verwenden Sie BCP, DTS oder ein anderes Massenwerkzeug. Wenn Sie Indizes fallen lassen können, wird es die Dinge viel schneller gehen.

Transaktion

in destinationTable (...) einfügen legen Sie @stop = (select count(primaryKey) aus tableName, wobei primaryKey nicht in destinstiontable)

während (@stop > 0)

Einige gute Antworten hier.

Fügen Sie einfach hinzu, dass, wenn Sie Indizes in der Zieltabelle haben, sie den Vorgang verlangsamen. Das Neuaufbauen des Indexes kann jedoch manchmal lange dauern, wenn Sie die Drop-Erstellungstechnik verwenden.

Wenn Sie die Indizes nicht löschen möchten, verwenden Sie ein ORDER BY in your in Ihrem SELECT that matches the , das dem gruppierten Zielindex entspricht, dies scheint zu helfen (wahrscheinlich hilft es, die Seitenaufteilungen zu minimieren).

Ok, es gibt ein paar grundlegende Fragen.

  1. I-O - Das Einfügen in eine Tabelle beim Lesen aus einer anderen Tabelle führt höchstwahrscheinlich zu Datenträgerkonflikten, wenn sich die Tabellen nicht auf separaten Datenträgern befinden. Setzen Sie die gegenüberliegenden Tische auf physisch unterschiedliche Spindeln.

  2. Transaktionsprotokoll – Sie müssen sicherstellen, dass sich Ihr Transaktionsprotokoll auf dem eigenen Datenträger befindet, oder in kleineren Transaktionen (ein paar tausend Zeilen gleichzeitig) arbeiten oder BCP-Bulk-Einfügung verwenden, die nicht protokolliert ist.

  3. Clustered-Indizes - Wenn Sie alle diese Zeilen in eine Zieltabelle einfügen und es sich um einen gruppierten Index handelt (die physischen Auftragsdaten werden nicht sequenziell auf den Datenträger geschrieben), werden die Festplatten-E/A-Anforderungen aufgrund von Seitenaufteilungen und Neuzuweisungen nicht sequenziell geschrieben. Eine einfache Lösung kann darin bestehen, einen gruppierten Index für die Empfängertabelle zu erstellen, bei dem es sich um einen sequenziellen Seed-Schlüssel handelt. Dadurch wird in der Regel sichergestellt, dass Sie sequenzielle Schreibvorgänge in die Tabelle und fast immer am Ende erhalten.

  4. Dateierweiterung - Stellen Sie sicher, dass Sie SQL eingestellt haben, um ihre Dateien mit einer anständigen Rate zu erweitern, wie 10% oder so. Andernfalls muss es ständig die Größe seiner Dateien ändern und die Festplatte auf Null stellen. Es gibt Möglichkeiten, zu verhindern, dass der Datenträger ebenfalls auf Null gesetzt werden muss, z. B. die Berechtigung Massendateibetrieb in Ihren Gruppenrichtlinien für den Sql Service-Benutzer aktivieren.

Ganz ehrlich, abgesehen davon und ein paar der anderen Vorschläge, ist es sehr unwahrscheinlich, dass Sie eine Einfügung mit Millionen von Zeilen in einer Transaktion wirklich schnell machen werden. Wenn Sie dies über Bulk Insert tun würden, wäre es drastisch schneller, obwohl es aus Anwendungssicht möglicherweise nicht das ist, was Sie benötigen.

Und Sie könnten in der Lage sein, die Leistung mit Tablock-Hinweise auf dem Tisch zu verbessern, in den Sie einfügen.

Sie können die Leistung der Auswahl verbessern.

Haben Sie sql über sql Server Management Studio getestet, um zu sehen, wie lange es tatsächlich dauert? Ich würde dort anfangen.