Optimierung durch Parameter und Prepared statements

[idefix2]

In allen Foren wird immmer wieder darauf hingewiesen, dass man in SQL Befehlen aus Performancegründen 1. die Werte als Parameter übergeben und 2. bei wiederkehrenden Operationen prepared statements verwenden sollte, nirgend war aber konkret davon die Rede, um wiviel der Zugriff beschleunigt wird.

Ich habe jetzt eine einfache Testapplikation zum Benchmarken verwendet - Datenimport aus einer (tab-delimited) Textdatei mit ca 120000 Zeilen nach Firebird.
Jede Zeile wird eingelesen, mittels Zuweisung an Tstringlist.delimitedtext in die diversen Felder aufgeteilt und dann werden mittels drei insert or update Statements drei Hilfstabellen gefüllt (wenn der entsprechende Wert schon vorher vorgekommen ist, erfolgt in diese Tabellen kein neuerliches Insert) und mittels insert die Haupttabelle gefüllt, insgesamt ca 160000 inserts. Für jedes insert wird zusätzlich über eine Triggerroutine in einer 5. Tabelle noch ein datensatz erzeugt.

Variante 1 erzeugt mit Hilfe des + Operators direkte SQL Befehle, die mittels execute immediate ausgeführt werden.
Variante 2 verwendet vier Stringkonstante mit ? für die Variablen für die SQL Befehle, Variable werden in einem Parameterfeld übergeben, Die Befehle werden in der Schleife mit Execute immediate ausgeführt.
Variante 3 macht vor der Schleife ein Prepare für alle vier Befehle, in der Schleife werden nur die Parameter gesetzt und die vorbereiteten Statements ausgeführt.

Zu meiner Überraschung waren die Varianten 1 und 2 exakt gleich schnell:
Variante 1: 8:22 min.
Variante 2: 8:19 min.
Und das, obwohl in meinem eigenen Programm Variante 1 zusätzlichen Aufwand bedingt (Überprüfung aller Stringfelder auf das Zeichen Hochkomma und, falls das Zeichen vorkommt, verdoppeln).

Die Variante mit einmaligen Prepare und wiederkehrendem Ausführen der prepared statements war deutlich schneller, nämlich knapp unter 6 Minuten.

Mein Schluss daraus: Bei sehr umfangreichen Schleifen sind prepared Statements in Verbindung mit Parametern sinnvoll, aber sonst zahlt sich der Mehraufwand aber kaum aus, wenn man keine SQL-Injections befürchten muss. Nur Parameter zu verwenden anstatt direkter SQL Statements bringt überhaupt keinen Performancegewinn.

[mkinzler]

Zeig mal deinen Testcode

[idefix2]

Hier sind die drei Prozeduren.

Delphi-Quellcode:

procedure TMainForm.Button1Click(Sender: TObject);
// execimmediate mit Parametern
// 0 Satz   1 Titel   2 Interpret   3 Album   4 Startzeit   5 FadeIn 6 Spielzeit 7 FadeOut
// 8 Endzeit 9 TrackNummer 10   AufnahmeDatum   11 BasisVerzeichnis   12 Dateiname

const
s1 = 'update or insert into interpret (name) values (?) matching (name) returning id;';
s2 = 'update or insert into album (name) values (?) matching (name) returning id;';
s3 = 'update or insert into basisverzeichnis (verzeichnis) values (?) matching (verzeichnis) returning id;';
s4 = 'insert into musik (Titel, Ip_id, album_id, Startzeit, FadeIn, Spielzeit, FadeOut, Endzeit, '
    +'TrackNummer, AufnahmeDatum, BV_id, Filename) values (?,?,?,?,?,?,?,?,?,?,?,?) ';

var
linecount,v: integer;
f: TextFile;
s: string;
Felder: TStringList;

DB: Pointer;
Trans: Pointer;

ipnr,albnr,vznr: integer;

par, par2, erg: TSQLParams;
q1, q2, q3, q4: Pointer;    // statement handle

 procedure settrigger (const t: string; active: boolean);
   const s: array[boolean] of string = (' inactive;', ' active;');
   begin
   FBExec('alter trigger ' + t + s[active]);
   end;

 function zeit (x: integer): integer;
 begin
 zeit := 60*StrToInt(copy(Felder.strings[x],1,2))
           +StrToInt(copy(Felder.strings[x],4,2))
 end;

