Hi Geoff,

Combining IB_DataPump with explicitly declared Sessions and Transactions
resulted in
23:52:40.640 Database Maint Completed in 21.156 Secs

Not using IB_DataPump now
23:57:55.218 Database Maint Completed in 53.953 Secs

versus the FIBPlus result of
15:22:48.843 Database Maint Completed in 13.812 Secs

It sure looks like your 'Experimental Improvements' could be quite an asset
to IBOjects :)

Would love to try them out in my tests.

Thank you taking your time and not considering me in the group classified by
Jason as ;)

>IBO is not for people who want to just slap together basic routines and
>expect top notch performance. IBO is for those who are willing to come to
>know it as if their livelihood depended on it and to always be willing to
>ask: Am I making the most of this toolset?

Best Regards
Hans

----- 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.
>
>
>
> ------------------------------------
>
> ___________________________________________________________________________
> IB Objects - direct, complete, custom connectivity to Firebird or
> InterBase
> without the need for BDE, ODBC or any other layer.
> ___________________________________________________________________________
> http://www.ibobjects.com - your IBO community resource for Tech Info
> papers,
> keyword-searchable FAQ, community code contributions and more !
> Yahoo! Groups Links
>
>
>