Subject Re: [ib-support] Mass insertion speeds
Author David K. Trudgett
On Thursday 2002-06-06 at 20:32:47 -0700, Jason Frey wrote:

> I'm inserting ~360,000 records in a table in my database (The table has 3
> columns, all indexes disabled for the insert). I'm running firebird 1.0 on
> WinXP, athlon 1.2ghz, 768 meg of ram.
> What kind of speed should I be seeing in inserting records? I'm currently
> seeing between 15,000 and 20,000 records a minute. A sample of the inserts
> are like this:
> insert into temp values('86147','346.80','00900-10000.00');
> insert into temp values('86147','346.81','00900-10000.00');
> insert into temp values('86147','346.90','00900-10000.00');
> I have a commit every 15,000 records. Forced Writes are enabled.
> It just seems like 15k records a minute seems... slow. Maybe I'm off base
> here.
> Also, is there a better way to do a mass insert than having the insert
> statements in a file, and just doing them one by one?
> MSSqlServer has the data import, which flies through through my source tab
> delimited file and inserts the entire set of records in 10-15 seconds (What
> it does, I'm not sure). Does FB have something similar?

Yes, it has an external file capability, whereby it can read fixed
length records from a text file as if it were a table in the database
(sort of). You can then run a single statement or multiple statments
to select from the external table and insert into the target table.
The documentation tells you how to set up external tables.

You could also run a small program to do the job using a prepared
insert query. This removes the parsing overhead for each statement.

David Trudgett