Kleiner Geschwindigkeitstest von Insert

[Monday]

Hallo,

mich hat es interessiert, wie sich die Geschwindigkeiten von

Inserts bei SQLite3 und Zeos

verändern, mit unterschiedlichen "schreibweisen"; Und habe festgestellt, dass es durchaus erhebliche Unterschiede geben kann.

Ich habe den Test mehrmals durchlaufen, auch mit unterschiedlichen Zahlen und habe folgende Durchschnittszeiten bekommen:



Hier die sortierte Ergebnisse:

Code:

Benötigte Zeit %      Funktionsname
1,20 %                Test4 (Insert; in Transaction, außerhalb der Funktion)
1,21 %                Test3 (Insert; in Transaction innerhalb der Funktion)
97,58 %               TEst1 (Insert und exec... direkt hintereinander)

(Funktion 2 hat im Test nicht teilgenommen. Ich war der Annahme, es ist auch mögliche mehrere Inserts mit add zu schreiben, doch das ist so nicht möglich.)


Hier die verschiedene Abschnitte:


Derzeit aktuelle Tabelle im Beitrag:
http://www.delphipraxis.net/1356490-post5.html

Delphi-Quellcode:

function test1(testzahl:integer): string;
var
  a: integer;
begin
  for a := 1 to testzahl do begin
    Form1.ZQuery1.SQL.Text := 'Insert into daten2 (zahl) values (:zahl)';
    Form1.ZQuery1.ParamByName('zahl').AsInteger := a;
    Form1.ZQuery1.ExecSQL;
  end;
end;       



function test2(testzahl:integer): string;
var
  a: integer;
begin
  Form1.ZQuery1.SQL.Clear;
  for a := 1 to testzahl do begin
    Form1.ZQuery1.SQL.add('Insert into daten2 (zahl) values (:zahl);');
    Form1.ZQuery1.ParamByName('zahl').AsInteger := a;
  end;
   Form1.ZQuery1.ExecSQL;
end; 



function test3(testzahl:integer): string;
var
  a: integer;
begin
  Form1.ZQuery1.SQL.text := 'begin transaction;';
  Form1.ZQuery1.ExecSQL;

    Form1.ZQuery1.SQL.Text := '';
  for a := 1 to testzahl do begin
    Form1.ZQuery1.SQL.text := 'Insert into daten2 (zahl) values (:zahl);';
    Form1.ZQuery1.ParamByName('zahl').AsInteger := a;
      Form1.ZQuery1.ExecSQL;
  end;

  Form1.ZQuery1.SQL.text := 'Commit;';
  Form1.ZQuery1.ExecSQL;
end;  




function test4(testzahl:integer): string;
var
  a: integer;
begin
    Form1.ZQuery1.SQL.Text := '';
  for a := 1 to testzahl do begin
    Form1.ZQuery1.SQL.text := 'Insert into daten2 (zahl) values (:zahl);';
    Form1.ZQuery1.ParamByName('zahl').AsInteger := a;
      Form1.ZQuery1.ExecSQL;
  end;
end;             


//START der Funktionen:
  Form1.ZQuery1.SQL.Text := 'Delete from daten2';  Form1.ZQuery1.ExecSQL;
  test1(testzahl);

  Form1.ZQuery1.SQL.Text := 'Delete from daten2';  Form1.ZQuery1.ExecSQL;
  test3(testzahl);

  Form1.ZQuery1.SQL.Text := 'Delete from daten2';  Form1.ZQuery1.ExecSQL;
  Form1.ZQuery1.SQL.text := 'begin transaction;';  Form1.ZQuery1.ExecSQL;
  test4(testzahl);
  Form1.ZQuery1.SQL.text := 'Commit;';  Form1.ZQuery1.ExecSQL;

LG
Monda<

[nahpets]

Funktioniert test2, wenn Du die Funktion dahingehend änderst?

Delphi-Quellcode:

function test2(testzahl:integer): string;
var
  a: integer;
begin
  Form1.ZQuery1.SQL.Clear;
  for a := 1 to testzahl do begin
    Form1.ZQuery1.SQL.add(Format('Insert into daten2 (zahl) values (%d);',[a]));
  end;
   Form1.ZQuery1.ExecSQL;
end;

[mensch72]

wenn es dir echt um Geschwindigkeit geht, dann sind deine Insert Schleifen noch subobtimal

entweder einfach

Delphi-Quellcode:

for a := 1 to testzahl do begin
    Form1.ZQuery1.SQL.text := 'Insert into daten2 (zahl) values ('+IntToStr(a)+')';
    Form1.ZQuery1.ExecSQL;
end;

oder besser

Delphi-Quellcode:

Form1.ZQuery1.SQL.text := 'Insert into daten2 (zahl) values (:zahl);';

