Some comments:

1. If it's performance you are seeking, there is NO WAY you should be
using TIBOTable for an executable process. Put this statement into a
TIBOQuery...

2. You are getting an enormous amount of overhead by using the default
Autocommit. A fresh transaction is being started for every
iteration. Take explicit control of the transaction and start it outside
the loop.

3. Forced writes will slow things down (as observed) but if you have a
fragile network environment or users who habitually terminate a database
session by switching the computer off, it's worth the cost.

As a general comment, it doesn't make sense to force InterBase to behave
like a file-based database (which it's not) and then expect it to compare
favourably with file-based databases, performance-wise. The kind of
"benchmark" you are trying to do is rather silly, for example, because one
would never be inserting 1000 records from a table interface. The Insert
and Append methods are intended for one-by-one manual inserts.

A further comment is this. Client/Server databases aren't intended for
desktop use. If speed is your only criterion then stay with the desktop
database. The transaction model is meant to preserve data integrity in a
multi-user environment. It would be surprising if there were no cost to
that. If your single-user app is getting broken by user behaviour under
Paradox, it won't get better by switching to another database.

For your bulk insert, try this:

Set up a TIBOQuery, e.g. MyQuery, with KeyLinks set to the primary key of
the table.
Use this as its SQL:

INSERT INTO MyTable((KEY, FIELD) values (:KEY, :FIELD);

And we have to assume that you are starting with an empty table, since your
test isn't able to avoid key violations otherwise.
Therefore, if you are using some interactive tool to empty the table,
follow it up with another interactive DSQL:

SELECT * FROM MyTable

which will perform the garbage collection for the bulk delete. Make sure
you commit after each of these preparatory operations, otherwise your
client routine won't be able to get write access.


procedure....
var
Loop: integer;
begin
with MyQuery do
if IB_Transaction.InTransaction then
IB_Transaction.Rollback;
if Prepared then
Prepared := False;
Prepare;
IB_Transaction.StartTransaction; // AutoCommit is now disabled
For Loop := 1 to 1000 do
begin
ParamByName('KEY').AsInteger := Loop;
ParamByName('FIELD').AsString := 'ABC';
ExecSQL;
end;
try
IB_Transaction.Commit;
except
IB_Transaction.Rollback;
ShowMessage('Batch insert cancelled due to error'); // rough error
trapping
end;
end;

H.

At 05:54 PM 07-01-01 +0100, you wrote:

>Hello Geno
>
>try the simple following code for append records into InterBase:
>
>Table1.Close;
>Table1.BeginBusy(True); //speed booster,eliminating 20 msec Cursor flicker
>!!
>
> For Loop := 1 To 1000 Do
> Begin
> Table1.SQL.Text := 'INSERT into MYTABLE (KEY, FIELD) values ('
>+IntToStr(Loop)+ ', ABC)';
> Table1.ExecSQL;
> End;
>
>Table1.EndBusy;
>
>
>Peter Czarnecki
>Eustachio SA
>http://eustachio.com
>info@...
>
>
>----- Original Message -----
>From: "Geno " <strtline@...>
>To: <IBObjects@egroups.com>
>Sent: Sunday, January 07, 2001 4:56 PM
>Subject: [IBO] Interbase,Advantage,Paradox
>
>
> > Hello:
> >
> > I have been reading the posts about the speed advantages of Interbase
> > over the BDE. It seems to be agreed that Interbase is much faster.
> > This leads me to believe I must be doing something wrong. I am a
> > novice to both Interbase and IBO. I have several applications that
> > have been running for years which are written in D5 with
> > BDE/Paradox. They are all applicaations that run on local networks
> > with less that 10 users. They all run fine except for the nasty
> > habit of Paradox to get corrupted indexes. Because of this, I
> > thought switching to another database might be a good idea. My first
> > switch was to Advantage. It worked fine and was relatively fast but
> > the Advantage local server does not support transactions. I was
> > going to purchase the Advantage remote server when I heared about
> > Interbase. Since it was free, it seemed foolish to spend money on
> > Advantage.
> > I have been working on converting the application and one of the
> > first things I noticed was how much slower everthing ran. I created
> > a few test progams that simply loop and add 1000 records to each
> > different type of database. The table format is simple:
> > TheKey : Integer;
> > TheField : Character[20];
> >
> > The loop is for the BDE\Paradox version is:
> >
> > For Loop := 1 To 1000 Do
> > Begin
> > Table1.Insert;
> > Table1.FieldByName('TheKey').AsInteger := Loop;
> > Table1.FieldByname('TheField').AsString := 'ABC';
> > Table1.Post;
> > End;
> >
> > The loop for the Advantage and Interbase versions use querys instead
> > of tables but are otherwise similar.
> >
> > In the BDE/Paradox version, the records are added in roughly 2
> > seconds. In the Advantage local server version, around 5 seconds.
> > In the Interbase version, around 15 seconds. In the Interbase
> > vsrsion this seems true if I use a DataSource/IBOQuery or IBOTable or
> > a IB_DataSource/IB_Query.
> >
> > I realize it is probable that I should be optimizing the application
> > for use with Interbase. However, my plan was to first convert to
> > Interbase using DataSource/IBOQuerys then eventually to
> > TIB_DataSource/IB_Querys.
> >
> > Can someone tell be what I am doing wrong?
> >
> > Thanks.
> >
> >
> >
> >
> >
> >
> >
>
>

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________