Subject Re: Insert speed very slow in large DB
Author Adam
--- In, "dbambo2000" <gcampbel@p...>
> I have a table with 6 columns (3 of which are 18 digit numeric
> fields). There are 57 million records in the table (an 5 gigabyte
> file). Four of the columns have indexes (including all 3 numeric
> fields). Inserting just two new records via
> "Insert into tableX (col1, col2, col3 ...)VALUES (val1, val2,
> val3...)"
> takes between 5 and 10 minutes!
> I'm running firebird super server Version 1.5 on a Windows XP Pro P4
> machine with 756MB Ram. My application uses Coldfusion and ODBC to
> connect to the database.
> Anyone have any ideas to help me SPEED THIS UP???

More information please.

Is this a once off insert or a regular operation. If once off, use the
embedded server and local connection (obviously). Turn off forced
writes (for the inital batch insert only, re-enable them afterwards),
disable the indices (again for the initial batch insert only,
re-enable them afterwards). I shouldn't have to say this, but I assume
with these suggestions you would have a working backup taken before
you run the batch.

Like Ann said, any triggers? 190 inserts per second seems a bit slow
to me. Is it really 5 - 10 minutes, or is that figure more an
exageration and what you mean is that it is slow? What sort of speed
are you expecting? Is the input file on the same drive as the database
file? If so, you could be overloading the I/O capabilities of your

Depending on your answers to all the above, it could be the speed of
on of the many interfaces you are using. Another option is to use
external tables, fill it using ColdFusion, then run a query that
transfers the data to a table within the database.