SQL语法提示工具SQL Prompt教程：使用EXISTS或COUNT查找相关行（下）

SQL Prompt根据数据库的对象名称、语法和代码片段自动进行检索，为用户提供合适的代码选择。自动脚本设置使代码简单易读--当开发者不大熟悉脚本时尤其有用。SQL Prompt安装即可使用，能大幅提高编码效率。此外，用户还可根据需要进行自定义，使之以预想的方式工作。

点击下载SQL Prompt免费版

本文是教程使用EXISTS或COUNT查找相关行的下半部分，内容紧接前文（点此查看），本文描述了“EXISTS或COUNT（或其他）哪个更好？”和“代码分析规则的注意事项和误报”。

查询执行统计数据

查看单个查询的计时和其他执行统计信息的最简单方法是使用STATISTICS IO/ TIME，如下所示（尽管STATISTICS IO在某些情况下会带来大量开销，您可能更喜欢使用扩展事件）。

SET STATISTICS IO, TIME ON; --turn on io and time stats
--clear the procedure cache for the WideWorldImporters DB
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
-- Query with EXISTS
-- Query with COUNT(*)
-- Query with DISTINCT

将每个查询执行几次，以获取仅与执行计划有关的统计信息（而不是编译计划或将数据缓存到内存）。我还建议单独运行每个语句，否则时间统计信息可能会增加或丢失查询执行过程中的一些开销。

我不会在这里列出统计数据，但是我没有看到任何查询变化在运行时间上的显着差异，对于COUNT和EXISTS查询，包括逻辑读取（来自内存）和物理读取（来自磁盘）内部的执行统计数据是相同的。

在更有趣的情况下，您可能会发现可读性较差的解决方案使用更少的CPU，更少的I / O（内存和磁盘）并且花费更少的时间。那么问题就变成了：是否花了更少的时间来实施一个更神秘的方式来回答这个问题呢？这个决定并不总是那么简单。如果查询每天运行一百万次，那么节省几毫秒是值得的。如果它每天运行一次，那么节省10秒几乎是绝对不值得的，尤其是如果它意味着没有人可以理解代码的工作原理时。

在这种情况下，值得简要考虑一下为什么COUNT和EXISTS之间没有性能差异。 这似乎令人惊讶，因为从逻辑上讲，很容易解释为什么EXISTS解决方案可能会更快，因为它一找到第一个客户就停止寻找客户的匹配订单。 如前所述，COUNT解决方案以及其他书面解决方案会为购买的每个人处理所有订单行，然后拒绝那些不符合“大于没有订单”标准的购买500件以上的产品。

执行计划揭示了答案。

执行计划

请注意，WideWorldImporters其中包括我暂时禁用的FilterCustomersBySalesTerritoryRole 安全策略，以免使计划复杂化。只有在开发中才能做到这一点！

ALTER SECURITY POLICY Application.FilterCustomersBySalesTerritoryRole WITH (STATE = OFF);

在我的测试中，COUNT和EXISTS查询的执行计划相同，如图2所示。

图2

在几乎每个查询示例中，您都可能会发现EXISTS和COUNT查询具有相同的计划。尽管从逻辑上讲前者效率更高，但实际上查询优化器通常可以将查询重写为性能更好的数学等效版本，而且实际上，只要可能，它都会以相同的方式对待这两个变体，因此计划和性能是相同的。

然而，随着查询复杂度的增加，优化器可能无法始终发挥其魔力，因此您可能仍会看到某些情况下，该COUNT变体的确变慢了，并且可读性较差。就是说，我在最大为数百GB的表上测试了这些查询的更复杂版本（尽管仍然具有相同的谓词条件），但仍然没有发现任何差异。

但是，如果将谓词条件更改为“大于或等于零”，我确实发现了微小的差异。例如，对于COUNT(*)查询：

........AND  OrderLines.UnitPrice > 500) >= 0;

从数学上讲，此查询必须返回数据。但是，该COUNT查询计划包括一些额外的运算符。一个哈希匹配（聚合）操作，来计算COUNT(*)值，计算标量，并且一个过滤器以过滤掉的行，其中COUNT(*) = 0。总体而言，它们只占该查询工作量的不到2％。

