Optimierung durch Parameter und Prepared statements

[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;