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 [email protected], "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 >