图3

最后，我不会在这里显示它，但是DISTINCT查询计划表明它是成本较高的实现，占预期成本的34％，其他两个成本分别为33％。DISTINCT查询版本中的额外费用主要是需要进行排序以实现不同的运算符才能删除重复的值。

先前的查询使用Customers和之间Orders的半联接（半联接实现为相关子查询，本质上是联接到表，但Orders在这种情况下不从一个输入（表）返回任何行），在这里我们得到一个JOIN将在处理过程中将数据添加到集合中的数据，导致使用的内存稍大。所产生的性能差异仍然可以忽略不计，但是这种方法可能无法很好地扩展到大数据量。

点击下载SQL Prompt免费版

代码分析规则的注意事项和误报

代码分析中有趣的事情之一是，如果我按如下方式编写查询，使用一个变量作为COUNT过滤器，结果将是正确的，我没有看到PE013警告，但我确实得到了较差的计划（在图3中）。

DECLARE @countvalue int = 0;
SELECT People.FullName, People.EmailAddress
FROM   Sales.Customers
        JOIN Application.People
         ON People.PersonID = Customers.PrimaryContactPersonID
WHERE  People.EmailAddress IS NOT NULL 
  AND  (SELECT COUNT(1)
        FROM   Sales.Orders
                JOIN Sales.OrderLines
                     ON OrderLines.OrderID = Orders.OrderID
        WHERE  Orders.CustomerID = Customers.CustomerID
          AND  OrderLines.UnitPrice >= 500) > @countvalue;

显然，如果的值@countvalue始终设置为文字值，则这不是编写查询的理想方法，因为它向优化器引入了看起来像变量值的变量，从而使该优化器选择了允许@countvalue（尤其是如果您只是为了避免代码分析警告而使您的团队代码审查变得更容易的话）。

如果@countvalue的值是一个允许查询执行的操作而不是简单的操作或存在一行，那么此技术是提供答案的最佳方法，例如“给我所有客户的邮件已经命令2个或更多的项目单价大于500”通过设置@countvalue变量2。然后要求5或更多是一个简单的参数改变。

如果您想知道是否使用COUNT（1）而不是COUNT（*）会对性能产生任何影响：标量表达式的含义没有什么不同，除非它在表达式中包含可能导致其评估为NULL的列。没有列引用的标量值不会被评估，即使它像1/0一样是无用的：

SELECT COUNT(1/0) FROM Sales.Orders;

这将返回73595，而不是您期望的除以零。任何标量表达式都会被忽略并计数。

最后，请注意，静态代码分析将规则基于幅度布尔表达式中的0。使用以下任何命令结束COUNT（*）查询都将导致相同的警报，即使它们具有非常不同的含义，因此也是该规则的误报，尽管以下示例均不是解决问题的理想方法。

--First two are equivalent to NOT EXISTS
  AND  OrderLines.UnitPrice >= 500) = 0;
  AND  OrderLines.UnitPrice >= 500) <= 0;
--Nonsense, COUNT cannot be < 0
  AND  OrderLines.UnitPrice >= 500) < 0;
--Equivalent to EXISTS
  AND  OrderLines.UnitPrice >= 500) > 0;
--Will always be true
  AND  OrderLines.UnitPrice >= 500) >= 0;

结论

为了根据至少必须存在一行的条件查找相关行，很明显，使用EXISTS是最合适的解决方案。它可读性强，可以直接，简单地回答问题，并且在任何替代解决方案中的执行效果至少相同。尽管我无法检测到使用EXISTS而不是COUNT所带来的性能优势，但是可读性足以确保认真对待代码分析规则PE013（为了您自己和其他程序员）。

本教程内容到这里就结束啦，您可以点击下方相关链接查看该教程上半部分的内容哦~您也可以下载SQL Prompt试用版评估一下~

相关内容推荐：

SQL语法提示工具SQL Prompt教程：使用EXISTS或COUNT查找相关行（上）

SQL Prompt使用教程>>>

想要购买SQL Prompt正版授权，或了解更多产品信息请点击