Subject Re: Insert speed very slow in large DB
Author dbambo2000
--- In, "Adam" <s3057043@y...> wrote:
> --- In, "dbambo2000" <gcampbel@p...>
> wrote:
> > 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
> machine.
> 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.
> Adam

Thanks for the feedback Adam and Ann. I've been running more tests,
and the results are much quicker than before. When I tested it the
first time, I ran it 3 or 4 times in a row and each time it took about
5 minutes. Tonight the process completes in just a couple of seconds.
There must have been some other housecleaning that it was doing
before. I've also rebooted the system since the earlier tests. I'm
very happy its running so quickly, I was beginning to think that data
input would be a major bottleneck. I apologize for not doing better
testing before. Thanks again for your input.

I love firebird!