Grosse Datenmengen in SQL einfügen - Tuning?

[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

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