---------- Original Message ----------- From: "brian.matchey" <[email protected]> > 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
