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.