On Fri, Jul 6, 2012 at 10:44 AM, brian.matchey <brian.matchey@...>wrote:

>
> 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')

Neither of your queries should be an outer (aka Left) join. You'll get
better performance if you use an inner join, generally because it gives the
optimizer more options in selecting a join order. The difference between
an inner and an outer join is that the inner join returns only pairs of
rows that match on the join condition (ON clause). An outer join returns
all rows from the first table in a left outer join, paired the rows that
match from the second table. But the WHERE clause filters those results,
eliminating return values that don't match the conditions in the WHERE. No
record from TRANSACTIONS will pass the WHERE clause with null values for
the columns from the COMMISSIONS table.

Put an index on commissions.InvoiceNumber and transactions.InvoiceNumber
(assuming that it is somewhat selective, and write your query like this:

Select t.invoiceNumber, t.itemNumber
from transactions t
inner join commissions c
on (c.invoiceNumber = t.invoiceNumber) and (c.itemNumber
= t.itemNumber)
where (c.Status IS NOT NULL)
and (c.InvoiceNumber = '-2')

Good luck,

Ann


[Non-text portions of this message have been removed]