Grosse Datenmengen in SQL einfügen - Tuning?

[Joerginger]

Hallo liebe DP-Profis,

ich bastel seit geraumer Zeit an einem ZusatzTool zu unserem ERP-System herum, das Daten aus (fixed-length) Text"Dateien" in MS-SQL-Tabellen abbilden soll... Und bis auf kleine Problemchen läufts auch, theoretisch, aber natürlich nicht so performant, wie ich mir das vorstelle. Die Textfiles beinhalten pro Datensatz bis zu 530!!! Felder, und z.B. im Belegstamm haben wir hier derzeit ca. 170.000 Belege, von den Positionen red' ich momentan noch nicht mal (OK, doch, sind 2.67 Millionen). Und da ich mit SQL grad mal erst begonnen habe ists für mich natürlich eher schwierig (ausser mittels viel Recherche hier) mich vorwärtszutasten... (Für die Interessierten: ERP ist BüroWARE, und leider, nein, wir werden's in den nächsten 2 Jahren nicht austauschen...)

Meine Herangehensweise ist bisher, die Variablennamen und damit die Parametervorbereitung nur 1 x zu durchlaufen, und bei jedem gelesenen Datensatz dann nur mehr die Werte in die Parameter einzufügen, ungefähr so:

Zuerst zerlege ich mir den Text-Datensatz in ein Array auf das ich mit der Feldnummer zugreifen kann...
Danach kommt in etwa dies... jaja, nicht der sauberste Code aber ich steh' da ja noch am Anfang... Verzeiht...

Delphi-Quellcode:

for i:=0 to iH (höchste Feldnummer)
begin 
  try                                                         //wir versuchen, die Felder zuzuweisern
    sVA:=aSQLVals[i].sValue;                     //Inhalt aus dem Werte-Array holen     
    if (sVA<>'') or (regFillAll) then             //Wenns gefüllt ist oder alle Felder geschrieben werden sollen, hier ist regFillAll False...
        DBqu.Parameters.ParamByName(aBWVars[i].sNum).Value:=sVA;  //Wert in die Parameterliste
  except //sofern es Peng gemacht hat ;-)
     ... Errorhandling
  end;
end;

Danach findet die eigentliche Schreiberei statt, also myQuery.ExecSQL;

Daraus ergibt sich leider folgendes Problem:
Entweder schreibe ich ALLE Werte (also auch leere Felder) brav in jedes einzelne Feld in dem aktuellen Datensatz - das ist allerdings grottig langsam
oder ich schreibe (wie in diesem Beispiel) nur dann, wenn wirklich im Feld was drinsteht - dann bleibt - wenn nicht drübergeschrieben wird - der Inhalt dieses Feldes vom vorigen Satz hängen und steht im nächsten automatisch auch drin... eher blöd...

Gibt's für solche Anwendungen ein Patentrezept? Eine 'best practice'? Über ca. 10.000 Artikel brauch ich mit diesem Code (und dem Lesen und aufdröseln der Daten und und und) rund 7 Minuten - das wäre zu verkraften - wenn ich NUR die Felder mit Inhalt eintrage. Dann hat's Fehler (also Werte vom vorigen Satz). Wenn ich alle Felder eintrage dauerts dann ca. 40 Minuten
Und die Artikel sind eher noch eine kleinere Tabelle...

Irgendwo glaube ich gelesen zu haben, dass man(n) da irgendwas 'preparen' soll, aber wo und wie?

Oder gäbe es die Möglichkeit (Felder sind fixed length, also Artikelnummer ist immer von Stelle 1 bis 25) das dem SQL sinnvoll per Bulk bzw. external reinzupusten?

Und noch eine Frage: gibts eine Variante nicht alle Werte zu übergeben und dem Delphi / SQL zu erklären, dass er nicht die vom vorigen Satz 'gespeichert lassen soll'?

I steh momentan am Schlauch

GLG,

ein ratloser Mr. Joerginger

[Bummi]

Klingt für mich als ob Du ein Integration Services-Projekt (DTSX) mit SQL Server Business Intelligence Development Studio erstellen solltest.

[Joerginger]

Zitat von Bummi:

