The plan is similar, only reversed.

Performant query:
PLAN JOIN (COMMISSIONS NATURAL, TRANSACTIONS NATURAL)

Non-performant query:
PLAN JOIN (TRANSACTIONS NATURAL, COMMISSIONS NATURAL)


There is no physical relationship between these tables as they were migrated from Paradox.
There is, however, a logical relationship where each commission record will have one or more transactions.

With that in mind and your education on the plan doing a lookup on the right side, I can understand why it might be slower having the detail table on the left.

Thanks - I now what to watch for and have some code to review with similar situations.


P.S. I think, too, there's room for improvement by adding specific indexes. I thought the primary key would automatically provide an index, but according to these plans using NATURAL, it looks like that isn't the case.

--- In firebird-support@yahoogroups.com, "unordained" <unordained_00@...> wrote:
>
> ---------- Original Message -----------
> From: "brian.matchey" <brian.matchey@...>
> > I have a problem where a select query works on one user's data but not
> > another's. (Firebird server 2.5.1.26351) Strangely, if I reverse the
> > join and from statements, the query works for both – why?
> >
> > This query fails for one user: (by fail, I mean it never returns)
> > Select
> > TRANSACTIONS.INVOICENUMBER,
> > TRANSACTIONS.ITEMNUMBER
> > >From TRANSACTIONS
> > LEFT JOIN COMMISSIONS ON
> > (COMMISSIONS.INVOICENUMBER=TRANSACTIONS.INVOICENUMBER) AND
> > (COMMISSIONS.ITEMNUMBER=TRANSACTIONS.ITEMNUMBER) WHERE
> > (Commissions.Status IS NOT NULL) AND (Commissions.InvoiceNumber = '-2')
> >
> > This one works for that same user:
> > Select
> > TRANSACTIONS.INVOICENUMBER,
> > TRANSACTIONS.ITEMNUMBER
> > >From COMMISSIONS
> > LEFT JOIN TRANSACTIONS ON
> > (TRANSACTIONS.INVOICENUMBER=COMMISSIONS.INVOICENUMBER) AND
> > (TRANSACTIONS.ITEMNUMBER=COMMISSIONS.ITEMNUMBER) WHERE
> > (Commissions.Status IS NOT NULL) AND (Commissions.InvoiceNumber = '-2')
> ------- End of Original Message -------
>
> In Firebird, a LEFT JOIN forces the optimizer into a particular PLAN -- starting
> with the "left" side, then doing lookups against the "right" side. By switching
> the direction of the join, besides the logical issues involved (one-to-many or
> one-to-one? it matters here!) which changes the number and content of rows
> returned, it also changes the physical approach taken by the server, changing
> which index it primarily navigates, etc. -- which makes a big difference in
> performance.
>
> Could you post the PLANs for each of these statements? I'm pretty sure we'll see
> a significant difference in the indexing involved.
>
> -Philip
>