Hello all:

I'm in need of help…I have been seeing very, very slow performance
executing queries against a Firebird 1.0.0.796 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
Plan PLAN SORT (SORT (JOIN (OUTBOUNDMEDIA
NATURAL,MEDIAITEM INDEX (IDX_MEDIAITEM_ISTATE))))
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
here??

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