Hi Dan!

In general, my impression (though I have no proof for it always being the
case) is that subselects are executed once for every potentially returned
row. Or to say it another way, in your query the subselect 'SELECT MKey
FROM MediaItem WHERE IState = ? GROUP BY MKey' is probably executed 1321 times.

IN is good when your set consists of fixed values, but I always avoid using
IN with subselects. Andrews suggestion about using JOIN is good when it is
appropriate, but sometimes it can result in too many returned rows. If so,
I rewrite the statement to using EXISTS, something which normally is very
fast in Fb/IB. E.g. I would use

SELECT * FROM Media M
WHERE EXISTS (SELECT 1 FROM MediaItem MI where MI.MKey = M.MKey and MI.
IState = ?)

to avoid having to create 1321 identical "temporary subselect result sets".

Having said that, as long as your subselect doesn't contain any references
to your original table I cannot understand why Fb/IB should have to issue
the subselect for every potential row. Using EXISTS with subselects is one
of the pitfalls people repeatedly fall into and at least in theory (I have
no knowledge of the source code, nor have I tried Fb 1.5 to see if anything
is changed in this regard) it should be possible to do things like this
much more efficiently.

Your IState index may or may not be useful (it is useful if you do not have
many records with the same value in that field), but it certainly doesn't
help much when using IN with subselects.

HTH,
Set

- I support Firebird, I am a FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation

At 15:10 07.02.2003 +0000, you wrote:

>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.
>Plan: PLAN (MEDIAITEM ORDER RDB$FOREIGN11) PLAN (MEDIA NAUTRAL)