Geoff,

These results are more along the lines of what I would expect.
IBO has more overhead on the prepare side of things but it is designed to
work much more fluidly and efficiently after prepared.
Thus, with fewer records the overall performance is going to appear some
what less for small recordsets when all things are considered.
Where IBO starts to shine out is in the more optimized handling of data that
at some point recoups the performance hit on the front-side to enable that.
Mostly it shines out in all of the versatile things you can do with a
prepared dataset in an efficient manner including the handling of large
datasets.

Thanks for taking the time to double-check things.

Kind regards,
Jason Wharton

----- Original Message -----
From: "Geoff Worboys" <geoff@...>
To: <IBObjects@yahoogroups.com>
Sent: Sunday, March 21, 2010 9:57 PM
Subject: Re: [IBO] development


> Hans wrote:
> [...]
>> All products 'straight out of the box', no tricks applied.
>
> Well, since I wanted to write some tests for my experimental
> changes anyway I went and downloaded the FIBPlus trial and
> did some experiments.
>
> A command-line app for each product so everything sits clear
> and obvious in the one source file (copied below).
>
> A 10201 row table (no blobs) of test data produced results
> that were inconclusive in terms of relative performance:
>
> = = = Xfer_IBO = = =
>
> Delphi 2010 / IBO v4.10.alpha
> -----------------------------
> App Init Time: 0ms
> Object Prepare Time: 110ms
> Records Transfered: 10202
> Records Transfer Time: 2172ms
> Transaction Commit Time: 187ms
> Connection Close Time: 16ms
> Application Run Time: 2485ms
>
>
> = = = Xfer_FIBPlus = = =
>
> Delphi 2010 / FIBPlus v6.9.9 - AutoReWriteSQLs := true (a mistake)
> -----------------------------
> App Init Time: 0ms
> Object Prepare Time: 125ms
> Records Transfered: 10201
> Records Transfer Time: 42656ms
> Transaction Commit Time: 156ms
> Connection Close Time: 32ms
> Application Run Time: 42969ms
>
> Delphi 2010 / FIBPlus v6.9.9 - Using explicit InsertSQL
> -----------------------------
> App Init Time: 0ms
> Object Prepare Time: 188ms
> Records Transfered: 10201
> Records Transfer Time: 1859ms
> Transaction Commit Time: 188ms
> Connection Close Time: 15ms
> Application Run Time: 2250ms
>
>
> The prepare times fluctuate quite a lot, the total times also
> varied from 2s up to 3.5s... so this was inconclusive.
> I also did the test with Delphi v6 and got similar times.
>
> (You may notice that when I got a result from FIBPlus that was
> 17 x the time I got from IBO I did not automatically assume
> that FIBPlus development was heading in the wrong direction, I
> did consider the possibility that the mistake was mine. ;-)
>
> After running this brief test several times I would conclude
> that FIBPlus was marginally faster in this example. (But
> noting that the example code is NOT an optimised transfer
> solution for either product.)
>
> So then I tried with 10x the number of rows. (Literally just
> loaded the table with 10 copies of the test data.)
>
> = = = Xfer_IBO = = =
>
> Delphi 2010 / IBO v4.10.alpha
> -----------------------------
> App Init Time: 0ms
> Object Prepare Time: 94ms
> Records Transfered: 102010
> Records Transfer Time: 22000ms
> Transaction Commit Time: 797ms
> Connection Close Time: 0ms
> Application Run Time: 22891ms
>
> = = = Xfer_FIBPlus = = =
>
> Delphi 2010 / FIBPlus v6.9.9 - Using explicit InsertSQL
> -----------------------------
> App Init Time: 0ms
> Object Prepare Time: 94ms
> Records Transfered: 102010
> Records Transfer Time: 23609ms
> Transaction Commit Time: 844ms
> Connection Close Time: 16ms
> Application Run Time: 24563ms
>
>
> As before I cleared the destination table with a separate
> program between each run.
>
> From this longer run I would have to say that IBO was
> marginally faster... but again highlighting that this is NOT
> the best that either product can do.
>
>
> Copying the source code for each program below. Just alter
> the const strings at the top of each to fit with your own
> database. You will see that the central loop attempts to be
> a copy of the loop you used - the difference between using
> Append and Insert was insubstantial which is what I expected
> from reading the source. (Insubstantial with TIB_Cursor - it
> would not be insubstantial with TIB_Query.)
>
> The hope is that the output times may help you identify if
> your performance hits are coming from some specific area of
> the code (the prepare or whatever).
>
> --
> Geoff Worboys
> Telesis Computing
>
> ===============================================================
>
> program Xfer_IBO;
>
> {$APPTYPE CONSOLE}
>
> uses
> SysUtils,
> Windows,
> IB_Components;
>
>
> const
> SrcDB: string = 'IBObjects_Tests';
> DstDB: string = 'Tmp';
> Usr: string = 'SYSDBA';
> Pwd: string = 'masterke';
>
> SrcSql: string = 'SELECT * FROM LOCALITIES';
> DstSql: string = 'SELECT * FROM LOCALITIES FOR UPDATE';
>
>
> var
> SrcCon: TIB_Connection;
> SrcTran: TIB_Transaction;
> SrcCsr: TIB_Cursor;
> DstCon: TIB_Connection;
> DstTran: TIB_Transaction;
> DstCsr: TIB_Cursor;
>
> AppStart: DWord;
> CheckPt: DWord;
>
> ii, rcount: integer;
>
>
> procedure TimerOutput(const AMsg: string);
> var
> TmpTicks: DWord;
> begin
> TmpTicks := GetTickCount;
> WriteLn( AMsg + ': ' + IntToStr(TmpTicks - CheckPt) + 'ms' );
> CheckPt := TmpTicks;
> end;
>
>
> begin
> try
> AppStart := GetTickCount;
> CheckPt := AppStart;
>
> SrcCon := TIB_Connection.Create(nil);
> with SrcCon do
> begin
> Database := SrcDB;
> UserName := Usr;
> Password := Pwd;
> end;
> SrcTran := TIB_Transaction.Create(nil);
> with SrcTran do
> begin
> IB_Connection := SrcCon;
> ReadOnly := true;
> end;
> SrcCsr := TIB_Cursor.Create(nil);
> with SrcCsr do
> begin
> IB_Connection := SrcCon;
> IB_Transaction := SrcTran;
> SQL.Text := SrcSql;
> end;
>
> DstCon := TIB_Connection.Create(nil);
> with DstCon do
> begin
> Database := DstDB;
> UserName := Usr;
> Password := Pwd;
> end;
> DstTran := TIB_Transaction.Create(nil);
> with DstTran do
> begin
> IB_Connection := DstCon;
> end;
> DstCsr := TIB_Cursor.Create(nil);
> with DstCsr do
> begin
> IB_Connection := DstCon;
> IB_Transaction := DstTran;
> SQL.Text := DstSql;
> end;
>
> TimerOutput('App Init Time');
>
> SrcCon.Connected := true;
> SrcTran.StartTransaction;
> SrcCsr.Prepare;
>
> DstCon.Connected := true;
> DstTran.StartTransaction;
> DstCsr.Prepare;
>
> TimerOutput('Object Prepare Time');
>
> rcount := 0;
> SrcCsr.First;
> with SrcCsr do
> while not Eof do
> begin
> //DstCsr.Append;
> DstCsr.Insert;
> for ii := 0 to FieldCount - 1 do
> DstCsr.Fields[ii].Assign(Fields[ii]);
> Next;
> DstCsr.Post;
> Inc(rcount);
> end;
> WriteLn('Records Transfered: ' + IntToStr(rcount));
>
> TimerOutput('Records Transfer Time');
>
> SrcTran.Commit;
> DstTran.Commit;
>
> TimerOutput('Transaction Commit Time');
>
> SrcCon.Close;
> DstCon.Close;
>
> TimerOutput('Connection Close Time');
>
> CheckPt := AppStart;
> TimerOutput('Application Run Time');
>
> except
> on E: Exception do
> Writeln(E.ClassName, ': ', E.Message);
> end;
> end.
>
> ===============================================================
>
> program Xfer_FIBPlus;
>
> {$APPTYPE CONSOLE}
>
> uses
> SysUtils,
> Windows,
> FIBDatabase,
> pFIBDatabase,
> FIBDataSet,
> pFIBDataSet;
>
>
> const
> SrcDB: string = 'IBObjects_Tests';
> DstDB: string = 'Tmp';
> Usr: string = 'SYSDBA';
> Pwd: string = 'masterke';
>
> SrcSql: string = 'SELECT * FROM LOCALITIES';
> DstSql: string = 'SELECT * FROM LOCALITIES';
> {$IFDEF AUTO_GEN_SQL}
> DstSqlTableName: string = 'LOCALITIES';
> DstSqlTableKeys: string = 'PK';
> {$ELSE}
> DstSqlInsert: string =
> 'INSERT INTO LOCALITIES ('#13#10 +
> ' PK, FLAGS, NAME, POSTAL_CODE, CREATED, UPDATED )'#13#10 +
> 'VALUES ('#13#10 +
> ' :PK, :FLAGS, :NAME, :POSTAL_CODE, :CREATED, :UPDATED )';
> {$ENDIF}
>
> var
> SrcCon: TpFIBDatabase;
> SrcTran: TpFIBTransaction;
> SrcCsr: TpFIBDataSet;
> DstCon: TpFIBDatabase;
> DstTran: TpFIBTransaction;
> DstCsr: TpFIBDataSet;
>
> AppStart: DWord;
> CheckPt: DWord;
>
> ii, rcount: integer;
>
>
> procedure TimerOutput(const AMsg: string);
> var
> TmpTicks: DWord;
> begin
> TmpTicks := GetTickCount;
> WriteLn( AMsg + ': ' + IntToStr(TmpTicks - CheckPt) + 'ms' );
> CheckPt := TmpTicks;
> end;
>
>
> begin
> try
> AppStart := GetTickCount;
> CheckPt := AppStart;
>
> SrcCon := TpFIBDatabase.Create(nil);
> with SrcCon do
> begin
> DBName := SrcDB;
> ConnectParams.UserName := Usr;
> ConnectParams.Password := Pwd;
> SQLDialect := 3; // defaults to 1 for some reason
> // LibraryName := 'fbclient.dll'; cant do this in trial, have to
> rename dll
> end;
> SrcTran := TpFIBTransaction.Create(nil);
> with SrcTran do
> begin
> DefaultDatabase := SrcCon;
> //??ReadOnly := true;
> end;
> SrcCsr := TpFIBDataSet.Create(nil);
> with SrcCsr do
> begin
> Database := SrcCon;
> Transaction := SrcTran;
> SelectSQL.Text := SrcSql;
> end;
>
> DstCon := TpFIBDatabase.Create(nil);
> with DstCon do
> begin
> DBName := DstDB;
> ConnectParams.UserName := Usr;
> ConnectParams.Password := Pwd;
> SQLDialect := 3; // defaults to 1 for some reason
> // LibraryName := 'fbclient.dll'; cant do this ins trial, have to
> rename dll
> end;
> DstTran := TpFIBTransaction.Create(nil);
> with DstTran do
> begin
> DefaultDatabase := DstCon;
> end;
> DstCsr := TpFIBDataSet.Create(nil);
> with DstCsr do
> begin
> Database := DstCon;
> Transaction := DstTran;
> SelectSQL.Text := DstSql;
> {$IFDEF AUTO_GEN_SQL}
> with AutoUpdateOptions do
> begin
> AutoReWriteSQLs := True;
> UpdateTableName := DstSqlTableName;
> KeyFields := DstSqlTableKeys;
> end;
> {$ELSE}
> InsertSQL.Text := DstSqlInsert;
> {$ENDIF}
> end;
>
> TimerOutput('App Init Time');
>
> SrcCon.Connected := true;
> SrcTran.StartTransaction;
> SrcCsr.Prepare;
>
> DstCon.Connected := true;
> DstTran.StartTransaction;
> DstCsr.Prepare;
>
> SrcCsr.Open;
> DstCsr.Open;
>
> TimerOutput('Object Prepare Time');
>
> rcount := 0;
> SrcCsr.First;
> with SrcCsr do
> while not Eof do
> begin
> //DstCsr.Append;
> DstCsr.Insert;
> for ii := 0 to FieldCount - 1 do
> DstCsr.Fields[ii].Assign(Fields[ii]);
> Next;
> DstCsr.Post;
> Inc(rcount);
> end;
> WriteLn('Records Transfered: ' + IntToStr(rcount));
>
> TimerOutput('Records Transfer Time');
>
> SrcTran.Commit;
> DstTran.Commit;
>
> TimerOutput('Transaction Commit Time');
>
> SrcCon.Close;
> DstCon.Close;
>
> TimerOutput('Connection Close Time');
>
> CheckPt := AppStart;
> TimerOutput('Application Run Time');
>
> except
> on E: Exception do
> Writeln(E.ClassName, ': ', E.Message);
> end;
> end.