--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

>
> Hi Adam!
>
> > I reduced a query from 3 minutes to about 0.5 seconds just last
> > week, and all I did was switch around the order of the joins and
> > the switched one of them with the from. It reduced the workload
> > from 100000 records to about 3 or 4 records.
>
> Statements like this makes me curious. Are you saying that SELECT *
> FROM A JOIN B is different from SELECT * FROM B JOIN A? Can you

show

> us the "good" and "bad" query with their indexes? I'm just puzzled.
>
> Set

Nope, switching join order for the sake of switching join order
doesn't help your cause but we needed left joins rather than inner
joins.

select *
from A
left join B
where B.testfield = 1

Note: This is incredibly overly simplified. I can't show you the
queries basically because they are built at runtime depending on what
you select on the report filter screen, so you need to run a trace to
get the SQL. But the difference isn't just theoretical, it is
measurably faster now (virtually instant), but this is not my point.

My point was that queries need to be developed from the ground up
taking into account efficiency. It is easier to ensure you have a
good index for the joins you are performing when designing the query.

Adam