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:

Zitat von Bummi (Beitrag 1218105)

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 :lol:

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

Delphi-Quellcode:

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

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

:thumb::thumb::thumb:

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):

Delphi-Quellcode:

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

[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!!! :cyclops:

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 :thumb:

GLG, Erwin

[Bbommel]

8-)

Zitat:

Zitat von Joerginger (Beitrag 1218132)

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:

Zitat von Bbommel (Beitrag 1218130)

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:

Zitat von DeddyH (Beitrag 1218144)

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

Delphi-Quellcode:

 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 :thumb:

GLG, Erwin

[Furtbichler]

Zitat:

Zitat von Joerginger (Beitrag 1218311)

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 :stupid:
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 :-)

[Joerginger]

Zitat:

Könnte in SQL schneller gehen.

Jein. Die Verarbeitung 100%, aber dem SQL mal die BüroWARE-Logik zu erklären, warum manche Sätze leer sind, manche Sätze vom Programmierer für "andere" Zwecke mißbraucht wurden etc... Ich weiss warum was wo steht und in Delphi kann ich's gut abhandeln. Den SQL jetzt so zu erlernen dass ich schlafwandlerisch wie in Delphi fuhrwerken kann wird wahrscheinlich net sein. Abgesehen davon: Der gute Mann hat, da der Platz in 1024 Byte Positionssatz zu knapp wurde einfach eine 2te Datei drangehängt wo nochmal 1024 Bytes platz finden. D.h. ich muss die 1. Datei durchiterieren, wenn ich einen sinnvollen (Positions)Satz gefunden habe mit der aktuellen Satznummer als Index in der 2. Datei via Pervasive-Key suchen, das gelesene hinzufügen etc... You see :roll: Btw: Mit ca. 5 Minuten für die 2.6 Mio Sätze Grundabgleich kann ich aber 1000x leben... Wenn ich's im SQL mit allen Abfragen und Spielis auf 2 Minuten kriege: what 4? Die Jungs wissen wieviele Daten sie haben. 7 Stunden wäre arg gewesen, aber so...

Zitat:

Einmal will ich loswerden, was ich mal gelernt habe :stupid:
Parametrisieren: Das Ausstatten mit Parametern.
Parametrieren: Das Belegen der Parameter mit Werten.

Danke (ernst gemeint), obschon ich ein recht eloquentes Kerlchen bin - das hab ich nicht gewusst...

Zitat:

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

Für diese Hilfe hast Du (fast) alle Rechte :oops::thumb:

GLG, Erwin