sql - Not Exists vs Not In: efficiency -


i've been under assumption not exists way go instead of using not in condition. however, doing comparison on query i've been using, noticed execution not in condition appears faster. insight why case, or if i've made horrible assumption until point, appreciated!

query 1:

select distinct  a.sfaccountid, a.slxid, a.name [dbo].[salesforce_accounts] with(nolock) join  _slx_accountchannel b with(nolock) on a.slxid = b.accountid join [dbo].[salesforce_contacts] c with(nolock) on a.sfaccountid = c.sfaccountid b.status in ('active','customer', 'current') , c.primary__c = 0 , not exists ( select 1 [dbo].[salesforce_contacts] c2 with(nolock) a.sfaccountid = c2.sfaccountid , c2.primary__c = 1 ); 

query 2:

select    distinct a.sfaccountid [dbo].[salesforce_accounts] with(nolock) join  _slx_accountchannel b with(nolock) on a.slxid = b.accountid join [dbo].[salesforce_contacts] c with(nolock)  on a.sfaccountid = c.sfaccountid b.status in ('active','customer', 'current') , c.primary__c = 0 , a.sfaccountid not in (select sfaccountid [dbo].[salesforce_contacts] primary__c = 1 , sfaccountid not null); 

actual execution plan query 1: execution plan 1

actual execution plan query 2:execution plan 2

time/io statistics:

query #1 (using not exists):

sql server parse , compile time:     cpu time = 0 ms, elapsed time = 0 ms.  sql server execution times:    cpu time = 0 ms,  elapsed time = 0 ms. sql server parse , compile time:     cpu time = 532 ms, elapsed time = 533 ms. table 'worktable'. scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'salesforce_contacts'. scan count 2, logical reads 3078, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'information'. scan count 1, logical reads 691, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'account'. scan count 4, logical reads 567, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'salesforce_accounts'. scan count 1, logical reads 680, 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 = 250 ms,  elapsed time = 271 ms. sql server parse , compile time:     cpu time = 0 ms, elapsed time = 0 ms.  sql server execution times:    cpu time = 0 ms,  elapsed time = 0 ms. 

query #2 (using not in):

sql server parse , compile time:     cpu time = 0 ms, elapsed time = 0 ms.  sql server execution times:    cpu time = 0 ms,  elapsed time = 0 ms. sql server parse , compile time:     cpu time = 500 ms, elapsed time = 500 ms. table 'worktable'. scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'salesforce_contacts'. scan count 2, logical reads 3079, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'information'. scan count 1, logical reads 691, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'account'. scan count 4, logical reads 567, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'salesforce_accounts'. scan count 1, logical reads 680, 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 = 157 ms,  elapsed time = 166 ms. sql server parse , compile time:     cpu time = 0 ms, elapsed time = 0 ms.  sql server execution times:    cpu time = 0 ms,  elapsed time = 0 ms. 

try

select distinct a.sfaccountid, a.slxid, a.name    [dbo].[salesforce_accounts] with(nolock)   join _slx_accountchannel b with(nolock)     on a.slxid = b.accountid    , b.status in ('active','customer', 'current')   join [dbo].[salesforce_contacts] c with(nolock)     on a.sfaccountid = c.sfaccountid     , c.primary__c = 0   left join [dbo].[salesforce_contacts] c2 with(nolock)      on c2.sfaccountid = a.sfaccountid    , c2.primary__c = 1  c2.sfaccountid null  

Comments