AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
• Registrieren
Zurück Delphi-PRAXiS Programmierung allgemein Datenbanken Optimierung durch Parameter und Prepared statements
Thema durchsuchen
Ansicht
Themen-Optionen

Optimierung durch Parameter und Prepared statements

Ein Thema von idefix2 · begonnen am 15. Jun 2010 · letzter Beitrag vom 17. Jun 2010
Antwort Antwort
mkinzler
(Moderator)

Registriert seit: 9. Dez 2005
Ort: Heilbronn
39.876 Beiträge
 
Delphi 11 Alexandria
 
#1

AW: Optimierung durch Parameter und Prepared statements

  Alt 15. Jun 2010, 23:56
Zeig mal deinen Testcode
Markus Kinzler
  Mit Zitat antworten Zitat
idefix2

Registriert seit: 17. Mär 2010
Ort: Wien
1.027 Beiträge
 
RAD-Studio 2009 Pro
 
#2

AW: Optimierung durch Parameter und Prepared statements

  Alt 16. Jun 2010, 00:32
Hier sind die drei Prozeduren.

zusammenfalten · markieren
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;
  Mit Zitat antworten Zitat
hoika

Registriert seit: 5. Jul 2006
Ort: Magdeburg
8.277 Beiträge
 
Delphi 10.4 Sydney
 
#3

AW: Optimierung durch Parameter und Prepared statements

  Alt 16. Jun 2010, 07:02
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
Heiko
Geändert von hoika (16. Jun 2010 um 07:06 Uhr)
  Mit Zitat antworten Zitat
Benutzerbild von dataspider
dataspider

Registriert seit: 9. Nov 2003
Ort: 04539 Groitzsch
1.361 Beiträge
 
Delphi 11 Alexandria
 
#4

AW: Optimierung durch Parameter und Prepared statements

  Alt 16. Jun 2010, 07:07
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
Frank Reim
  Mit Zitat antworten Zitat
Antwort Antwort

« Vorheriges Thema | Nächstes Thema »

Forumregeln

Es ist dir nicht erlaubt, neue Themen zu verfassen.
Es ist dir nicht erlaubt, auf Beiträge zu antworten.
Es ist dir nicht erlaubt, Anhänge hochzuladen.
Es ist dir nicht erlaubt, deine Beiträge zu bearbeiten.
BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are aus
Gehe zu:

• Impressum · AGB · Datenschutz · Nach oben
Alle Zeitangaben in WEZ +1. Es ist jetzt 09:05 Uhr.
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024-2025 by Thomas Breitkreuz