begin
assignfile (f, 'd:\musik.txt');
reset (f);
linecount := 0;

par := TSQLParams.Create (csWIN1250);
par2 := TSQLParams.Create (csWIN1250);
erg := TSQLparams.Create (csWIN1250);

par.AddFieldType ('Name', uftVarchar);
erg.AddFieldType ('Id', uftInteger);

par2.AddFieldType ('Titel', uftVarchar);
par2.AddFieldType ('Ip_Id', uftInteger);
par2.AddFieldType ('Album_Id', uftInteger);
par2.AddFieldType ('Startzeit', uftInteger);
par2.AddFieldType ('FadeIn', uftInteger);
par2.AddFieldType ('Spielzeit', uftInteger);
par2.AddFieldType ('Fadeout', uftInteger);
par2.AddFieldType ('Endzeit', uftInteger);
par2.AddFieldType ('Tracknummer', uftInteger);
par2.AddFieldType ('Aufnahmedatum', uftVarchar);
par2.AddFieldType ('BV_id', uftInteger);
par2.AddFieldType ('Filename', uftVarchar);

Felder := TStringList.Create;
Felder.Delimiter := #9;
Felder.StrictDelimiter := true;

//Updatetrigger deaktivieren
settrigger('Albumtrigger2',false);
settrigger('Interprettrigger2',false);
settrigger('Basisverzeichnistrigger2',false);
readln (f); // erste Zeile überspringen
z := now;
repeat readln (f,s); inc (linecount);
  try statusbar.simpletext := IntToStr (linecount);

    felder.DelimitedText := s;

    par.AsString[0] := Felder.strings[2];
    z1:=now;
    FBExec (s1, par, erg);
    ipnr := erg.AsInteger[0];

    par.AsString[0] := Felder.strings[3];
    FBExec (s2, par, erg);
    albnr := erg.AsInteger[0];

    par.AsString[0] := Felder.strings[11];
    FBExec(s3, par, erg);
    vznr := erg.AsInteger[0];

    par2.AsString[0] := Felder.strings[1];
    par2.AsInteger[1] := ipnr;
    par2.AsInteger[2] := albnr;
    par2.AsInteger[3] := zeit (4);
    par2.AsInteger[4] := zeit (5);
    par2.AsInteger[5] := zeit (6);
    par2.AsInteger[6] := zeit (7);
    par2.AsInteger[7] := zeit (8);
    if tryStrToInt (Felder.strings[9],v)
    then par2.Asinteger[8] := v
    else par2.AsInteger[8] := 0;
    par2.AsString[9] := Felder.strings[10];
    par2.AsInteger[10] := vznr;
    par2.AsString[11] := Felder.strings[12];
    FBExec (s4, par2);
    except messagedlg('Fehler in Zeile: '+s, mterror, [mbok], 0);
    end (* try *);
  if (linecount mod 500 = 0)
  then begin FBCommit;
       memo1.lines.add (inttostr(linecount)+': '+Timetostr(now-z));
       memo1.lines.add (Felder.strings[1]);
       end;
  Application.Processmessages;
  until eof(f);

if (linecount mod 500 <> 0)
then begin memo1.lines.add (inttostr(linecount)+': '+Timetostr(now-z));
     memo1.lines.add (Felder.strings[1]);
     end;

settrigger('Albumtrigger2',true);
settrigger('Interprettrigger2',true);
settrigger('Basisverzeichnistrigger2',true);
FBCommit;
z := now - z;
Button1.Caption := TimeToStr(z);
FBDetachDB;
end;


procedure TMainForm.Button2Click(Sender: TObject);
// prepared statements
// 0 Satz   1 Titel   2 Interpret   3 Album   4 Startzeit   5 FadeIn 6 Spielzeit 7 FadeOut
// 8 Endzeit 9 TrackNummer 10   AufnahmeDatum   11 BasisVerzeichnis   12 Dateiname

