Insert optimieren

[PhilmacFLy]

Moin Dp
Ich hab folgendes Problem, ich hab hier ein Array mit 4*30000 Werten, und muss dass möglichst schnell in die Firebird Datenbank einspeisen. Momentan brauch ich für die 30k werte ungefähr 30 Sekunden (übers Netzwerk), und da dachte ich mir das kann man bestimmt noch optimieren, hier mal mein Code:

Delphi-Quellcode:

  Frm_Messung.ZConnection1.StartTransaction;
  with Frm_Messung.ZQuery2 do
    begin
      saving := true;
      for zt := 0 to round(Frm_Messung.Ch_Messung.BottomAxis.Maximum) do
        begin
          Params[0].AsInteger := tId;
          Params[1].AsInteger := zt;
          Params[2].AsFloat := arr_messwerte[tdim, zt, 0];
          Params[3].AsFloat := arr_messwerte[tdim, zt, 1];
          ExecSQL;
          frm_messung.pb_db.Position := frm_messung.pb_db.Position +1;
        end;
    end;
  (*try
  Frm_Messung.ZQuery2.ExecSQL;
  except
  Frm_Messung.ZConnection1.Rollback;
  end;*)
  Frm_Messung.ZConnection1.Commit;

Im Zquery2.Sql steht:

Code:

INSERT INTO Messwerte (ID_mess, Millisec, Messwert_Links, Messwert_Rechts, Winkel, Merker) VALUES (:id, :sec, :mel, :mer, :win, :mek);

wobei ich die Parameter alle im Query selber schon eingetragen hab.
Die Parameter win und mek sind noch nicht belegt kommt aber später noch.

Nun wäre halt die frage wo ich noch das eine oder andere Rädchen drehen könnte um das zu optimieren.

[Bernhard Geyer]

Normalerweise muss man am Anfang ein Prepare aufrufen damit auch wirklich das prepared statement verwendet wird. Evtl. macht das ZEOS automatisch.

Sonst könntest du versuchen mehrer inserts in einem ExecSQL abschicken (BULK INSERT).

[PhilmacFLy]

Ich hab versucht erst nach dem for to das ExecSQL abzuschicken, dann wird _nur_ der letzte Wert eingetragen.

Wie ruf ich den das Prepare auf?

[Bernhard Geyer]

normalerweise gibts ein Property Prepared das du auf True setzt oder eine entsprechende Methode.
Aber ich weis nicht wie das Zeos bereit stellt.

Beim MS SQL Server würde ich sagen: Schau doch mal im Profiler nach was auf der DB ankommt.

[PhilmacFLy]

Also eine Option prepared hab ich nicht gefunden, weder in der Connection noch im query

[DeddyH]

Klappt denn dies?

Delphi-Quellcode:

Frm_Messung.ZQuery2.Prepare;

[PhilmacFLy]

Nein es kommt die typische Meldung, TZQuery enthält kein Element namens Prepare.

[shmia]

Es gibt folgende "Tricks" für schnellere Masseninsert:
1.) INSERT-Anweisung mit mehreren Zeilen

SQL-Code:

INSERT INTO Tabelle(KFZ, leistung)
VALUES('BMW M3', 321.0)
VALUES('VW Golf5 TDI', 120.0)
VALUES('Audi A3 TDI', 120.0)
...

Somit kann man auf einen Rutsch einige Hundert bis Tausend Datensätze einfügen.
Der Vorteil ergibt sich auch dadurch, dass nicht jeder einzelne Datensatz
einen TCP/IP Roundtrip erfordert.
Leider verstehen nur wenige Datenbanken diese erweiterte Syntax.

2.) viele Insert-Anweisungen (getrennt durch ;) auf einen Rutsch an den Server senden.
Dazu werden die Insert-Anweisung in einer Stringliste gesammelt
und als ein Gesamtstring an die ExecSQL() Funktion weitergegeben.
Auch hier hängt es davon ab, ob die Datenbank diesen "Batch-Betrieb" akzeptiert.

