Subject Re: [firebird-support] very slow commit
Author Ann W. Harrison
Milan TomeŇ° - Position wrote:
> Dne 09.02.2010 13:26, svanderclock napsal(a):
>> I update one row in 10 tables with a lot of index. (around 300 index
>> to update) to insert or Update the data it is very fast (around 100ms)
>> but, when i commit the SQL, it is very slow (around 3 to 6 seconds!).
>> i see also that my hardrive is use a lot in the commit.
> AFAIK Indexes are updated during commit.

Not usually. If an index is active when a record version is created,
the index will be updated immediately. That's pretty important. If
the index updates were deferred to commit, your transaction wouldn't
see its own updates with indexed queries. Inactive indexes are
recreated when the transaction that activates them commits.

> You can do these steps:
> 1. Before a bulk load of a lot of records it's recommended to deactivate
> indexes and activate them after loading is done

Err... That's going to increase the commit time. Activation recreates
the whole index, so if you have an index with 10,000 records and store
another 5,000 records, you'll be indexing 15,000 records. And, during
your load, queries on the table that could have been indexed will be
full table scans.

Good luck,