const
s1 = 'update or insert into interpret (name) values (?) matching (name) returning id;';
s2 = 'update or insert into album (name) values (?) matching (name) returning id;';
s3 = 'update or insert into basisverzeichnis (verzeichnis) values (?) matching (verzeichnis) returning id;';
//s4 = 'update or insert into musik (Titel, Ip_id, album_id, Startzeit, FadeIn, Spielzeit, FadeOut, Endzeit, '
//    +'TrackNummer, AufnahmeDatum, BV_id, Filename) values (?,?,?,?,?,?,?,?,?,?,?,?) '
//    +'matching (Titel, BV_id, Filename)';
s4 = 'insert into musik (Titel, Ip_id, album_id, Startzeit, FadeIn, Spielzeit, FadeOut, Endzeit, '
    +'TrackNummer, AufnahmeDatum, BV_id, Filename) values (?,?,?,?,?,?,?,?,?,?,?,?) ';

var
linecount,v: integer;
f: TextFile;
s: string;
Felder: TStringList;

ipnr,albnr,vznr: integer;

par, par2: TSQLParams;
q1, q2, q3, q4: Pointer;    // statement handle
erg: TSQLResult;

 procedure settrigger (const t: string; active: boolean);
   const s: array[boolean] of string = (' inactive;', ' active;');
   begin
   FBExec('alter trigger ' + t + s[active]);
   end;

 function zeit (x: integer): integer;
 begin
 zeit := 60*StrToInt(copy(Felder.strings[x],1,2))
           +StrToInt(copy(Felder.strings[x],4,2))
 end;

begin
assignfile (f, 'd:\musik.txt');
reset (f);
linecount := 0;

par := TSQLParams.Create (csWIN1250);
par2 := TSQLParams.Create (csWIN1250);
erg := TSQLResult.Create (csWIN1250);

par.AddFieldType ('Name', uftVarchar);

par2.AddFieldType ('Titel', uftVarchar);
par2.AddFieldType ('Ip_Id', uftInteger);
par2.AddFieldType ('Album_Id', uftInteger);
par2.AddFieldType ('Startzeit', uftInteger);
par2.AddFieldType ('FadeIn', uftInteger);
par2.AddFieldType ('Spielzeit', uftInteger);
par2.AddFieldType ('Fadeout', uftInteger);
par2.AddFieldType ('Endzeit', uftInteger);
par2.AddFieldType ('Tracknummer', uftInteger);
par2.AddFieldType ('Aufnahmedatum', uftVarchar);
par2.AddFieldType ('BV_id', uftInteger);
par2.AddFieldType ('Filename', uftVarchar);

Felder := TStringList.Create;
Felder.Delimiter := #9;
Felder.StrictDelimiter := true;

//Updatetrigger deaktivieren
settrigger('Albumtrigger2',false);
settrigger('Interprettrigger2',false);
settrigger('Basisverzeichnistrigger2',false);

FBPrepare(q1, s1, erg);
FBPrepare(q2, s2, erg);
FBPrepare(q3, s3, erg);
FBPrepare(q4, s4);

readln (f); // erste Zeile überspringen
z := now;
repeat  readln (f,s); inc (linecount);

  try
    statusbar.simpletext := IntToStr (linecount);
    felder.DelimitedText := s;

    par.AsString[0] := Felder.strings[2];
    z1:=now;
    FBExec(q1, par, erg);
    ipnr := erg.AsInteger[0];

    par.AsString[0] := Felder.strings[3];
    FBExec(q2, par, erg);
    albnr := erg.AsInteger[0];

    par.AsString[0] := Felder.strings[11];
    FBExec(q3, par, erg);
    vznr := erg.AsInteger[0];

    par2.AsString[0] := Felder.strings[1];
    par2.AsInteger[1] := ipnr;
    par2.AsInteger[2] := albnr;
    par2.AsInteger[3] := zeit (4);
    par2.AsInteger[4] := zeit (5);
    par2.AsInteger[5] := zeit (6);
    par2.AsInteger[6] := zeit (7);
    par2.AsInteger[7] := zeit (8);
    if tryStrToInt (Felder.strings[9],v)
    then par2.Asinteger[8] := v
    else par2.AsInteger[8] := 0;
    par2.AsString[9] := Felder.strings[10];
    par2.AsInteger[10] := vznr;
    par2.AsString[11] := Felder.strings[12];
    FBExec(q4, par2);
    if (linecount mod 500 = 0)
    then begin FBCommit;
         memo1.lines.add (inttostr(linecount)+': '+Timetostr(now-z));
         memo1.lines.add (Felder.strings[1]);
         end;
    except messagedlg('Fehler bei '+s,mterror, [mbok], 0);
    end;
  Application.Processmessages;
  until eof(f);

