sql-server – 在仅使用文字值的WHERE子句中替换ISNULL()的不同方法是什么？

这不是什么：

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

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

你为什么不在那边坐？

我们的示例查询针对sql Server 2016上的Stack Overflow数据库的本地副本,并查找具有NULL年龄或年龄用户. 18.="" <="">

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并不是一种改进.

Table 'Users'. Scan count 20,logical reads 17564,lob read-ahead reads 0.
Table 'Workfile'. Scan count 0,lob read-ahead reads 0.

 sql Server Execution Times:
   cpu time = 4844 ms,elapsed time = 863 ms.

重写交叉适用于派生连接会产生完全相同的一切.我不打算重新发布查询计划和统计信息 – 它们确实没有改变.

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

    UNION ALL

    SELECT u.Id
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x ON x.Id = u.Id;

关系代数：
为了彻底,并让Joe Celko不要困扰我的梦想,我们至少需要尝试一些奇怪的关系.这里没什么’！

尝试使用INTERSECT

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE NOT EXISTS ( SELECT u.Age WHERE u.Age >= 18
                   INTERSECT
                   SELECT u.Age WHERE u.Age IS NOT NULL );

Table 'Users'. Scan count 1,lob read-ahead reads 0.

 sql Server Execution Times:
   cpu time = 1094 ms,elapsed time = 1090 ms.

这是除了以外的尝试

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE NOT EXISTS ( SELECT u.Age WHERE u.Age >= 18
                   EXCEPT
                   SELECT u.Age WHERE u.Age IS NULL);

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

 sql Server Execution Times:
   cpu time = 2126 ms,elapsed time = 376 ms.

可能还有其他方法来编写这些,但我会把这个留给那些可能比我更常使用EXCEPT和INTERSECT的人.

如果你真的需要一个计数
我在查询中使用COUNT作为一些简写(读：我有时懒得想出更多涉及的场景).如果你只需要一个计数,你可以使用CASE表达式做同样的事情.

SELECT SUM(CASE WHEN u.Age < 18 THEN 1
                WHEN u.Age IS NULL THEN 1
                ELSE 0 END) 
FROM dbo.Users AS u

SELECT SUM(CASE WHEN u.Age < 18 OR u.Age IS NULL THEN 1
                ELSE 0 END) 
FROM dbo.Users AS u

它们都具有相同的计划并具有相同的cpu和读取特性.

Table 'Users'. Scan count 1,lob read-ahead reads 0.

 sql Server Execution Times:
   cpu time = 719 ms,elapsed time = 719 ms.

获胜者,冠军？
在我的测试中,在派生表上使用SUM的强制并行计划表现最佳.是的,许多这些查询都可以通过添加一些过滤索引来解决这两个谓词来辅助,但我想给其他人留下一些实验.

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);

谢谢！

原文链接：https://www.f2er.com/mssql/80019.html

sql-server – 在仅使用文字值的WHERE子句中替换ISNULL()的不同方法是什么？

解决方法

猜你在找的MsSQL相关文章