Insert Optimierung

[Franzelchen]

Hallo,

Den folgenden Quelltext habe ich im Forum gefunden und für meinen Zweck angepaßt:

Delphi-Quellcode:

procedure TForm1.InputDatasetClick(Sender: TObject);
var                i:Integer;
 startzeit,stopzeit : TDateTime;

begin
startzeit := now;
Screen.Cursor := crHourGlass;
ZConnection.Database := 'c:\msql\firedb.fdb';

 for i:=0 to SG1.RowCount-1 do
  begin
   ZQuery1.SQL.text:='INSERT INTO TestTabelle (Wert1, Wert2) VALUES(:v1, :v2)';
        with ZQuery1.params do
    begin
      ParamValues['v1']:=SG1.Cells[0, i];
      ParamValues['v2']:=SG1.Cells[1, i];
    end; // with
   ZQuery1.ExecSQL;
  end; //  i
Screen.Cursor := crDefault;
stopzeit := now;
Panel1.Caption :='SuchZeit :  '+ FormatDateTime('nn:ss:zzz', StopZeit - StartZeit) ;
end;

Das Übertragen von ca. 35000 Datensätzen dauert ca. 4 Minuten.

Kann die Übertragung beschleunigt werden?

Ich habe mich natürlich über einige andere Möglichkeiten informiert.
zB. Bulk insert wird aber von Firebird leider nicht unterstützt.

oder external tables bei denen Sicherheitsbedenken bestehen? Welche das sind...?? daraus folgt vermutlich für mich nicht verwendbar.

Ich bitte um Eure Hilfe

[himitsu]

Das ZQuery1.SQL.Text := 'INSERT INTO TestTabelle (Wert1, Wert2) VALUES(:v1, :v2)'; reicht doch einmal aus, also vor der Schleife.

Und du kannst mal probieren, ob eine Zusammenfassung mehrere Werte noch ein bissl was rausholen kannst.

Delphi-Quellcode:

ZQuery1.SQL.Text := 'INSERT INTO TestTabelle (Wert1, Wert2) VALUES (:v0, :v1), (:v2, :v3), (:v4, :v5), (:v6, :v7);';
...
with ZQuery1.Params do begin
  ParamValues[0] := SG1.Cells[0, i];
  ParamValues[1] := SG1.Cells[1, i];
  ParamValues[2] := SG1.Cells[0, i + 1];
  ParamValues[3] := SG1.Cells[1, i + 1];
  ParamValues[4] := SG1.Cells[0, i + 2];
  ParamValues[5] := SG1.Cells[1, i + 2];
  ParamValues[6] := SG1.Cells[0, i + 3];
  ParamValues[7] := SG1.Cells[1, i + 3];
end;
ZQuery1.ExecSQL;

Mit vielen kleinen Inserts, haben wir in Postgres, mit PgDAC, etwa 60 bis maximal 250 Datensätze rausbekommen.
Mit deaktivierten Triggern und soeinem zusammengefassten Insert sind wir schon auf über 600 DS/s gekommen.

Das liegt aber irgendwie am Delphi, denn in C# soll es wesentlich schneller gehn ... ich glaub da was von über 10.000 DS/s gehört zu haben.

Wenn dei DBMS sowas wie einen COPY-Befehl kennt, worüber man z.B. die Daten via einer CSV-Datei direkt in die Datenbank reinbekommt, dann wäre das wohl der schnellste Weg.

[p80286]

Du schleppst 35.000 datensätze in einem Stringgrid mit Dir herum?
Wie wäre es dafür eine Stringlist oder eine List zu nehmen?
Ein Stringgrid ist ja eigentlich für die Datenanzeige zuständig, nicht für die Datenhaltung. Da sollte noch etwas für Dich drin sein.

Gruß
K-H

[himitsu]

Nunja, in einem TStringGrid stecken auch nur ein paar TStringLists.

Oder wie wäre es mit einem Memory-DataSet und einem DBGrid?

[ConstantGardener]

...ausserdem solltest Du den Query Text vor dem For setzen und mit Prepare der Datenbank die Chance geben dieses Statement vorzubereiten. In der Schleife setzt du nur noch die Parameter. Das bringt dann einiges an Speed.

[Franzelchen]

Zitat von himitsu:

Das ZQuery1.SQL.Text := 'INSERT INTO TestTabelle (Wert1, Wert2) VALUES(:v1, :v2)'; reicht doch einmal aus, also vor der Schleife.

?? Wenn ich das vor die Schleife setze, wie wird dann die Parameterliste befü...

richtig!! egal wo INSERT steht,ausgeführt wird es mit execsql.

Delphi-Quellcode:

ZQuery1.SQL.Text := 'INSERT INTO TestTabelle (Wert1, Wert2) VALUES (:v0, :v1), (:v2, :v3), (:v4, :v5), (:v6, :v7);';
...
with ZQuery1.Params do begin
  ParamValues[0] := SG1.Cells[0, i];
  ParamValues[1] := SG1.Cells[1, i];
  ParamValues[2] := SG1.Cells[0, i + 1];
  ParamValues[3] := SG1.Cells[1, i + 1];
  ParamValues[4] := SG1.Cells[0, i + 2];
  ParamValues[5] := SG1.Cells[1, i + 2];
  ParamValues[6] := SG1.Cells[0, i + 3];
  ParamValues[7] := SG1.Cells[1, i + 3];