Klingt für mich als ob Du ein Integration Services-Projekt (DTSX) mit SQL Server Business Intelligence Development Studio erstellen solltest.

Danke, lieber Freund! Jetzt hast' mir einen zweiten Schlauch zum draufsteigen hergelegt

Ich hab von dem Dir genannten Teil genau NULL (und ich meine nicht das gute SQL NULL, das 'wir kennen den Zustand nicht' NULL) Ahnung, von daher wäre das wahrscheinlich Overkill? Ich mein, im Prinzip bin ich am Weg, nur denke ich mir dass es einen 'Mittelweg' geben sollte, also zwischen 7 Minuten und falsche Daten sowie 40 Minuten... Wenn die Daten mal drin sind ist es Pipifax die aktuell zu halten. Wenn ich über die 167.000 Belege drüberrausche und nur gucke, ob der TimeStamp von Geändert zwischen SQL und BüroWARE differiert und erst dann update ist das in 3 Minuten lockerst durch... Abgesehen davon dass ich mit Triggern in der BW mir das Ding eh relativ aktuell halte...

Geht sich halt nur um den Erstimport...

GLG, Erwin

[Bummi]

Eigentlich ist das nur ein zusammenklicken von ein paar "Komponenten" und etwas Mapping, dass dafür pfeilschnell durchläuft.
Wenn ich zu Fuß machen sollte würde ich wahrscheinlich einfach ein Adodataset mit der Bedingung 'where 1=0' aufmachen, und in einer Schleife appenden, die Felder die belegt sind eintragen und Posten.

[D-User]

wäre erst mal zu klären ob es überhaupt an deinem Code-Snippet liegt oder an der Eintragungsgeschwinditkeit des SQL-Servers.

Wird übers Netz eingetragen oder auf der gleichen Maschine?

Der Code selber ist sicherlich auch noch optimierbar,
das ParamByName z.B. muss erst mal mit Textsuche die Paramname
durcheiern.

Also selber eine Prep-Prozedur schreiben, die vor dem for
aufgerufen wird und die Feldnamen einem Array zuordnet.
Der dann in der Prozedur erfolgende Arrayzugriff ist dann
wesentlich schneller, da kein internes Find mehr erforderlich.

so mal für den Anfang...

[Joerginger]

Zitat:

Wird übers Netz eingetragen oder auf der gleichen Maschine?

Meine Entwicklungsumgebung läuft tutto completto auf einem Mac Mini mit 16GB. Darauf rennen diverse VM's, 1 x die XP-Entwicklerkiste mit D7 und auf einer anderen VM der SQL... Das ganze auf einer SSD. Also quasi local...

Zitat:

Also selber eine Prep-Prozedur schreiben, die vor dem for
aufgerufen wird und die Feldnamen einem Array zuordnet.
Der dann in der Prozedur erfolgende Arrayzugriff ist dann
wesentlich schneller, da kein internes Find mehr erforderlich.

Die Feldnamen hab ich ja in einem Array (aBWVars[i]), wenn ich Dich richtig verstehe sollte ich mein ParamByName(aBWVars[i].sNum) durch die Zugriffsvariable (also das i) ersetzen?

Darf ich nochmal kurz nachhaken, wie ich (evtl) die falschen Werte aus den 'vor-Datensätzen' sinnhaft entfernen könnte? Gibts da ein globales Löschen?

GLG aus Wien,

Erwin

[Joerginger]

Hab grad die Änderung eingepflegt, jetzt lautet die Zeile

DBqu.Parameters[i].Value:=sVA;

Ein kurzer Test über Artikelstamm hat ergeben: statt 7 Minuten nur mehr 1:30!!!!!!!!!! WOW!!! Das kann was!



GLG, Erwin

[nahpets]

Hallo,

musste mal in eine SQL-Serverdatenbank Unmengen von Daten reinschieben.
Zuerst also (ähnlich wie Du) die Textdatei zerbröselt, Parameter für das Insertstatement gefüllt und per ExecSQL satzweise Richtung Datenbank geschickt. War nicht wirklich schnell.

Dann bin ich hergegangen und habe die Insertstatement als Text erstellt, also Statements, die die Datenbank selbst verarbeiten kann. In der Form

