Why is my database query slow?

There are various possible reasons. Here's a checklist:

1. First, check the plan of a query. If you don't want to hog the server just by testing plans, you can only request for plan without executing the query on real data. It is done by typing SET PLAN ONLY in isql, or clicking Show Plan in FlameRobin.

If you see a NATURAL plan going against a big table, you've found the problem. If you have where clause or JOIN to that table, make sure you have index defined on related fields. If you do have index, but it isn't used, perhaps you have ascending index (default) and you need descending (or vice versa). Or perhaps you just need to rebuild the index statistics so that Firebird finds it usable. That can be done with SET STATISTICS sql command.

If you use views with unions (you cannot index a view), I highly recommend you use at least Firebird 2.0 as earlier versions don't use any indexes of underlying tables when you use WHERE or JOIN with a view. If you can't use Firebird 2.0, the only way to speed it up is to write a stored procedure that takes value in WHERE clause as agrument.

2. If the plan is ok, perhaps you have bad transaction management, or a lot of deleted records. Perhaps the garbage collection kicks in at a bad momemt. Take a look at database statistics and the gap between oldest and active transaction. Try increasing the sweep interval or turn it off completely and see if it helps.

If you use Delphi and IBX, make sure you understand the implications of different transaction levels. IBX default for AutoCommit is CommitRetaining, which is a killer for the server. The best idea is not to use AutoCommit, but do explicit commits and rollbacks from application at the right point (depending on nature of application and user actions).

3. If all else fails, perhaps you really have a large amount of data. It's time to check your configuration for optimal performance.

a) Do you use PATA, SATA or SCSI disks.

b) Is the database on same disk as the rest of operating system (esp. swap file/partition and temp directory from firebird.conf)?

c) How about memory usage? Swapping on disk (because of low RAM) is a killer for database applications?

Make sure you use at least Firebird 1.5 as it does sorts in memory if the data fits (Firebird 1.0 does it on disk always).

You can also try to increase amount of RAM Firebird will use. There are settings in firebird.conf to increase the default and maximum RAM allocation. If you use Classic, you can also try to increase the page buffers RAM allocation (it is set to low value as Classic creates one for each connection). Use gstat -h and read the Page Buffers line to see how much each client is using, and then increase DefaultDbCachePages in firebird.conf.

d) multi-core CPU? If you are using Super Server you need to set CPU Affinity option in firebird.conf.