paul.hectors wrote:

> Configuration
> =============
> I have an application using Firebird 1.5 database on Windows XP.
> Firebird is installed with default options.
> Application is a Delphi program.
> Program has 10 connections to the database and is the only program
> using the database.
> The computer is an IBM desktop computer with a 2.8 GHz P4 processor.
>
> Problem
> =======
> The application has been running for 9 months and the database is
> 870mb. The user has complained that the PC is very slow to startup and
> the way to improve this is by running a defrag. The defrag is done on
> a monthly basis at the moment. The customer has determine that it is
> the gdb file that is causing the defragmentation.

Hi Paul!

Firebird gets more disk space from the system when it requires more
space than what is available. Upon deletion, this space is saved for
reuse by Firebird and not given back to the system. Hence, Firebird just
grabs more and more hard disk space for itself, and never gives it back.
I can't see how it is possible to cause fragmentation without giving
back space to the file system, so I think the above assumption about the
Firebird .gdb file being to blame is incorrect. Of course, Firebird will
use fragmented areas on the harddisk if that is what it gets from the
file system (you want 100 bytes? - well, I'll give you one byte there
and two over there...), but Firebird itself doesn't cause fragmentation.

Another thing is that if the harddisk is small compared to the database
and the .gdb file takes - say 50% of the space, then you of course have
only half as much space for other programs. When the harddisk gets close
to full, and you have to start deleting to insert new data, then the
disk gets fragmented.

I don't know enough about temporary files (sort files) to say how
Firebird uses these. My guess is that if your program makes Firebird
need big temporary files (e.g. a large sorted result set), then this
could use quite some space and be part of the reason other files got
fragmented for the same reason as mentioned above.

What can cause gradual slowdown after using your program for a while, is
if you don't take care of your transactions. If there is a transaction
that is kept open for a long time, you risk having lots of versions of
each record and then Firebird has to check which version to use with the
current transaction for every record you want to select.

Though your problem is more likely to be the system restore 'feature' of
.gdb files that Paul Beach has already mentioned.

Set