Luis Cazares (9/24/2012)
I don't have anything to test with, but I believe this could be better.However, the problem is that it's still using a cartesian product to obtain the result.
SELECT TAB1.SalesOrderId AS ID1,TAB1.TotalDue AS DUE1,
TAB2.SalesOrderId AS ID2,
TAB2.TotalDue AS DUE2
FROM SALESTABLE TAB1
JOIN SALESTABLE TAB2 ON TAB1.SalesOrderID < TAB2.SalesOrderID --Avoid half of the rows.
WHERE TAB1.TotalDue < @SPECIFIED_DIFFERENCE --This are useless unless you have negatives.
AND TAB2.TotalDue < @SPECIFIED_DIFFERENCE --Same as above.
AND TAB1.TotalDue + TAB2.Totaldue = @SPECIFIED_DIFFERENCE
I'm not sure if the conditions added will help but I'm sure the condition for the join must help the performance.
What you have here is what I was thinking. If the total due for each order can not be less than zero, then it makes sense to restrict the testing to orders where the total due for each order is less than or equal to the total difference. If you could have a negative total, then this check would not be worthwhile.