Subject Re: [firebird-support] INSERT efficiency
Author Alexandre Benson Smith
At 01:43 21/01/2004 +0000, you wrote:

> Using Delphi 5 and Firebird 1.0.3:
> I am reading a 16 million line text file,
>parsing the data on each line, and storing the
>resulting records into a table.
> The key loop is as follows:
>while not eof(Fi) do begin
> readln(Fi,S);
> Parse(S); { parse string "S" into structure "Rec" }
> IB_Q.sql.clear;
> IB_Q.sql.add(format('INSERT INTO T1 (ID1, ID2, D1, D2, V1) VALUES ('
> +' ''%s'', ''%s'', ''%s'', ''%s'', %s )',
> [Rec.ID1,Rec.ID2,Rec.D1,Rec.D2,Rec.V]));
> IB_Q.execSQL;
> The table has a primary key consisting of the first 4
>columns. Columns ID1, ID2, D1 and D2 are all VARCHARs of
>certain lengths, and V1 is a double. The IB_Query is
>connected to an IB_Connection. I haven't done anything
>special with these other than set the minimally required
> I'm sure this is the absolute slowest way to perform
>this task. Nothing is "prepared", no tricks of the trade
>are used.
> I'd like to learn some of the more obvious and straight-
>forward things I can do to make this process more efficient.
>-- Stan

First option.

1.) Prepare the query outside the loop
2.) Assign parameters (previously prepared) for each row
3.) commit at a "certain" (try 10000) interval..., check for the best for
your process

Second option.

If that file is in a tabular mode (every field has the same size over the
table), create an external table and do:

Insert into MyTable select * from ExtTable

I think this will be the fastest way tho import a lot of data into fb.


Alexandre Benson Smith
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil


Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004

[Non-text portions of this message have been removed]