Hello all:

I need some help understanding how an SQL statement is executed
within the FB engine. Specifically, I need to understand why an SQL
statement such as:

SELECT * FROM Media WHERE MKey IN (SELECT MKey FROM MediaItem WHERE
IState = ? GROUP BY MKey)

takes approximately 3 minutes to execute per isc_dsql_fetch(…) call?

In this SQL statement, Media.MKey is a primary key so it's auto
indexed, MediaItem.Meky is a foreign key and I have created an ASC
index on IState. What I'm seeing is extremely slow performance by
the ibserver.exe in executing this sort of query. When this query is
executed the ibserver.exe will spike at 98% CPU utilization and
remain in this state for nearly 3 minutes before returning the
results to the client. Each table, Media and MediaItem has 1321 rows.

Here is what the statistics are:

Execution Time: 00:03:22:0488
Prepare Time: 00:00:00:0010
Starting Memory: 9200186
Current Memory: 9221676
Delta Memory: 21490
Number of Buffers: 2048
Reads: 291
Writes: 13
Plan: PLAN (MEDIAITEM ORDER RDB$FOREIGN11) PLAN (MEDIA NAUTRAL)
Records Fetched: 1064

This is but one example. Overall, I'm having this sort of problem
with several of my queries. I find it disturbing that this query
takes 3 minutes and that the ibserver.exe uses 98% CPU. I have other
queries that take 6 minutes.

Obviously, I have rewritten some of my queries and found better
performance but what I need to understand is why any query,
regardless of how complex, should take more that a few milliseconds
to seconds when there is only 1300 rows in the db??

This is an out of the box install, FB 1.0 build 796 on Win2k, is
there anything that I can do, e.g., config wise, to get the engine to
run faster?

Best regards,
Dan Crea