qPara:=Form1.ZQuery1.ParamByName('zahl');
for a := 1 to testzahl do begin
    qPara.AsInteger := a;
    Form1.ZQuery1.ExecSQL;
end;

ich würde wenn es was wie "PrepareSQL" bei Zeos gibt es nutzen, und Parameter nur 1xabfragen+"merken", sowie Query als Übergabe im Aufruf mitgeben(Form. ist immer unschön):

Delphi-Quellcode:

Query.SQL.text := 'begin transaction;';
Query.ExecSQL;

Query.SQL.text := 'Insert into daten2 (zahl) values (:zahl);';
Query.PrepareSQL;

qPara:=Query.ParamByName('zahl');
for a := 1 to testzahl do begin
    qPara.AsInteger := a;
    Query.ExecSQL;
end;

Query.SQL.text := 'Commit;';
Query.ExecSQL;

[bnreimer42]

Hallo,

vermutlich bekommst Du andere Ergebnisse, wenn Du die Reihenfolge der Tests veränderst.

Ich weiß nicht, wie SQLLITE das intern umsetzt, aber nach einem Commit des DELETE-Statements können in der Regel die selben Speicherbereiche für die neuen Inserts genutzt werden, andernfalls müssen Dateien vergrößert werden oder neue Blöcke vom OS angefordert werden, um die Datensätze unterzubringen. Das gilt insbesondere im Singleuser Betrieb.


Gruß

Björn

[Monday]

Hey Super für eure Anregungen!


@bnreimer42
Bzgl. der Reihenfolge der Tests: Ich habe die Reihenfolge geändert, und die Ergebnisse blieben die gleichen.

@nahpets
Ja Funktion 2 funktioniert mit deinem Vorschlag tatsächlich.


@mensch72
Gibt es Vor- Nachteile von .ParamByName()...? Ich habe mir einst angewöhnt so zu schreiben und nicht mehr direkt in die Zeile, weil es einen schwerwiegenden Grund hatte. Leider weiß ich nicht mehr was der Grund dafür war.



Nun nochmal mit den neuen Ergebnissen, im Vergleich:

Code:

Benötigte Zeit % |  Funktionsname