In beiden Fällen müssen die Insert-Anweisung ohne Unterstützung durch Parameter erzeugt werden.
Aber die höhere Einfügegeschw. sollte diesen Nachteil aufwiegen.

[exilant]

Zitat:

Zitat von PhilmacFLy (Beitrag 1050048)

... Momentan brauch ich für die 30k werte ungefähr 30 Sekunden (übers Netzwerk), und da dachte ich mir das kann man bestimmt noch optimieren,

Nun, das sind immerhin 1.000 Inserts/Sek. An dem Code kannst Du IMO nichts optimieren. Transaktion starten - inserts - Commit/Rollback. Was anderes kannst Du da nicht machen.
Auf was für einer Maschine läuft der Firebird? Welche Auslastung erzeugt er bei den inserts? Welche Netzlast liegt vor? Hat die Tabelle Trigger und wenn ja was tun die? Hat die Tabelle Indexe und wenn ja wie komplex sind die? Falls der FB auf einer Windows Maschine läuft: Lasse den Job doch mal auf der gleichen Maschine laufen.

[PhilmacFLy]

Also ich hab jetzt mal den Code so abgeändert:

Delphi-Quellcode:

 Frm_Messung.ZConnection1.StartTransaction;
  with Frm_Messung.ZQuery2 do
    begin
      saving := true;
      //list.Text := 'execute block as begin';
      for zt := 0 to round(Frm_Messung.Ch_Messung.BottomAxis.Maximum) do
        begin
          (*Params[0].AsInteger := tId;
          Params[1].AsInteger := zt;
          Params[2].AsFloat := arr_messwerte[tdim, zt, 0];
          Params[3].AsFloat := arr_messwerte[tdim, zt, 1];
          ExecSQL;*)
          list.Add('INSERT INTO Messwerte (ID_mess, Millisec, Messwert_Links, Messwert_Rechts) VALUES (' + InttoStr(tid) + ', ' + InttoStr(zt) +', ' + FloattoStr(arr_messwerte[tdim, zt, 0]) + ', '+ FloattoStr(arr_messwerte[tdim, zt, 1]) + ' );');
          frm_messung.pb_db.Position := frm_messung.pb_db.Position +1;
        end;
      //list.Add('end');
      SQL := list;
    end;
  try
  Frm_Messung.ZQuery2.ExecSQL;
  except
  Frm_Messung.ZConnection1.Rollback;
  end;
  Frm_Messung.ZConnection1.Commit;

Dann bekomm ich immer

Code:

Unknown Token in Line 2 Insert

@exilant
Der Firebird läuft auf einem WIN 2003 SBS, keine trigger keine indizes, und wenn ich die ganze Sache Embedded mach gehts etwas schneller so cs 15-20 sekunden für 30k

Edit:
Hab das grad gefunden
http://codicesoftware.blogspot.com/2...registers.html
der schafft in 19s 100k, aber ich versteh nicht ganz was er macht?

[Bernhard Geyer]

Zitat:

Zitat von shmia (Beitrag 1050080)

Leider verstehen nur wenige Datenbanken diese erweiterte Syntax.

Also MS SQL, MySQL und oracle verstehen das - halt mit angepaßter Syntax

Zitat:

Zitat von shmia (Beitrag 1050080)

In beiden Fällen müssen die Insert-Anweisung ohne Unterstützung durch Parameter erzeugt werden.

Nein, müssen nicht. Auch hier können Parameter verwendet werden. Mann sollte jedoch aufpassen das man die maximale Parameteranzahl der DB nicht überschreitet.

[mkinzler]

Eine paramterisierte Variante sollte aber der erweiterten Syntax in nichts nachstehen

[PhilmacFLy]

Naja im moment würde es mir ja schon helfen wenn ich das Problem mit dem token in den Griff bekommen würde :D