end;
ZQuery1.ExecSQL;

Genau das ist meines Verständnisses nach Bulk insert?
Packe eine Reihe Parameter in einen Bulk (=Paket) und überführe mit einem Insert das Paket.

Zitat von p80286:

Du schleppst 35.000 datensätze in einem Stringgrid mit Dir herum?
Wie wäre es dafür eine Stringlist oder eine List zu nehmen?
Ein Stringgrid ist ja eigentlich für die Datenanzeige zuständig, nicht für die Datenhaltung. Da sollte noch etwas für Dich drin sein.

Gruß
K-H

Die Textdatei ist Tabulator getrennt. Komma, Punkt, Semikolon usw. sind Schriftzeichen innerhalb des Textes.

Stringlist hätte ich auch gern genommen, aber meine Programmierkenntnisse reichen dafür nicht aus.

Zitat von ConstantGardener:

...ausserdem solltest Du den Query Text vor dem For setzen und mit Prepare der Datenbank die Chance geben dieses Statement vorzubereiten. In der Schleife setzt du nur noch die Parameter. Das bringt dann einiges an Speed.

Prepare ist bereits im Quelltext von Zeos eingebaut.

[hoika]

Hallo,

hat die Testtabelle irgendwelche Indizes ?
Die könnte man vor dem Insert deaktivieren.

Mit dem ZEOS und Prepare ist in der Tat so,
er prepared selber, allerdings darf sich der SQL-Text
nicht ändern, was hier durch Nutzung von Parametern
ja auch gut gemacht wird.


Heiko

[Franzelchen]

Den ZQuery.text vor die Schleife zu ziehen, statt in der Schleife zu haben, wie in Post 1 bringt sage und schreibe rund 30 sec.

Jetzt habe ich die Parameterliste eingefügt und es passiert absolut nichts. Zeitmessung zeigt 0,00

Wo ist der Fehler?

Delphi-Quellcode:

var   i:Integer;
var   startzeit,stopzeit : TDateTime;

begin
startzeit := now;
Screen.Cursor := crHourGlass;
ZConnection.Database := 'c:\msql\firedb.fdb';
 Zquery1.SQL.Text:='INSERT INTO TestTabelle (Wert1, Wert2) VALUES(:v1, :v2), (:v3, :v4), (:v5, :v6), (:v7, :v8), (:v9, :v10)';
 while i <= SG1.RowCount-1  do
  begin
       with ZQuery1.params do
       begin
        ParamValues['v1']:=SG1.Cells[0, i];
        ParamValues['v2']:=SG1.Cells[1, i];
        ParamValues['v3'] := SG1.Cells[0, i + 1];
        ParamValues['v4'] := SG1.Cells[1, i + 1];
        ParamValues['v5'] := SG1.Cells[0, i + 2];
        ParamValues['v6'] := SG1.Cells[1, i + 2];
        ParamValues['v7'] := SG1.Cells[0, i + 3];
        ParamValues['v8'] := SG1.Cells[1, i + 3];
        ParamValues['v9'] := SG1.Cells[0, i + 4];
        ParamValues['v10']:= SG1.Cells[1, i + 4];

      end; // with
  ZQuery1.ExecSQL;
   i := i+5;
  end; //  i
Screen.Cursor := crDefault;
stopzeit := now;
Panel1.Caption :='SuchZeit :  '+ FormatDateTime('nn:ss:zzz', StopZeit - StartZeit) ;
end;

Indizes existieren nicht,
Trigger (was auch immer das ist) auch nicht.

[himitsu]

Jupp, eine Schleife mit dem zusammengefassten Statement und größeren Schritten
Der Vorteil an dem indizierten Indize und nicht den Namentlichen wäre, daß man eine innere Schleife befüllen könnte und so z.B. 100 Werte zusammenfassen könnte.
(müßte man mal ausprobieren, welche Größe halbwegs optimal ist)

und zum Schluß nicht vergessen, daß da noch ein paar Zeilen übrigbleiben können.


Entweder man bastelt das Insert-Statement als String zusammen, für die Restlichen und vorher einmal für alle Teile
oder man läßt den Rest über ein einfaches Insert, mit je einem der Restwerte, übertragen.

[Furtbichler]

Viel schneller geht es, wenn Du die Daten in eine Textdatei packst und dann per bulk insert in deine DB kopierst. So sollte das in wenigen Sekunden erledigt sein.

Wenn Du unbedingt mit INSERT-Befehlen arbeiten musst, dann könnte es viel schneller sein, wenn Du dir das Skript zunächst in einer Stringliste zusammenbaust und dann als ein Befehl innerhalb einer Transaktion ausführen lässt.

Zitat von Franzelchen:

Wo ist der Fehler?

i wird nicht initialisiert