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.

best regards
Pavel Cisar
IBPhoenix