Code:

Insert into tabelle (spalte1, spallte2, ..., Spalten) values ('Paula',4711,...,'0815');

Die kannst Du z. B. in den SQL.Text einer ADOQuery packen und alle 100 oder 1000 Zeilen ein ExecSQL, SQL.Text leeren und weiter.

Ungefähr sowas:

Delphi-Quellcode:

qry.SQL.Clear;
for i := 0 to slCSVDatei.Count - 1 do begin
  qry.sql.Add(FunktionCreateInsert(slCSVDatei[i]));
  if i mod 100 = 0 then begin // oder 1000, ausprobieren wie's am Schnellsten geht.
    qry.sql.Add('Commit;'); // Prüfen, ob das erforderlich ist, weiß ich momentan nicht.
    qry.ExecSQL;
    qry.sql.Clear;
  end;
end;
// Resteverarbeitung
qry.sql.Add('Commit;'); // Prüfen, ob das erforderlich ist, weiß ich momentan 
qry.ExecSQL;

Alternative:

Ebenfalls Insertstatements erstellen und in Textdatei(en) speichern.
Diese Datei(en) per ISQL (Kommandozeilentool von SQL-Server - zumindest früher mal, gibt's das noch?) in die Datenbank jagen.

Mit zweiter Methode habe ich vor Jahren mal einige Tage Laufzeit gegenüber der reinen Delphiversion eingespart.

[Joerginger]

Zitat:

Dann bin ich hergegangen und habe die Insertstatement als Text erstellt, also Statements, die die Datenbank selbst verarbeiten kann

Das war mein erster Optimierungsschritt. Wenn ich die ReadFile-Routine aufrufe bastel ich als erstes das SQL-Statement 'INSERT INTO' + TableName und so und dann 2 Strings, der Erste mit den Feldnamen und der Zweite mit ':0001, :0002' etc... Hat schon viel gebracht!

Jetzt hab ich folgendes gebaut:

Delphi-Quellcode:

  sVA:=aSQLVals[i].sValue;                           //STRING, WHAT ELSE
  if (sVA<>'') or (regFillAll) then          //WENN WAS ZU SCHREIBEN IST
    DBqu.Parameters[i].Value:=sVA        //Felder in Parameters zuweisen
  else                                                      //alternativ
    DBqu.Parameters[i].Value:='';          //Leerfeld, damit alles passt

Und man mag es nicht glauben: Das was vorher mit knapp 40!!! Minuten unterwegs war schafft jetzt alles in unter 2 Minuten... Ich lieeebe dieses Forum!

Jetzt fehlt mir nur mehr der Plan, was es mit diesem Befehl 'Prepare' auf sich hat. Oder ist das eine Forums-Ente?

GLG, Erwin

[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.

10.000 Datensätze in unter 2 Minuten klingt doch schon mal ganz gut, aber vielleicht kann man noch ein bisschen herauskitzeln.

Zum Prepared, nach dem du noch gefragt hast. Du arbeitest in deinem SQL-Statement ja schon mit den Parametern. Das einzige, was du noch tun musst, ist, folgenden Befehl einzufügen, direkt nachdem du den SQL-Befehl geschrieben hast (also bevor du die Parameter mit Werten befüllst):
DBqu.prepared:=true;
Das ist auch schon alles und kann tatsächlich einiges an Zeit bringen (wobei ich hier davon ausgehe, dass DBqu dein TADOQuery-Objekt ist).

Mit einem bisschen Rumspielen mit dem Datenbank-Cursor und dem Sperrverhalten kann man evtl. weitere Zeit sparen (den Tipp, mir das mal näher anzuschauen hatte mir bei ähnlichen Problemen wie deinem vor einiger Zeit mal Bernhard Geyer hier im Forum gegeben). In einem Projekt ist das hier bei mir z.B. eine Einstellung, die zu guter Performance führt:

Delphi-Quellcode:

    DBqu.CursorType:=ctOpenForwardOnly;
    DBqu.LockType:=ltBatchOptimistic;

Weitere Infos zu den jeweiligen Geschichten gibt es erstmal in der Online-Hilfe (und sicherlich zur Not auch hier )

Bis denn
Bommel