Subject Re: [ib-support] Re: Why is Firebird so slow?
Author Helen Borrie
At 07:41 PM 12/02/2003 +0000, you wrote:
>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:
>FROM Media
>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?

If the example is typical then I think you have some work to do to
understand what you are asking for in your query specifications, esp. what
joins do. Your sample is a cross-join which will literally get
exponentially slower for each additional record pair in the left-right
set. It will pull number of left records * number of right records. It
doesn't take many records to get that to a very big number.