January 21, 2009 at 3:25 pm
Lynn Pettis (1/21/2009)
Dennis D. Allen (1/21/2009)
Greetings Lynn,Yes that does work and is a fine workaround.
I want to understand why this does not work as it should work correctly and the fact that it is not might point to an error in my system that I would like to find and fix. Also, I am trying to learn to read the execution plan better.
Any ideas?
Not that what I gave you is a workaround. I actually think that it is more efficient than using NOT IN. The execution plan showed a nested loop. Think about this for a moment. For each row in Parent, you have to loop through 1,000,000 values returned from Child before knowing if it does not exist in Child. Not very efficient.
Now, the left outer join returns all records from Parent regardless of a matching record in Child, and you then filter on the Child ParentID, which will be null if there was no match to the ParentID from Parent.
Interestingly enough - you may find that the two of them are getting increasingly similar exec plans. The optimizer has been getting some improvements to not cause it to act so correlated sub-like.
It's now down to whether a left join+filter runs faster than a "left anti-join", and that's a lot more of a toss-up. In the case I just tried - the NOT IN was actually faster than the LEFT OUTER JOIN.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 21, 2009 at 3:29 pm
Matt Miller (1/21/2009)
Lynn Pettis (1/21/2009)
Dennis D. Allen (1/21/2009)
Greetings Lynn,Yes that does work and is a fine workaround.
I want to understand why this does not work as it should work correctly and the fact that it is not might point to an error in my system that I would like to find and fix. Also, I am trying to learn to read the execution plan better.
Any ideas?
Not that what I gave you is a workaround. I actually think that it is more efficient than using NOT IN. The execution plan showed a nested loop. Think about this for a moment. For each row in Parent, you have to loop through 1,000,000 values returned from Child before knowing if it does not exist in Child. Not very efficient.
Now, the left outer join returns all records from Parent regardless of a matching record in Child, and you then filter on the Child ParentID, which will be null if there was no match to the ParentID from Parent.
Interestingly enough - you may find that the two of them are getting increasingly similar exec plans. The optimizer has been getting some improvements to not cause it to act so correlated sub-like.
It's now down to whether a left join+filter runs faster than a "left anti-join", and that's a lot more of a toss-up. In the case I just tried - the NOT IN was actually faster than the LEFT OUTER JOIN.
What it may start coming down to is the data itself and database structures (indexes, etc). One method may work better when the datasets or narrow and the other when they are wider. I guess it comes down to the old adage: Test, Test, and Test again. And when you upgrade, do it all again.
January 21, 2009 at 3:35 pm
Luke L (1/21/2009)
And I've been wondering for weeks why we have 2 Vaders running around here, or are you and Perry one in the same?
Hmm, I thought that Perry changed his avatar again.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply