Minoru wrote:
[...]

> This thread sounds bad IBO performance for me...
> I try the Geoff's Xfer_IBO code on my environment.
> It seems that the result is later than the parameterized-insert.
> (Sorry, blob subtype is text only in this testing)

[...]

Yes there are lots of possible optimisations to the code...
although your parameterized insert is probably the most
worth-while. There are interesting tricks with buffers etc
in special situations like this where the structure is
identical, but Assign is too convenient to be ignored when
looking for an easy solution.

The posted sample was intended to compare equivalent code
and features of IBO and FIBPlus. I imagine that FIBPlus has
something similar to IBO's DSQL to perform this sort of
DML execute... perhaps someone with FIBPlus experience could
update that sample so we can compare that situation too.

Your times certainly highlight the advantages of direct
execute of DML. I copied my version of what you did below.


My earlier example of 102010 rows (with no blobs) using DSQL
and an explicit INSERT statement gives:

= = = xfer_ibo = = =
App Init Time: 0ms
Object Prepare Time: 125ms
Records Transfered: 102010
Records Transfer Time: 11782ms
Transaction Commit Time: 1156ms
Connection Close Time: 0ms
Application Run Time: 13063ms

Down from 22sec to 12sec in transfer time. Not as dramatic as
the times in your tests Minoru... not sure why. I hope it is
not a Delphi 2010 / Unicode thing, but I guess that's possible,
it must cost something.


In this table I found that the Assign worked faster than the
AsString assignments... probably because there is a dedicated
datetime part to the assign code (and my small table had two
timestamp columns so that it made the difference of a few
seconds). This implies that Assign function could benefit
from further specialisation for number types etc.

With the later blob intensive table there was almost no
measurable difference (most of the time for the 106 rows
is spent in transferring blobs).


--
Geoff Worboys
Telesis Computing

===============================================================

program Xfer_IBO;

{$APPTYPE CONSOLE}

uses
SysUtils,
Windows,
IB_Components;


{$DEFINE DSQL_INSERT} // Faster than using a dataset to insert

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';
{$IFDEF DSQL_INSERT}
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: TIB_Connection;
SrcTran: TIB_Transaction;
SrcCsr: TIB_Cursor;
DstCon: TIB_Connection;
DstTran: TIB_Transaction;
{$IFDEF DSQL_INSERT}
DstDSql: TIB_DSQL;
{$ELSE}
DstCsr: TIB_Cursor;
{$ENDIF}

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;
{$IFDEF DSQL_INSERT}
DstDSql := TIB_DSQL.Create(nil);
with DstDSql do
begin
IB_Connection := DstCon;
IB_Transaction := DstTran;
SQL.Text := DstSqlInsert;
end;
{$ELSE}
DstCsr := TIB_Cursor.Create(nil);
with DstCsr do
begin
IB_Connection := DstCon;
IB_Transaction := DstTran;
SQL.Text := DstSql;
end;
{$ENDIF}

TimerOutput('App Init Time');

SrcCon.Connected := true;
SrcTran.StartTransaction;
SrcCsr.Prepare;

DstCon.Connected := true;
DstTran.StartTransaction;
{$IFDEF DSQL_INSERT}
DstDSql.Prepare;
{$ELSE}
DstCsr.Prepare;
{$ENDIF}

TimerOutput('Object Prepare Time');

rcount := 0;
SrcCsr.First;
with SrcCsr do
while not Eof do
begin
{$IFDEF DSQL_INSERT}
for ii := 0 to FieldCount - 1 do
DstDSql.Params[ii].Assign(Fields[ii]);
DstDSql.Execute;;
{$ELSE}
//DstCsr.Append;
DstCsr.Insert;
for ii := 0 to FieldCount - 1 do
DstCsr.Fields[ii].Assign(Fields[ii]);
DstCsr.Post;
{$ENDIF}
Next;
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.