if (linecount mod 500 <> 0)
then begin memo1.lines.add (inttostr(linecount)+': '+Timetostr(now-z));
     memo1.lines.add (Felder.strings[1]);
     end;

settrigger('Albumtrigger2',true);
settrigger('Interprettrigger2',true);
settrigger('Basisverzeichnistrigger2',true);
FBCommit;
z := now - z;
Button2.Caption := TimeToStr(z);
FBDetachDB;
end;

procedure TMainForm.Button3Click(Sender: TObject);
// execimmediate ohne Parameter
// 0 Satz   1 Titel   2 Interpret   3 Album   4 Startzeit   5 FadeIn 6 Spielzeit 7 FadeOut
// 8 Endzeit 9 TrackNummer 10   AufnahmeDatum   11 BasisVerzeichnis   12 Dateiname

var
linecount,v: integer;
f: TextFile;
s: string;
Felder: TStringList;

ipnr,albnr,vznr: string;

q1, q2, q3, q4: Pointer;    // statement handle
erg: TSQLParams;

 procedure settrigger (const t: string; active: boolean);
   const s: array[boolean] of string = (' inactive;', ' active;');
   begin
   FBExec('alter trigger ' + t + s[active]);
   end;

 function zeit (x: integer): string;
 begin
 zeit := IntToStr(60*StrToInt(copy(Felder.strings[x],1,2))
                    +StrToInt(copy(Felder.strings[x],4,2)))
 end;

 function quote(i: integer): string;
 begin Result := felder.strings[i];
 for i := length(Result) downto 1 do
     if Result[i]='''' then insert('''',result,i);
 end;

 const comma = ',';

begin
assignfile (f, 'd:\musik.txt');
reset (f);
linecount := 0;

erg := TSQLParams.Create (csWIN1250);
erg.AddFieldType ('Id', uftInteger);

Felder := TStringList.Create;
Felder.Delimiter := #9;
Felder.StrictDelimiter := true;

//Updatetrigger deaktivieren
settrigger('Albumtrigger2',false);
settrigger('Interprettrigger2',false);
settrigger('Basisverzeichnistrigger2',false);

