Hochladen einer umfangreichen CSV-Datei in die SQL Server-Datenbank

Question

Ich muss eine riesige CSV-Datei (16 GB, 65+ Millionen Datensätze) in eine einzelne Tabelle in einer SQL Server 2005-Datenbank hochladen. Hat jemand einen Tipp, wie man das am besten macht?

Einzelheiten

Ich verwende derzeit eine C#-Konsolenanwendung (.NET Framework 2.0), um die Importdatei in Dateien mit 50000 Datensätzen aufzuteilen und dann jede Datei zu verarbeiten. Ich lade die Datensätze von der Konsolenanwendung aus mit der Klasse SqlBulkCopy in Stapeln von 5000 in die Datenbank hoch. Das Aufteilen der Dateien dauert etwa 30 Minuten, und das Hochladen des gesamten Datensatzes (über 65 Millionen Datensätze) dauert etwa 4,5 Stunden. Die Größe der generierten Datei und die Größe des Stapel-Uploads sind beides Konfigurationseinstellungen, und ich versuche, den Wert beider zu erhöhen, um die Leistung zu verbessern. Zur Ausführung der Anwendung verwenden wir einen Quad-Core-Server mit 16 GB RAM. Dieser Server ist auch der Datenbankserver.

Update

In Anbetracht der bisherigen Antworten ist zu beachten, dass vor der Einfuhr:

• Die Datenbanktabelle wird abgeschnitten, und alle Indizes und Beschränkungen werden gelöscht.
• Die Datenbank wird verkleinert, und es wird Speicherplatz zurückgewonnen.

Nachdem der Import abgeschlossen ist:

• Die Indizes werden neu erstellt

Wenn Sie andere Ansätze vorschlagen können oder Möglichkeiten, wie wir die bestehende Importanwendung verbessern können, wäre ich Ihnen dankbar. Danke!

Verwandte Frage

Die folgende Frage könnte für andere, die sich mit diesem Problem befassen, von Nutzen sein:

• Potenzielle Fallstricke beim Einfügen von Millionen von Datensätzen in SQL Server 2005 aus einer flachen Datei

Lösung

Ich habe die Auswirkungen der Änderung der Stapelgröße und der Größe der Splitdateien untersucht und festgestellt, dass Stapel von 500 Datensätzen und Splitdateien von 200.000 Datensätzen für meine Anwendung am besten funktionieren. Die Verwendung der SqlBulkCopyOptions.TableLock auch geholfen. Siehe die Antwort auf diese Frage question für weitere Einzelheiten.

Ich habe auch die Verwendung eines SSIS-DTS-Pakets und eines BULK INSERT SQL-Skript. Das SSIS-Paket schien schneller zu sein, bot mir aber nicht die Möglichkeit, ungültige Datensätze usw. zu erfassen. Das BULK INSERT Das SQL-Skript war zwar langsamer als das SSIS-Paket, aber wesentlich schneller als die C#-Anwendung. Es ermöglichte mir, Fehler usw. aufzuzeichnen, und aus diesem Grund akzeptiere ich die BULK INSERT Antwort von ConcernedOfTunbridgeWells als die Lösung. Ich bin mir bewusst, dass dies vielleicht nicht die beste Antwort für alle ist, die mit diesem Problem konfrontiert sind, aber es löst mein unmittelbares Problem.

Vielen Dank an alle, die geantwortet haben.

Mit freundlichen Grüßen, MagicAndi

Answer 1

BULK INSERT wird vom DBMS selbst ausgeführt und liest Dateien, die durch eine bcp-Steuerdatei beschrieben werden, aus einem Verzeichnis auf dem Server (oder auf diesem gemountet). Schreiben Sie eine Anwendung, die die Datei in kleinere Teile zerlegt, diese in einem geeigneten Verzeichnis ablegt und einen Wrapper ausführt, der eine Reihe von BULK INSERTS . Sie können bei Bedarf mehrere Threads parallel laufen lassen.

Dies ist wahrscheinlich die schnellste Zeit, die eine Massenladung erreichen kann. Wenn in der Bulk-Load-Datei ein geeigneter Partitionierungsschlüssel verfügbar ist, können Sie die Staging-Tabelle auch in ein Partitionsschema einfügen.

Wenn Sie große Mengen in eine Tabelle mit einem geclusterten Index laden, stellen Sie außerdem sicher, dass die Daten in der gleichen Reihenfolge wie der Index sortiert sind. Bei großen Datensätzen ist Merge Sort Ihr Freund.

Answer 2

Haben Sie SSIS (SQL Server Integration Services) ausprobiert?

Answer 3

El SqlBulkCopy Klasse, die Sie bereits verwenden, ist die beste Wahl. Das Beste, was Sie von hier aus in Ihrem c#-Code tun können, ist mit Ihrem speziellen System und Ihren Daten zu experimentieren, um zu sehen, welche Losgrößen am besten funktionieren. Aber das tun Sie ja bereits.

Über den Client-Code hinaus gibt es möglicherweise einige Dinge, die Sie auf dem Server tun können, um den Import effizienter zu gestalten:

• Versuchen Sie, die Tabellen- und Datenbankgröße vor dem Import auf eine Größe einzustellen, die für den gesamten Satz ausreicht. Sie wollen sich nicht auf die automatische Vergrößerung verlassen.

• Je nachdem, wie die Daten sortiert sind und welche Indizes in der Tabelle vorhanden sind, sollten Sie Indizes, die nicht mit der Reihenfolge übereinstimmen, in der die Datensätze importiert werden, löschen und sie nach dem Import neu erstellen.

• Schließlich ist es verlockend zu versuchen, dies parallel laufen zu lassen, mit einigen Threads, die Masseneinfügungen auf einmal durchführen. Der größte Engpass ist jedoch mit Sicherheit die Festplattenleistung. Alles, was Sie am physischen Server tun können, um diese zu verbessern (neue Festplatten, san usw.), wird viel mehr helfen.

Answer 4

Möglicherweise können Sie sich den Schritt des Aufteilens der Dateien wie folgt sparen:

• Instanziieren Sie einen IDataReader, um die Werte aus der CSV-Eingabedatei zu lesen. Es gibt mehrere Möglichkeiten, dies zu tun: Die einfachste ist wahrscheinlich die Verwendung des Microsoft OleDb Jet-Treibers. Googeln Sie danach, wenn Sie mehr Informationen benötigen - es gibt z.B. einige Informationen in diese StackOverflow-Frage .

  Eine alternative Methode ist die Anwendung einer Technik, wie sie von www.csvreader.com .

• Instanziieren Sie ein SqlBulkCopy-Objekt und setzen Sie die Eigenschaften BatchSize und BulkCopyTimeout auf geeignete Werte.

• Übergeben Sie den IDataReader an die Methode SqlBulkCopy.WriteToServer.

Ich habe diese Technik erfolgreich bei großen Dateien angewandt, aber nicht so groß wie Ihre.

Answer 5

Voir diese y diese Blogbeiträge zum Vergleich. Es scheint die beste Alternative zu sein, BulkInsert mit der Option TABLOCK auf true zu verwenden.