sql-server – 在仅使用文字值的WHERE子句中替换ISNULL()的不同

这不是什么：

这不是关于接受用户输入或使用变量的catch-all queries的问题.

这严格来说,在WHERE子句中使用ISNULL()将NULL值替换为canary值以与谓词进行比较,以及在SQL Server中将这些查询重写为SARGable的不同方法.

你为什么不在那边坐？

我们的示例查询针对SQL Server 2016上的Stack Overflow数据库的本地副本,并查找具有NULL年龄或年龄

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE ISNULL(u.Age,17) < 18;

查询计划显示扫描非常周到的非聚簇索引.

扫描运算符显示(由于在更新版本的SQL Server中添加了实际执行计划XML),我们读取了每个stinkin’行.

总的来说,我们执行9157次读取并使用大约半秒的CPU时间：

Table 'Users'. Scan count 1,logical reads 9157,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 = 485 ms,elapsed time = 483 ms.

问题是：
有什么方法可以重写这个查询以使其更有效,甚至可能是SARGable？

随意提供其他建议.我认为我的答案不一定是答案,并且有足够的聪明人在那里提出可能更好的替代方案.

如果你想在自己的电脑上玩,请到这里download the SO database.

谢谢！

解决方法

有多种方法可以使用不同的T-SQL结构重写它.我们将看看利弊,并在下面进行整体比较.

首先：使用OR

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE u.Age < 18
OR u.Age IS NULL;

使用OR为我们提供了一个更有效的Seek计划,该计划读取了我们需要的确切行数,但是它增加了技术界称为查询计划的一整套malarkey.

另请注意,Seek在这里执行了两次,从图形操作符开始真的应该更明显：

Table 'Users'. Scan count 2,logical reads 8233,lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 469 ms,elapsed time = 473 ms.

第二个：使用带UNION ALL的派生表
我们的查询也可以像这样重写

SELECT SUM(Records)
FROM 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records);

这产生了相同类型的计划,更少的malarkey,以及关于索引被寻求(寻求？)的次数的更明显的诚实程度.

它与OR查询执行相同数量的读取(8233),但削减大约100ms的CPU时间.

CPU time = 313 ms,elapsed time = 315 ms.

但是,你必须非常小心,因为如果这个计划试图并行,那么两个单独的COUNT操作将被序列化,因为它们都被认为是全局标量聚合.如果我们使用Trace Flag 8649强制执行并行计划,问题就变得很明显了.

SELECT SUM(Records)
FROM 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)
OPTION(QUERYTRACEON 8649);

通过稍微更改我们的查询可以避免这种情况.

SELECT SUM(Records)
FROM 
(
    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)   
OPTION(QUERYTRACEON 8649);

现在,执行Seek的两个节点都完全并行化,直到我们到达连接运算符.

对于它的价值,完全并行版本有一些很好的好处.以大约100多次读取和大约90ms的额外CPU时间为代价,经过的时间缩短到93ms.

Table 'Users'. Scan count 12,logical reads 8317,lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 500 ms,elapsed time = 93 ms.

CROSS APPLY怎么样？
没有CROSS APPLY的魔力,没有答案是完整的！

不幸的是,我们遇到了COUNT的更多问题.

SELECT SUM(Records)
FROM dbo.Users AS u 
CROSS APPLY 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u2 
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u2 
    WHERE u2.Id = u.Id 
    AND u2.Age IS NULL
) x (Records);

这个计划太可怕了.当你最后出现在圣帕特里克节时,这是你最终得到的计划.虽然很平行,但出于某种原因,它正在扫描PK / CX. EW.该计划的成本为2198美元.

Table 'Users'. Scan count 7,logical reads 31676233,lob read-ahead reads 0.
Table 'Worktable'. Scan count 0,logical reads 0,lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 29532 ms,elapsed time = 5828 ms.

这是一个奇怪的选择,因为如果我们强制它使用非聚集索引,那么成本会显着下降到1798个查询.

SELECT SUM(Records)
FROM dbo.Users AS u 
CROSS APPLY 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u2 WITH (INDEX(ix_Id_Age))
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u2 WITH (INDEX(ix_Id_Age))
    WHERE u2.Id = u.Id 
    AND u2.Age IS NULL
) x (Records);

嘿,寻求！在那边检查你.另请注意,凭借CROSS APPLY的魔力,我们不需要做任何愚蠢的事情来制定一个大致完全平行的计划.

Table 'Users'. Scan count 5277838,logical reads 31685303,lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 27625 ms,elapsed time = 4909 ms.

如果没有COUNT的东西,交叉申请最终会更好.

SELECT SUM(Records)
FROM dbo.Users AS u
CROSS APPLY 
(
    SELECT 1
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id 
    AND u2.Age IS NULL
) x (Records);

该计划看起来不错,但读取和CPU并不是一种改进.

（编辑：应用网_丽江站长网）

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