Subject Why is Firebird so slow?
Author nitaligavino <>
Hello all:

I'm in need of helpÂ…I have been seeing very, very slow performance
executing queries against a Firebird server.

For example:
SELECT OutboundMedia.MKey, OutboundMedia.DestinationDomain
FROM OutboundMedia, MediaItem
WHERE MediaItem.IState = 5
GROUP BY OutboundMedia.MKey, OutboundMedia.DestinationDomain

Here is the statistics for this query:
Execution Time: 00:07:28:0275
Prepare Time: 00:00:00:0010
Starting Memory: 34831335
Current Memory: 34840913
Delta Memory: 9578
Number of Buffers: 8000
Reads: 177
Writes: 31
Records Fetched: 1500

That's right this query took 7 minutes to execute and during this
time ibserver.exe consumes nearly 100%cpu time. What is going on

If however I execute:
SELECT OutboundMedia.MKey, OutboundMedia.DestinationDomain
FROM OutboundMedia
GROUP BY OutboundMedia.MKey, OutboundMedia.DestinationDomain

Simply dropping the second table, the execution time is milliseconds.
What I need to know is what is going on here. The database only has
1900 records total.

And yes, there are many ways to write SQL queries but the syntax is
not what I'm questioning here. The issue is the ibserver taking 7
minutes to execute the query and return the results. This sort of
performance is just totally unacceptable from a database engine. I'm
afraid to think what would happen if I execute the same query with
say 100,000 records? I might just need to come back the next day.

Is there someway to configure the engine for better performance? I
have already spent a day evaluation the cache settings to find what I
believe to be the optimal setting for my environment., e.g., setting
buffers to 8000 or approximately a 32mb cache.

Best regards,
Dan Crea