Firebird Performance

We currently have a firebird (1.5) database used by around 10 users and it's only 120MB, growing by about 10MB a week.

The application that accesses the database users Crystal Reports 9 to generate reports but some reports are taking a very long time to complete.

The developer first said that there is a problem CR9 but now says we have backup and restore the database on a weekly basis to correct the problem!

This seems to be absolutely riduculous as I would usually only normally restore a database in disaster recovery situation.

Taking into consideration that Firebird is a database that shouldn't need much administration, has anybody got any suggestions on why this needs to take place?

To me it seems that either the queries that generate the reports are faulty or the application/database is poorly designed.

First question: Does it?
If it does, what is the pattern of performance degredation after the backup/restore process?

Clive

Start reading this very interesting article about performance tuning.

The degradation varies greatly. Usually after a restore the reports work as they should but the next day they might not.

The problem also occurred when the database was only 30MB.

I installed a program that analyses the statistics of the database and 80% of the indices are useless due to the 'key' being the same in all of them, 10% are bad with duplicated keys.


What does a backup and restore do to optimize the database (apart from index selectivity)?

The server only runs this database, 3GB of memory, Dual 3GHz processors, RAID 10 for the database file and RAID 1 for the OS.

Are you running the classic server (which can take advantage of multiple processors) or the super server (multithreaded, better on single proc machines)?
Apart from analysing stats, did you update them?

I've tried classic and super, both have the same problem. The developer/suppler of the software insists on it being super server, so it's using that one at the moment.

I used IBAnalyst (from ib-aid website), to have a quick look at the database. I'm not certain how good or accurate it is but it seems useful.

My major concern is that the developer is concentrating on adding new features to the product rather than improving the performance.

Well, that certainly indicates some significant design issues; but does not really explain why a backup/restore would fix the problem on such a temporary basis. After all, the more data you add, the less likely the cardinality of the index will change significantly between backup/restore events.
Are you able to test/examine the queries being generated by CR9 using direct SQL through a Firebird tool?
Is there anything else happening on this server that could be affecting performance?
Clive