Hello Sean:

Thanks for the information. Just for the record, I would not
consider myself an expert on SQL, but just an average SQL user.
Having said that, this example is not something that I'm actually
using but just a query I tried that demonstrated a general behavior
that I'm seeing. (I was unaware of the need to specify a JOIN)

A query that I "was" using:
SELECT *
FROM Media
WHERE MKey IN (SELECT MKey FROM MediaItem WHERE IState = 5 GROUP BY
Key

also took a long time, about 4 minutes to execute and cause
ibserver.exe to run at the 100%cpu as well.

I guess the question that I might ask is am I just writing bad SQL?
I have re-written this query and found 100% better performance but
what I'm finding is that I need to re-write a lot of my queries for
the same reason.

What I'm trying to find out is am I just writing shit code or do I
need to tune the engine in some way?

Thanks again,
Dan Crea

--- In ib-support@yahoogroups.com, "Leyne, Sean" <sleyne@a...> wrote:
> Dan,
>
> > For example:
> > SELECT OutboundMedia.MKey, OutboundMedia.DestinationDomain
> > FROM OutboundMedia, MediaItem
> > WHERE MediaItem.IState = 5
> > GROUP BY OutboundMedia.MKey, OutboundMedia.DestinationDomain
> ...
> > 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??
>
> You haven't told the engine what the relationship is between
> OutboundMedia and MediaItem
>
> Your should restart the query as:
>
>
> SELECT OutboundMedia.MKey, OutboundMedia.DestinationDomain
> FROM OutboundMedia
> join MediaItem on MediaItem.xxx = OutboundMedia.yyyy
> WHERE MediaItem.IState = 5
> GROUP BY OutboundMedia.MKey, OutboundMedia.DestinationDomain
>
> --
> Sean Leyne
>
> Support Firebird, join FirebirdSQL Foundation today!
> http://www.FirebirdSQL.org/foundation