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 von idefix2:

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 von schlecki:

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