readln (f); // erste Zeile überspringen
z := now;
repeat  readln (f,s); inc (linecount);

  try
    statusbar.simpletext := IntToStr (linecount);
    felder.DelimitedText := s;

    z1:=now;
    s := 'update or insert into interpret (name) values (''' + quote(2)
         + ''') matching (name) returning id;';
    FBExec(s, nil, erg);
    ipnr := erg.AsString[0];

    FBExec('update or insert into album (name) values (''' + quote(3)
         + ''') matching (name) returning id;', nil, erg);
    albnr := erg.AsString[0];

    FBExec('update or insert into basisverzeichnis (verzeichnis) values (''' + quote(11)
         + ''') matching (verzeichnis) returning id;', nil, erg);
    vznr := erg.AsString[0];

    s := Felder.Strings[9];
    if not tryStrToInt (Felder.strings[9],v) then s := '0';

    FBExec('insert into musik (Titel, Ip_id, album_id, Startzeit, FadeIn, Spielzeit, FadeOut, Endzeit, '
    +'TrackNummer, AufnahmeDatum, BV_id, Filename) values ('''+quote(1)+''','+ipnr+comma+albnr+comma
    +zeit(4)+comma+zeit(5)+comma+zeit(6)+comma+zeit(7)+comma+zeit(8)+comma+s+','''+quote(10)+''','
    +vznr+','''+quote(12)+''');');

    if (linecount mod 500 = 0)
    then begin FBCommit;
         memo1.lines.add (inttostr(linecount)+': '+Timetostr(now-z));
         memo1.lines.add (Felder.strings[1]);
         end;
    except messagedlg('Fehler bei '+s,mterror, [mbok], 0);
    end;
  Application.Processmessages;
  until eof(f);

if (linecount mod 500 <> 0)
then begin memo1.lines.add (inttostr(linecount)+': '+Timetostr(now-z));
     memo1.lines.add (Felder.strings[1]);
     end;

settrigger('Albumtrigger2',true);
settrigger('Interprettrigger2',true);
settrigger('Basisverzeichnistrigger2',true);
FBCommit;
z := now - z;
Button3.Caption := TimeToStr(z);
FBDetachDB;
end;

[hoika]

Hallo,

du hast aber auch etwas vergessen.

1. Das Auslesen aus der Text-Datei kostet auch Zeit.
Wieviel % der Gesamtzeit, ist die Frage.
2. Memo.Lines.Add -> dito
3. Application.ProcessMessages gehört in das if (mod 500)
ansonsten wird es ja bei jedme der 100.000 Loops ausgeführt.

Hat aber mit dem Vergleich nichts zu tun.

4. Hast du den Rechner auch nach jedem Test neu gestartet und hast
die DB neu erzeugt ?


Eine direkte Audsage, um wieviel schneller FB mit prepared statements ist,
wirst du nirgends finden. Das kommt doch auch darauf an.

1. Wie komplex ist die Abfrage ?
2. Wie oft wird sie nach dem prepared ausgeführt.
3. ...


Bei meinen Anwendungen (10/20er Schleifen war es etwa 50% schneller),
auserdem belastet es bei mehreren Usern den Server nicht so sehr.

Heiko

[dataspider]

IMHO ist es ja auch gerade das Prepare, welches Zeit kostet. Denn beim Prepare muss der Optimizer aus dem Statement den Zugriffs - Plan erstellen.
Das heisst, dass z.B. bei Imports erheblich optimiert werden kann.
Nämlich dann, wenn ich die Prepare' s auf 1 reduzieren kann.
Das ist der Fall, wenn ich eine Stored Procedure mit Parametern oder eine Query mit Parametern benutze.

Parameter neu setzen - Ausführen - kein erneutes Prepare notwendig...

Query - neues SQL Statement zuweisen - Ausführen - Prepare erzwungen!

Ich nutze momentan in FB für Importe nur noch Stored Procedures mit Parametern.

Frank

[schlecki]

Zitat:

Zitat von idefix2 (Beitrag 1029160)

Mein Schluss daraus: Bei sehr umfangreichen Schleifen sind prepared Statements in Verbindung mit Parametern sinnvoll, aber sonst zahlt sich der Mehraufwand aber kaum aus, wenn man keine SQL-Injections befürchten muss. Nur Parameter zu verwenden anstatt direkter SQL Statements bringt überhaupt keinen Performancegewinn.

Nochmal ein anderer Aspekt: Ich weiß, das es einige DBMS gibt, die die Statements cachen und wiederverwenden. Das bedeutet, wenn du Parameter verwendest, erkennt der Server das als gleiches Statemenent und kann auf die bereits erstellten Zugriffspläne zurückgreifen. Er muß hier also nicht mehr tätig werden und diese neue berechnen. In größeren Datenbanken, die von mehreren Benutzern angesprochen werden, kann das doch einiges ausmachen. Auf jeden Fall nutzt Oracle ein solches Konstrukt, beim FB bin ich mir nicht ganz sicher.

[mkinzler]

Auch bei FireBird ist das so, wie oben auch schon öfters erwähnt wurde

[Bernhard Geyer]

Zitat:

Zitat von schlecki (Beitrag 1029177)

Nochmal ein anderer Aspekt: Ich weiß, das es einige DBMS gibt, die die Statements cachen und wiederverwenden. ... Auf jeden Fall nutzt Oracle ein solches Konstrukt

Wirklich? Haben früher sowas nicht bemerkt.
Wir selbst verwenden einen clientseitigen (selbst implementierten) Query Cache um die letzten 20 Statements zu cachen. Damit erreichen wird das i.d.R. 95% der Abfragen schon prepared Statements verwenden können.

[hoika]

Hallo,

minzler, was schlecki meint, ist, dass das Oracle automatisch macht,
d.h. ich erzeuge eine Query mit Parametern, benutze sie und gebe sie frei.
Benutzt jetzt eine andere Anwendung genau den gleichen Query-SQL-Text,
hat Oracle den Ausführungsplan vielleicht noch in seinen Query-Cache (auf dem Server slebst) und benutzt ihn.

D.h. die Anwendung muss gar nichts tun (halt Parameter verwenden ;) ).

FB kann das AFAIK noch nicht.


Heiko

[mkinzler]

Bei FB kommt es hierbei darauf an, welche Version verwendet wird. Bei Classic könnte es gehen

[idefix2]

Zitat:

Hast du den Rechner auch nach jedem Test neu gestartet und hast die DB neu erzeugt ?

Nein, sondern vor jedem Lauf die gleiche DB mit leeren Tabellen zurückkopiert :)
Den Rechner nach jedem Test habe ich nicht neu gestartet, aber jeden Test mehrfach ausgeführt, mit auf die Sekunde genau gleichem Ergebnis bei jedem Durchlauf, es hat also das, was vorher passiert ist, keinen Einfluss.

Ich habe mir jetzt noch das Zeitverhalten angeschaut, wenn ich aus meinen FBExec Prozeduren Dummy-Prozeduren mache (1. Befehl: Exit). Das Programm braucht ohne SQL Ausführung genau 22 Sekunden, und zwar in jeder Variante, zum Einlesen der Daten und Vorbereiten der Parameter bzw. des SQL Strings (inkl. der 100.000 Processmessages, die sind nur marginal am Ergebnis beteiligt). D.h. heisst, der Zeitaufwand nur für die eigentliche SQL Abarbeitung ist in allen Fällen um 22 Sekunden weniger.

Zitat:

Bei meinen Anwendungen (10/20er Schleifen war es etwa 50% schneller),
auserdem belastet es bei mehreren Usern den Server nicht so sehr.

Das verblüfft mich jetzt ein bisschen, weil ich habe eine einzige 100.000er Schleife und es wird nur ca 30% schneller. Allerdings kommt bei mir bei jedem Insert zusätzlich ein Insert-Trigger zum Tragen (nur die Update-Trigger habe ich bei dem Import deaktiviert), dadurch wird das, was nach dem Absetzen in der Datenbank passiert, im Verhältnis zu Delphiprogramm etwas komplexer, vielleicht deshalb der geringere Zeitgewinn. Weil natürlich ist auch bei 20er Schleifen der Zeitanteil, den das Prepare gegenüber dem Exec benötigt, schon sehr gering, ob 20 oder 100.000 Wiederholungen, macht also gar keinen so grossen Unterschied mehr.

Was mich an meinem Testergebnis aber vor allem überrascht hat, ist, dass ohne Prepare und wiederholtes Ausführen die direkte Datenübergabe im SQL String und die Datenübergabe via Parameter genau gleich schnell sind.

[idefix2]

Was ich jetzt noch festgestellt habe: die Wahl der "richtigen" Transaktionsgrösse ist ganz entscheidend für die Geschwindigkeit, und mein erster Ansatz (500 Zeilen pro Transaktion einlesen" war ziemlich weit daneben. Wenn ich statt nach 500 Zeilen erst nach 15000 Zeilen (ca 36000 Inserts) ein Commit mit anschliessendem neuen Transaktionsstart mache, braucht das Programm weniger als die Hälfte der Zeit, nämlich 2:51 in Verbindung mit prepared Statements statt knapp 6 Minuten. Ein weiteres Vergrössern der Transaktion verlangsamt allerdings das Programm wieder. Die optimale Transaktionsgrösse wird vermutlich von einer Menge Faktoren abhängen und nicht leicht zu bestimmen sein, sie ist aber jedenfalls sehr viel grösser, als ich gedacht hatte.

@mkinzler
Das Ergebnis des Tests legt nahe, dass Firebird keinen Statement-Cache verwaltet, um sich das wiederholte Ausführen von prepare zu sparen. In meiner Einleseschleife gibt es 4 SQL Statements, die der Reihe nach aufgerufen werden. Würde Firebird mit einem Statement Cache arbeiten, dann müsste die Variante mit execute immediate mit Parametern annähernd so schnell sein wie die mit manuellem Prepare, die 4 Statements wären ja dann nach der ersten importierten Zeile schon vorbereitet im Cache. Tatsächlich ist diese Variante aber ebenso langsam wie die Variante execute immediate ohne Parameter, bei der jedes Statement anders aussieht und ein Cache deshalb keinen Vorteil bringen würde.