我一直认为不存在是不可能的,而不是使用不处于条件状态.但是,我对我一直在使用的查询进行了比较,我注意到Not In条件的执行实际上看起来更快.任何洞察为什么会出现这种情况,或者如果我在此之前做出一个可怕的假设,我将不胜感激！
问题1：
SELECT DISTINCT
a.SFAccountID,a.SLXID,a.Name FROM [dbo].[Salesforce_Accounts] a 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
WHERE b.STATUS IN ('Active','Customer','Current')
AND c.Primary__C = 0
AND NOT EXISTS
(
SELECT 1 FROM [dbo].[Salesforce_Contacts] c2 WITH(NOLOCK)
WHERE a.SFAccountID = c2.SFAccountID
AND c2.Primary__c = 1
);
问题2：
SELECT
DISTINCT
a.SFAccountID FROM [dbo].[Salesforce_Accounts] a 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
WHERE b.STATUS IN ('Active','Current')
AND c.Primary__C = 0
AND a.SFAccountID NOT IN (SELECT SFAccountID FROM [dbo].[Salesforce_Contacts] WHERE Primary__c = 1 AND SFAccountID IS NOT NULL);
查询1的实际执行计划：
查询2的实际执行计划：
时间/ IO统计：
查询#1(使用不存在)：
sql Server parse and 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 and 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,lob read-ahead reads 0. Table 'INFORMATION'. Scan count 1,logical reads 691,lob read-ahead reads 0. Table 'ACCOUNT'. Scan count 4,logical reads 567,lob read-ahead reads 0. Table 'Salesforce_Accounts'. Scan count 1,logical reads 680,lob read-ahead reads 0. sql Server Execution Times: cpu time = 250 ms,elapsed time = 271 ms. sql Server parse and compile time: cpu time = 0 ms,elapsed time = 0 ms.
查询#2(使用Not In)：
sql Server parse and compile time: cpu time = 0 ms,elapsed time = 0 ms. sql Server parse and compile time: cpu time = 500 ms,elapsed time = 500 ms. Table 'Worktable'. Scan count 0,logical reads 3079,lob read-ahead reads 0. sql Server Execution Times: cpu time = 157 ms,elapsed time = 166 ms. sql Server parse and compile time: cpu time = 0 ms,elapsed time = 0 ms.
解决方法
尝试
SELECT DISTINCT a.SFAccountID,a.Name
FROM [dbo].[Salesforce_Accounts] a WITH(NOLOCK)
JOIN _SLX_AccountChannel b WITH(NOLOCK)
ON a.SLXID = b.ACCOUNTID
AND b.STATUS IN ('Active','Current')
JOIN [dbo].[Salesforce_Contacts] c WITH(NOLOCK)
ON a.SFAccountID = c.SFAccountID
AND c.Primary__C = 0
LEFT JOIN [dbo].[Salesforce_Contacts] c2 WITH(NOLOCK)
on c2.SFAccountID = a.SFAccountID
AND c2.Primary__c = 1
WHERE c2.SFAccountID is null
