July 19, 2011 at 2:11 am
Hi ,
I prepared same query in following 3 ways
1) using NOT IN
2) using LEFT JOIN WITH NULL
3) using NOT EXISTS
then the Query cost is same i.e., 33%,33%,33%
at last Performance wise which method i should use in Real Time.
Need Suggestions .
Thanks ,
July 19, 2011 at 4:10 am
can you paste in your queires so we can take a look please
***The first step is always the hardest *******
July 19, 2011 at 5:07 am
Keep in mind that NOT IN is not 100% equivalent to the other 2. In you have a null in the in list the results will be different.
Moreover left join allows you to get columns from the outer table while exists and not in don't allow you to do that.
Here's a complete article on the subject :
http://sqlinthewild.co.za/index.php/2010/03/23/left-outer-join-vs-not-exists/
July 20, 2011 at 6:57 am
Also keep in mind that those costs are estimates based on statistics, not actual execution time for your server.
Look at the execution times, the number of scans and the number of reads. Then compare them. That will give you a better measure of performance.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 21, 2011 at 3:52 pm
IRK (7/19/2011)
Hi ,I prepared same query in following 3 ways
1) using NOT IN
2) using LEFT JOIN WITH NULL
3) using NOT EXISTS
then the Query cost is same i.e., 33%,33%,33%
at last Performance wise which method i should use in Real Time.
Need Suggestions .
Thanks ,
See what STATISTICS IO and SET SHOWPLAN_ALL say about it. Also, have three or more sessions of the query running simultaneously against a large rowset to see which performs better with locks and pressure. When in doubt, then use LEFT JOIN.. WHERE.. IS NULL, because it will scale better for very large rowsets.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 21, 2011 at 7:52 pm
Eric M Russell (7/21/2011)
When in doubt, then use LEFT JOIN.. WHERE.. IS NULL, because it will scale better for very large rowsets.
http://sqlinthewild.co.za/index.php/2010/04/27/in-exists-and-join-a-roundup/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2011 at 8:05 am
GilaMonster (7/21/2011)
Eric M Russell (7/21/2011)
When in doubt, then use LEFT JOIN.. WHERE.. IS NULL, because it will scale better for very large rowsets.http://sqlinthewild.co.za/index.php/2010/04/27/in-exists-and-join-a-roundup/
In my experience, a large table is in the order of 10s millions to billions of rows and multi-column foreign key relationships, which is why I virtually always use the OUTER JOIN approach over NOT IN or NOT EXISTS. You're right that it may not apply in this specific case, and your article provides a good overview of how the three options can be compared.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply