Grosse Datenmengen in SQL einfügen - Tuning?

[Joerginger]

Oh Mann... das glaub' ich einfach alles nicht mehr...

Also zuerstmal ganz wesentlich (das hab ich in den letzten Minuten durch Probieren herausgefunden):

Delphi-Quellcode:

var
  vNull:            variant;       //Da nicht initiiert wird bleibt die NULL!

und meinen Code hab ich zu

Delphi-Quellcode:

  if (sVA<>'') or (regFillAll) then          //WENN WAS ZU SCHREIBEN IST
    DBqu.Parameters[i].Value:=sVA        //Felder in Parameters zuweisen
  else
    DBqu.Parameters[i].Value:=vNull;

geändert. Einfach nur vNull zuweisen... So gemacht: ca. 2 Minuten!!! Wenn man's mit '' befüllt (statt mit vNull) dauerts 6-7 Minuten!!!

Und - jetzt der Hammer: 10.038 Sätze inkl. zerhacken, einarrayen, einflicken, reinschmieren UND INKL. preparen (und zwar innerhalb der Schleife, bei jedem Datensatz!):

Zitat:

StartZeit: 22:54:36
EndeZeit:22:55:13

Den Cursor hab ich bisher auf clUseClient. Ma kucken ob da noch ein paar Sekunden drin sind...

Alter Schwede!!! Von knapp 40 Minuten auf 37 Sekunden ist irgendwie schon der Hammer, ne nech? Und vor allem: Alle Sätze voll korregd, inkl. NULL's! Wenn ich nicht momentan AntiAlkoFix wäre, ein bis zehn Bierli hätten wir uns hier allemal verdient

GLG, Erwin

[Bbommel]

Zitat von Joerginger:

Und - jetzt der Hammer: 10.038 Sätze inkl. zerhacken, einarrayen, einflicken, reinschmieren UND INKL. preparen (und zwar innerhalb der Schleife, bei jedem Datensatz!):

Aber das Prepare solltest du mal aus der Schleife rausnehmen. Die Abfrage soll ja nur einmal vorbereitet werden und danach einfach nur noch mit Werten "vollgeballert" werden. Bringt dir vielleicht noch ein paar Sekündchen.

Also:

Delphi-Quellcode:

    DBqu.CursorType:=ctOpenForwardOnly;
    DBqu.LockType:=ltBatchOptimistic; 
    DBqu.SQL.Text:='blablabla';
    DBqu.Prepared:=true;
    for i:=0 to max do begin
      ...
      DBqu.Parameters[i].Value:=wuppdi;
      ...
      DBqu.ExecSQL;
    end;

Viel Erfolg...

[DeddyH]

Zitat von Bbommel:

Hm, wusste gar nicht, dass das mit dem ParamByName eine solche Performance-Bremse ist (klar, eigentlich aber logisch) - sollte man mal im Hinterkopf behalten.

Dazu habe ich vor einiger Zeit mal einen Artikel von Marco Cantu gelesen. Der dort angewendete Trick war ganz einfach: es wurde je Parameter eine lokale Variable deklariert und einmalig belegt. Innerhalb der Schleife wurde dann nur noch mit diesen Variablen gearbeitet, also ungefähr so:

Delphi-Quellcode:

var
  ParamName, ParamVorname: TParameter;
begin
  ParamName := Dataset.ParamByName('Name');
  ParamVorname := Dataset.ParamByName('Vorname');
  for i := 0 to Liste.Count - 1 do
    begin
      ParamName.Value := Liste[i].Name;
      ParamVorname.Value := Liste[i].Vorname;
      Dataset.ExecSQL;
    end;

[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