Not always but I observed simple join is much better than Correlated
sub-query. So plz avoid to use it, if we have option than ....today i am sharing how a simple join is better than correlated sub query..
correlated sub-query in columns
select
candidate_id,last_name,first_name,client_id, (select name from client_master where
client_id=candidate_master.client_id) as cl_name
from
candidate_master
Output
(291 row(s)
affected)
Stats
Table
'candidate_master'. Scan count 1, logical reads 16, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead
reads 0.
Table
'client_master'. Scan count 1, logical reads 5, physical reads 0, read-ahead
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 107 ms.
Left outer join
SELECT candidate_master.Candidate_ID, candidate_master.last_name,
candidate_master.first_name, candidate_master.Client_ID, client_master.name
FROM candidate_master LEFT OUTER JOIN
client_master ON candidate_master.Client_ID
= client_master.Client_ID
Output
(291 row(s)
affected)
Stats
Table
'candidate_master'. Scan count 1, logical reads 16, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead
reads 0.
Table
'client_master'. Scan count 1, logical reads 5, physical reads 0, read-ahead
reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 70 ms.
comparison show how left outer is much better than correlated sub-query. So plz try to avoid this if we have option in simple way.
If you liked this post, do like my page on https://www.facebook.com/pages/Sql-DBAcoin/523110684456757
good explanation, how can I get these stats to compare?
ReplyDeleteThanks for comments. You can use 'Set Statistics IO On' in any query session.. stats will show on print tab
ReplyDeleteHowdy! Someone in my Facebook group shared this site with us
ReplyDeleteso I came to take a look. I'm definitely enjoying the information. I'm book-marking and will be tweeting this to my followers!
Outstanding blog and fantastic design.