June 11, 2015 at 8:34 am
I am curious as to which of my two options would generally perform better when dealing with a table with 5+ million rows. Option 1 is the left join TABLE A to TABLE B on the key and then say WHERE TABLE B's key IS NULL. Option 2 is to have a WHERE clause using NOT EXISTS( select key from TABLE B where B.key = A.key)
From what I understand, using a NOT condition makes SQL unable to use indexes effectively. This would lead me to think that option 2 is not as good as option 1, where the two tables are joined on the index and then filtered. However, option 1 requires a much larger dataset prior to filtering...
Any thoughts?
BTW, I did a search on this topic and found lots of stuff on EXISTS but not a lot on NOT EXISTS.
June 11, 2015 at 8:45 am
NOT EXISTS is generally the more performant, because of the short-circuiting built into that construction.
But you should verify it for yourself by testing and examining execution plans.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 11, 2015 at 8:51 am
Gail Shaw has a series of articles on her blog where she tests this options. This specific test is done here: http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/
However, I would insist on following Phil's advice on doing some testing of your own.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply