Subject Re: Mass insertion speeds
Author csswa
You will find turning Forced Writes OFF is a lot faster...

BUT!!!! only do this if you are populating a DB, have it backed up,
or can otherwise recover from an error very quickly.

DO NOT turn forced writes off for a production db, and especially do
not muck about with the forced writes setting when there are
connected users.

Otherwise follow David K.'s sound advice.

Andrew Ferguson
-- All your babes are belong to me.

--- In ib-support@y..., "David K. Trudgett" <dkt@r...> wrote:
> 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
> > 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
> > 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
> (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