--- In IBObjects@yahoogroups.com, "henry FRANQUET" wrote:

> Hi,
> I use TIB_DataPump and IB_cursor to insert data into a database (a
> empty one created with backup and restore only metadata).
> As I have Tables with about one hundrer thousand records, I perform
> a commit retainning on after execute event. When looking to
> statistics, I see then next transaction counter growing.
>
> How can I improve inserting ? Is there a counter I could use, for
> doing a commit every 1000 inserts ?
>
> I have seen in forums, somebody's speaking of inserting thousand's
> insert a second, but even when doing insert without IB_DataPump and
> committing every 1000 insert, I can't go further 400 inserts a
> second with 2 fields table. Should I use virtual disk for temporary
> files, disable forced write and enable it after inserts done ?
>
> Thanks for your suggestions

Doing something like

try
TIB_DSQL1.Prepare;
while not eof(MyFile) do
begin
readln(MyFile, MyLine);
TIB_DSQL1.Params[1].AsString:=copy(MyLine, 1, 11);
TIB_DSQL1.Params[2].AsString:=copy(MyLine, 12, 2);
TIB_DSQL1.Execute;
end;
except
TIB_Transaction1.Rollback;
end;
if TIB_Transaction1.TransactionIsActive then
TIB_Transaction1.Commit;

should be faster than a few hundred inserts per second (unless you're
doing it over the Internet, of course). You must use parameters (and
prepare before the loop), and not commit (nor use commitretaining) too
often. Using ParamByName rather than Params will be a bit slower, but
you can use variables of type TIB_Column (or is there a TIB_Param
type?) and e.g. assign them to ParamByName('Field1') before the loop
(it is more readable than using Params and will work even if you
modify your SQL to include additional parameters).

HTH,
Set