sql – 不存在vs不存在：效率

发布时间：2020-12-12 06:35:29 所属栏目：MsSql教程来源：网络整理

导读：我一直认为不存在是不可能的,而不是使用不处于条件状态.但是,我对我一直在使用的查询进行了比较,我注意到Not In条件的执行实际上看起来更快.任何洞察为什么会出现这种情况,或者如果我在此之前做出一个可怕的假设,我将不胜感激！问题1： SELECT DISTINCT a.SF

我一直认为不存在是不可能的,而不是使用不处于条件状态.但是,我对我一直在使用的查询进行了比较,我注意到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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!