48,57                  Test2 (Insert mit .add(Format(... siehe auch Test5 )                  
17,62                  Test1 (Insert und exec... direkt hintereinander)
16,88                  Test8 (Wie Test7 + Prepare)
16,46                  Test7 (Nur ParamByName in Schleife)
0,22                   Test4 (Insert; in Transaction, außerhalb der Schleife)
0,21                   Test3 (Insert; in Transaction, innerhalb der Funktion)
0,03                   Test9 (wie 8 + Transaction)
0,01                   Test6 (Wie Test5 mit Transaction)
0,01                   Test5 (Direkt, Ohne .ParamByName.)



17,62                  Test1 (Insert und exec... direkt hintereinander)
48,57                  Test2 (Insert mit .add(Format(... siehe auch Test5 )                  
0,21                   Test3 (Insert; in Transaction, innerhalb der Funktion)
0,22                   Test4 (Insert; in Transaction, außerhalb der Schleife)
0,01                   Test5 (Direkt, Ohne .ParamByName.)
0,01                   Test6 (Wie Test5 mit Transaction)
16,46                  Test7 (Nur ParamByName in Schleife)
16,88                  Test8 (Wie Test7 + Prepare)
0,03                   Test9 (wie 8 + Transaction)

Und wieder der Code:

Delphi-Quellcode:

function test1(testzahl: integer): string;
var
  a: integer;
begin
  for a := 1 to testzahl do
  begin
    Form1.ZQuery1.SQL.Text := 'Insert into daten2 (zahl) values (:zahl)';
    Form1.ZQuery1.ParamByName('zahl').AsInteger := a;
    Form1.ZQuery1.ExecSQL;
  end;
end;



function test2(testzahl: integer): string;   //nahpets
var
  a: integer;
begin

  Form1.ZQuery1.SQL.Clear;
  for a := 1 to testzahl do
  begin
    Form1.ZQuery1.SQL.add(Format('Insert into daten2 (zahl) values (%d);', [a]));
  end;
  Form1.ZQuery1.ExecSQL;

end;


function test3(testzahl: integer): string;
var
  a: integer;
begin
  Form1.ZQuery1.SQL.Text := 'begin transaction;';
  Form1.ZQuery1.ExecSQL;

  Form1.ZQuery1.SQL.Text := '';
  for a := 1 to testzahl do
  begin
    Form1.ZQuery1.SQL.Text := 'Insert into daten2 (zahl) values (:zahl);';
    Form1.ZQuery1.ParamByName('zahl').AsInteger := a;
    Form1.ZQuery1.ExecSQL;
  end;


  Form1.ZQuery1.SQL.Text := 'Commit;';
  Form1.ZQuery1.ExecSQL;
end;




function test4(testzahl: integer): string;
var
  a: integer;
begin

  Form1.ZQuery1.SQL.Text := '';
  for a := 1 to testzahl do
  begin
    Form1.ZQuery1.SQL.Text := 'Insert into daten2 (zahl) values (:zahl);';
    Form1.ZQuery1.ParamByName('zahl').AsInteger := a;
    Form1.ZQuery1.ExecSQL;
  end;

end;



function test5(testzahl: integer): string;  //mensch72
var
  a: integer;
begin

  Form1.ZQuery1.SQL.Clear;
  for a := 1 to testzahl do
  begin
    Form1.ZQuery1.SQL.Text := 'Insert into daten2 (zahl) values (' + IntToStr(a) + ')';
  end;
  Form1.ZQuery1.ExecSQL;

end;


function test6(testzahl: integer): string;
var
  a: integer;
begin
   Form1.ZQuery1.SQL.Text := 'begin transaction;';
  Form1.ZQuery1.ExecSQL;

  Form1.ZQuery1.SQL.Clear;
  for a := 1 to testzahl do
  begin
    Form1.ZQuery1.SQL.Text := 'Insert into daten2 (zahl) values (' + IntToStr(a) + ')';
  end;
  Form1.ZQuery1.ExecSQL;

  Form1.ZQuery1.SQL.Text := 'Commit;';
  Form1.ZQuery1.ExecSQL;

end;



function test7(testzahl: integer): string;
var
  a: integer;
begin
  Form1.ZQuery1.SQL.text := 'Insert into daten2 (zahl) values (:zahl);';

  for a := 1 to testzahl do begin
      Form1.ZQuery1.ParamByName('zahl').AsInteger := a;
      Form1.ZQuery1.ExecSQL;
  end;

end;

function test8(testzahl: integer): string;
var
  a: integer;
begin
  Form1.ZQuery1.SQL.text := 'Insert into daten2 (zahl) values (:zahl);';
  Form1.ZQuery1.Prepare;

  for a := 1 to testzahl do begin
      Form1.ZQuery1.ParamByName('zahl').AsInteger := a;
      Form1.ZQuery1.ExecSQL;
  end;
end;



function test9(testzahl: integer): string;
var
  a: integer;
begin
  Form1.ZQuery1.SQL.Text := 'begin transaction;';
 Form1.ZQuery1.ExecSQL;

  Form1.ZQuery1.SQL.text := 'Insert into daten2 (zahl) values (:zahl);';
  Form1.ZQuery1.Prepare;

  for a := 1 to testzahl do begin
      Form1.ZQuery1.ParamByName('zahl').AsInteger := a;
      Form1.ZQuery1.ExecSQL;
  end;

  Form1.ZQuery1.SQL.Text := 'Commit;';
 Form1.ZQuery1.ExecSQL;
 
 
 
 //Starts....

  Form1.ZQuery1.SQL.Text := 'Delete from daten2';
  Form1.ZQuery1.ExecSQL;
  test1(testzahl);

  Form1.ZQuery1.SQL.Text := 'Delete from daten2';
  Form1.ZQuery1.ExecSQL;
  test2(testzahl);

  Form1.ZQuery1.SQL.Text := 'Delete from daten2';
  Form1.ZQuery1.ExecSQL;
  test3(testzahl);



  Form1.ZQuery1.SQL.Text := 'Delete from daten2';
  Form1.ZQuery1.ExecSQL;
  Form1.ZQuery1.SQL.Text := 'begin transaction;';
  Form1.ZQuery1.ExecSQL;
  test4(testzahl);
  Form1.ZQuery1.SQL.Text := 'Commit;';
  Form1.ZQuery1.ExecSQL;


  Form1.ZQuery1.SQL.Text := 'Delete from daten2';
  Form1.ZQuery1.ExecSQL;
  test5(testzahl);


  Form1.ZQuery1.SQL.Text := 'Delete from daten2';
  Form1.ZQuery1.ExecSQL;
  test6(testzahl);


  Form1.ZQuery1.SQL.Text := 'Delete from daten2';
  Form1.ZQuery1.ExecSQL;
  test7(testzahl);


  Form1.ZQuery1.SQL.Text := 'Delete from daten2';
  Form1.ZQuery1.ExecSQL;
  test8(testzahl);


  Form1.ZQuery1.SQL.Text := 'Delete from daten2';
  Form1.ZQuery1.ExecSQL;
  test9(testzahl);

[nahpets]

Hier muss testzahl mal geprüft werden, ob es den Parameter Zahl gibt. Das kostet Rechenzeit.

Delphi-Quellcode:

function test7(testzahl: integer): string;
var
  a: integer;
begin
  Form1.ZQuery1.SQL.text := 'Insert into daten2 (zahl) values (:zahl);';

  for a := 1 to testzahl do begin
      Form1.ZQuery1.ParamByName('zahl').AsInteger := a;
      Form1.ZQuery1.ExecSQL;
  end;

end;

Hier muss einmal geprüft werden, ob es den Parameter Zahl gibt.

Delphi-Quellcode:

function test7a(testzahl: integer): string;
var
  a: integer;
  p: TParam;
begin
  Form1.ZQuery1.SQL.text := 'Insert into daten2 (zahl) values (:zahl);';
  Form1.ZQuery1.Prepare;
  p := Form1.ZQuery1.ParamByName('Zahl');
  for a := 1 to testzahl do begin
    p.AsInteger := a;
    Form1.ZQuery1.ExecSQL;
  end;
end;

Ergibt das 'nen messbaren Unterschied?

Delphi-Quellcode:

function test3a(testzahl: integer): string;
var
  a: integer;
  p: TParam;
begin
  Form1.ZQuery1.SQL.Text := 'begin transaction;';
  Form1.ZQuery1.ExecSQL;
  Form1.ZQuery1.SQL.Text := 'Insert into daten2 (zahl) values (:zahl);';
  Form1.ZQuery1.Prepare;
  p := Form1.ZQuery1.ParamByName('Zahl');
  for a := 1 to testzahl do
  begin
    p.AsInteger := a;
    Form1.ZQuery1.ExecSQL;
  end;
  Form1.ZQuery1.SQL.Text := 'Commit;';
  Form1.ZQuery1.ExecSQL;
end;

Hier eventuell auch?

[Monday]

Ich habe nun verschiedenes getestet und sehe mir die Ergebnisse an:


Test2,1,8,7: Sind deutlich langsam. Sollte man so keinesfalls verwenden, wenn Geschwindigkeit ein Kriterium sein soll.
Nur ParamByName in der schleife (Test7) ergibt für sich keinen Geschwindigkeitsvorteil, auch nicht mit Prepare verbunden (Test8); Und ergibt nur Sinn, wenn man diese mit Transaction verbindet (Test9). Mit Format() (Test2) sollte man bzgl. Schnelligkeit eher vorsichtig sein, diese war sogar fast dreimal solangsam wie Test1 (Insert,Exec direkt hintereinander).


Test3,4: Insert mit Transactionen gehen bereits erheblich schneller und können/sollten verwendet werden. Dabei spielt es keine Rolle ob "begin transaction / commit) innerhalb der Funktion oder außerhalb ist.

Test9,6,5: Sind nochmal um ca. 0,2 % schneller als Test3 und 4. Variablen direkt in die SQL Anweisung zu schreiben (Test5), war das schnellste; Gleiches mit Transaction (Test6) ist sogar einen Tick langsamer und wird nicht wirklich benötigt.

[Monday]

@nahpets
Ah Ok, ich dachte das macht kein Unterschied. Ich kannte das so noch nicht; Ich werde es testen.

[mensch72]

na wenn jetzt das "Form" noch wegfällt, wären wir bei der Version, welche nach meinem Bauchgefühl am schnellsten und "am saubersten" wäre

Delphi-Quellcode:

function test3a(testzahl: integer): string;
var
  a: integer;
  p: TParam;
begin
  Form1.ZQuery1.SQL.Text := 'begin transaction;';
  Form1.ZQuery1.ExecSQL;
  Form1.ZQuery1.SQL.Text := 'Insert into daten2 (zahl) values (:zahl);';
  Form1.ZQuery1.Prepare;
  p := Form1.ZQuery1.ParamByName('Zahl');
  for a := 1 to testzahl do
  begin
    p.AsInteger := a;
    Form1.ZQuery1.ExecSQL;
  end;
  Form1.ZQuery1.SQL.Text := 'Commit;';
  Form1.ZQuery1.ExecSQL;
end;

[bnreimer42]

Hallo,

seid Ihr sicher, dass die Transaktionssteuerung so funktioniert?

Ich arbeite nicht mit ZEOS, aber ein schnelles googeln brachte zu Tage, dass wenn die Zeos-Connection AutoCommit=True eingestellt hat, dann macht sie das auch, d.h. nach jedem Statement wird ein Commit gemacht, auch nach dem begin transaction.

Über das Connection-Objekt kann man mit den Methoden StartTransaction, Commit und Rollback die Transaktionssteuerung "richtig" machen.

Leider hast Du nicht das ganze Projekt hier gepostet, somit musst Du es selber ausprobieren


Gruß