Grosse Datenmengen in SQL einfügen - Tuning?

[Furtbichler]

Ist 'BULK INSERT' keine Option? So kann man mit einem SQL-Befehl in null-komma-nix Millionen von Datensätzen importieren.

SQL-Code:

BULK INSERT 
  MyTable 
FROM 
  'C:\Somewhere\MyDataFile.txt'
WITH 
  FORMATFILE = 'C:\SomewhereElse\MyFormat.xml'
---
--- oder
---
SELECT * FROM OPENROWSET(BULK 'C:\Somewhere\MyDataFile.txt' FORMATFILE='C:\SomewhereElse\MyFormat.xml').

Das Schema für die Format-Datei ist hier zu finden und sähe ungefähr so aus:

Code:

<?xml version="1.0"?>
<BCPFORMAT 
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
    <FIELD ID="1" xsi:type="CharFixed" LENGTH="20"/> 
    <FIELD ID="2" xsi:type="CharFixed" LENGTH="7"/>
    <FIELD ID="3" xsi:type="CharFixed" LENGTH="40"/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="Name" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="2" NAME="Age" xsi:type="SQLINT"/>
    <COLUMN SOURCE="3" NAME="Street" xsi:type="SQLVARYCHAR"/>
  </ROW>
</BCPFORMAT>

Für eine Datei bestehend aus drei Spalten (Längen: 20,7 und 40) und einem String an der 1. und 3. Stelle, sowie einer INT-Zahl an der 2.

Deine 10.000 Datensätze wären so in geschätzten 0.5 Sekunden importiert. Bei 20-50 Mio Records sind das bei meinem Fall ca. 20 Sekunden (sofern ich mich erinnere).

Zitat von DeddyH:

Der dort angewendete Trick war ganz einfach

Der MethodenName 'xxxByName' impliziert eine Suche in einem ungeordneten Array. Das muss langsam sein. Genausowenig, wie man Felder über ihren Namen in einer Schleife füllen soll  MyDataset['FieldName'] := 'Values'; sollte man das mit Parametern tun.

Nichtsdestotrotz würde ich der 'BULK INSERT' Variante eine Chance geben. Sind die Dateien nicht auf dem Server verfügbar, würde ich mich mit BCP.EXE beschäftigen. Die von Bummy propagierte SSIS-Lösung dürfte dies ebenso umsetzen.

[Joerginger]

Mal herzlichen Dank an alle 'entfernten Mitarbeiter'! Für die kleineren Dateien (bis 20.000 Sätze) pfeift's so eh... Leider geht mir (da ich hier bei mir nur einen Express im Einsatz hab) danach schon bei den Belegen die Luft aus und er wird wieder extrem langsam... Sehr seltsam. Die Artikel (10.038 Records) sind - vielleicht auch gechached? - in 31 Sekunden in der DB, die Adressen (mit 19.000 Sätzen) brauchen schon 32 Minuten, die Belege (167.000) 1:14 benötigt. OK, ist immer noch wesentlich flotter als vorher... Werds aber jetzt mal auf einem echten SQL-Server testen, ohne Limits. Irgendwie legt mir der zu viele 'Denkpausen' ein, wo sich de facto der Fortschrittsbalken kaum bewegt.

Zitat:

Ist 'BULK INSERT' keine Option? So kann man mit einem SQL-Befehl in null-komma-nix Millionen von Datensätzen importieren.

Oja, das hab ich auch mehrfach als Lösung (zumindest für die Positionen) angedacht, das muss ich mir an Hand Deines Beispiels noch mal genauer ansehen. Man muss dazu erwähnen, dass wir ca. 2.6 Millionen Posis haben, der Datensatz hat 1022 Byte (+CR-LF) und besteht aus... immerhin 216 Datenfeldern die ich füllen muss oder NULLen. Eine Schleife über 2.6 Mio's durchiterieren, da fällt sogar schon der Unterschied zwischen inc(i) und i:=i+1 ins Gewicht

Wenn ich das mit BULK jetzt richtig interpretiere - ich dachte nämlich dass das nur CSV-Format kann - könnte ich die Daten zerlegen, in ein XML schmieren und dann per SQLCommand sagen: Hol's Stöckchen? Das hätt' große Phantasie.

OTOH hat die BULK Variante einen gravierenden Nachteil, nämlich, dass Du nie weisst wo's Peng macht wenns Peng mach... Angeblich, wurde mir nur gesagt? Stimmt das?

GLG, Erwin

[Joerginger]

Für die Interessierten: Hab BULK ausprobiert, der glatte Wahnsinn...

Lesen der Positionsdatei und Ausgeben der Sätze (nur die Sinnvollen, ohne Umwandlungen) dauert ca. 5 Minuten. Import via BULK, naja, 10 Sekunden? Wenn überhaupt. Demgegenüber waren's vorher ca. 7 Stunden...

So, ab an das XML-Schreiben...

GLG, Erwin

[Sir Rufo]

Ähm, jetzt aber nicht die Daten in XML umwandeln, sondern eine Format-Datei in XML erstellen, die den Aufbau der Textdatei beschreibt.

[Furtbichler]

Thank you, Sir (Rufo)

I was about to mention that.

[Joerginger]

JoJo, mein Fehler, hab beim ersten Mal drüberlesen gedacht: Geil, XML und gut ist. Aaaaber, nach intensiver Lektüre auf der MSDN-Seite hab ich eben folgendes gezimmert:

TEXTDatei lesen (und die leeren / unnötigen Sätze filtern)
Ergebnis in Textdatei - die hat nur 'sinnvollen' Inhalt, ist fixedlength
Aus meinem Array mit Beginn / Länge und Feldname ein XML für fixedLength ausgeben (Der Import klappt ohne dass ich den DS auch nur 1 x angreifen muss...)
SQL Table droppen
SQL Table creatern
Table mittels Bulk füllen...

Hopsassa! Klappt sensationell... Und da ich alles Parametriert habe und quasi nur über eine einzige Dateinummer zugreife klappts mit egal welcher Table

GLG, Erwin

[Furtbichler]

Zitat von Joerginger:

TEXTDatei lesen (und die leeren / unnötigen Sätze filtern)

Könnte in SQL schneller gehen.
[QUOTEUnd da ich alles parametriert habe [/QUOTE] Einmal will ich loswerden, was ich mal gelernt habe
Parametrisieren: Das Ausstatten mit Parametern.
Parametrieren: Das Belegen der Parameter mit Werten.

Du hast also dein Verfahren parametrisiert, damit Du es im Anwendungsfall mit den entsprechenden Werten parametrieren kannst.

Ich komm sonst nicht so als Klugscheißer daher (psst!), aber einmal darf man doch