Subject Re: Insert speed very slow in large DB
Author 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!
> Thanks.

There are a number of things that may be in play here. First thing to
understand, deleting all the records in a table doesn't delete them.
It flags them as deleted so new transactions will not "see" them, but
they are still in the index until they are garbage collected. Garbage
collection will happen when a transaction stumbles across the deleted
record, notices that there are no transactions old enough to care
about that record. This space is retained by Firebird for future
records. It is a bit more complex than that, and superserver has a
dedicated thread that does the garbage collection, and V2 modifies the
behaviour slightly again, but you get the gist. You may have been
competing for resources with a sweep or a backup or something.

You mentioned the only other object used is a generator, this is
definately not your issue, they are lightning fast.

You might want to take a look at this.