Grosse Datenmengen in SQL einfügen - Tuning?

[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