Hello and thanks to Svein and Andrew:

I have replaced my original statement and based on your suggestions,
I'm now able to get a result set in a matter of milliseconds verses
minutes. Huge difference!

I agree with Svein's assessment about the subselects, I have seen the
same sort of behavior where it appears that the subselect is being
executed once for each potential row, very costly.

I do have one question however, Andrew, you suggested increasing the
number of buffers. Can you explain how to do this and what this will
do?

Thanks again for the help
Dan


--- In ib-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@k...> wrote:

> 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)