--- In firebird-support@yahoogroups.com, Pavel Cisar wrote:

> nocsav wrote:
> > They defragmented their disk (using Windows XP's defrag tool) and
> > claimed some amazing performance improvements. Queries were taking

a

> > few seconds instead of 50 minutes, etc.
> >
> > I had the idea that maybe fragmentation could be reduced if the
> > database expanded in larger increments than whatever is the
> > default. (Also, is expanding a file an expensive operation?)
>
> I doubt that any level of disk fragmentation would decrease the
> query performance that much. It must be something else.
>
> > I asked the user to tell me more about their defragmenting story.
> > He told me that the defrag tool could not do anything with the GDB
> > file. So, he backed it up and restored the GDB from GBAK.
>
> Yup, here it goes. Fragmentation at database level does have impact
> on performance, and restore from backup will bring it to good shape.
> So performance improvement is no surprise here. I also suppose, that
> empty database was created when your application was installed,
> including all indices. If you don't update index statistics in your
> application, or don't perform restore from backup from time to time,
> then index statistics are not updated (in fact, they are completely
> useless) and optimizer can't do its best. Updated index stats itself
> can be the reason for observed performance boost.

Add to that the common problem of long-running transactions. If the
client starts a transaction that never commits or only use autocommit,
then lots of old versions of records will never be removed. Check the
gap between oldest active transaction and next transaction to find
whether this is an additional problem (it would also be visible by the
file size being considerably reduced through backup/restore).

Set