[Bernhard Geyer]

Zitat:

Zitat von PhilmacFLy (Beitrag 1050103)

Naja im moment würde es mir ja schon helfen wenn ich das Problem mit dem token in den Griff bekommen würde :D

Schau dir mal das Beispiel genau an.

Dort gibt es 1* die INSERT-Anweisung und x* die Values-Anweisungen.
Du baust jeweils komplett die INSERT + Values zusammen.

Ach ja: Wenn du werte direkt im String verwendest: Quotion nicht vergessen! SQL-Injection lassen grüßen!

[PhilmacFLy]

Selbes Problem nur diesmal:

Unkown toke line 3 column 1, VALUES

[Bernhard Geyer]

Zitat:

Zitat von PhilmacFLy (Beitrag 1050106)

Selbes Problem nur diesmal:

Unkown toke line 3 column 1, VALUES

Selber Tipp: Schau mal ganz genau hin.

Im Beispiel: Wie oft siehst du da das abschließende ";"?
Und wie oft ist es in deinem zusammen gebauten SQL-String?

[PhilmacFLy]

Im Beispiel von shima is es garnicht drin, und ich habs bei mir im neuen code auch rausgenommen.

Edit:

Delphi-Quellcode:

list.Add('INSERT INTO Messwerte (ID_mess, Millisec, Messwert_Links, Messwert_Rechts)');
      for zt := 0 to round(Frm_Messung.Ch_Messung.BottomAxis.Maximum) do
        begin
          (*Params[0].AsInteger := tId;
          Params[1].AsInteger := zt;
          Params[2].AsFloat := arr_messwerte[tdim, zt, 0];
          Params[3].AsFloat := arr_messwerte[tdim, zt, 1];
          ExecSQL;*)
          List.Add(' VALUES (' + InttoStr(tid) + ', ' + InttoStr(zt) +', ' + FloattoStr(arr_messwerte[tdim, zt, 0]) + ', '+ FloattoStr(arr_messwerte[tdim, zt, 1]) + ' )');
          frm_messung.pb_db.Position := frm_messung.pb_db.Position +1;
        end;

[DelphiBandit]

Habe mir die Beispiele gerade mal angesehen - das dritte und Schnellste wird unter FB nicht funktionieren, da er keine Multi-Valueliste unterstützt

Zitat:

Unfortunately neither SQLServer nor Firebird support multiple rows in the values part of an insert. I know they can use some sort of union clause to do something similar, but performance is not better.So, let’s try with MySql

Und von den beiden davor aufgezählten ist das mit den Parametern (das mittlere) schneller, als wie jetzt mit Einzel-SQL's zu arbeiten.

Hab @work leider keine Zeos installiert, aber müsste man für die Konstrukte mit mehreren SQL-Statements nicht so eine Art Script/Batch-SQL hernehmen?

Zwei Sachen die mir noch einfallen, um es ggf. ein wenig zu beschleunigen
1) Eine Stored-Procedure innerhalb der Datenbank anlegen und deren Parameter füllen und dann diese ausführen. So kann man sich garantiert drauf verlassen, dass die in jedem Fall prepared ist.
2) Externe Text-Tabelle anlegen und die Daten dort hinein inserten, die hält der Server bei 30.000 Werten bestimmt noch im Cache. Anschliessend auf die richtige Tabelle einen einzelnen INSERT mit VALUES (SELECT FROM TextTabelle) absetzen. Wenn man die externe Tabelle dann noch auf eine RAM-Disk legt, kriegt der FB-Prozess mal richtig Last.

[hoika]

Hallo,

die Sache mit der externen Tabellen wollte ich auch gerade vorschlagen ...

Aber noch mal zum Prepare.
Das macht ZEOS selber (musste mich mal mit den Quellen rumschlagen).


Heiko

[mkinzler]

FireBird prepared zudem beim 1. Zugriff auf eine parametrisierte Abfrage