主页 / dba / 问题 / 127639
Accepted
Kenneth Fisher
Asked: 2016-01-29 10:40:44 +0800 CST

为什么添加 TOP 1 会显着降低性能？

  • 772

我有一个相当简单的查询

SELECT TOP 1 dc.DOCUMENT_ID,
        dc.COPIES,
        dc.REQUESTOR,
        dc.D_ID,
        cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
JOIN CORRESPONDENCE_JOURNAL cj
    ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
WHERE dc.QUEUE_DATE <= GETDATE()
  AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER

这给了我可怕的表现（就像从不费心等待它完成一样）。查询计划如下所示：

在此处输入图像描述

但是，如果我删除它，TOP 1我会得到一个看起来像这样的计划，它会在 1-2 秒内运行：

在此处输入图像描述

正确的 PK 和索引如下。

更改查询计划这一事实TOP 1并不让我感到惊讶，我只是有点惊讶它使它变得更糟。

注意：我已经阅读了这篇文章的结果并理解了 aRow Goal等的概念。我很好奇的是如何更改查询以便它使用更好的计划。目前我正在将数据转储到临时表中，然后将第一行从中拉出。我想知道是否有更好的方法。

编辑对于事后阅读本文的人来说，这里有一些额外的信息。

  • Document_Queue - PK/CI 是 D_ID，它有大约 5k 行。
  • Correspondence_Journal - PK/CI 是 FILE_NUMBER、CORRESPONDENCE_ID，它有大约 140 万行。

当我开始时没有其他索引。我最终在 Correspondence_Journal (Document_Id, File_Number) 上找到了一个

sql-server sql-server-2008-r2

6 个回答

  1. 既然你得到了正确的计划ORDER BY，也许你可以推出自己的TOP运营商？

    SELECT DOCUMENT_ID, COPIES, REQUESTOR, D_ID, FILE_NUMBER
FROM (
    SELECT dc.DOCUMENT_ID,
           dc.COPIES,
           dc.REQUESTOR,
           dc.D_ID,
           cj.FILE_NUMBER,
           ROW_NUMBER() OVER (ORDER BY cj.FILE_NUMBER) AS _rownum
    FROM DOCUMENT_QUEUE dc
    INNER JOIN CORRESPONDENCE_JOURNAL cj
        ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
    WHERE dc.QUEUE_DATE <= GETDATE()
      AND dc.PRINT_LOCATION = 2
) AS sub
WHERE _rownum=1;

    在我看来，上面的查询计划ROW_NUMBER()应该和你有一个ORDER BY. 查询计划现在应该有一个 Segment、Sequence Project 和最后一个 Filter 运算符，其余的应该看起来就像你的好计划一样。

    • 30
  2. Best Answer

    尝试强制哈希连接* 

    SELECT TOP 1 
       dc.DOCUMENT_ID,
       dc.COPIES,
       dc.REQUESTOR,
       dc.D_ID,
       cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
INNER HASH JOIN CORRESPONDENCE_JOURNAL cj
        ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
       AND dc.QUEUE_DATE <= GETDATE()
       AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER

    优化器可能认为循环使用 top 1 会更好，这是有道理的，但实际上它在这里不起作用。这里只是一个猜测，但可能该阀芯的估计成本已关闭 - 它使用 TEMPDB - 您可能有一个性能不佳的 TEMPDB。

    * 小心连接提示，因为它们强制计划表访问顺序与查询中表的写入顺序相匹配（就像OPTION (FORCE ORDER)已指定一样）。从文档链接：

    BOL 提取物

    这在示例中可能不会产生任何不良影响，但总的来说，它很有可能。FORCE ORDER（隐含的或显式的）是一个非常强大的提示，超出了执行命令的范围；它阻止应用广泛的优化器技术，包括部分聚合和重新排序。

    在合适的情况下，OPTION (HASH JOIN) 查询提示的侵入性可能较小，因为这并不意味着FORCE ORDER. 但是，它确实适用于查询中的所有联接。其他解决方案可用。

    • 29

  3. 编辑：+1 在这种情况下有效，因为事实证明这FILE_NUMBER是整数的零填充字符串版本。对于字符串，一个更好的解决方案是追加''（空字符串），因为追加一个值会影响顺序，或者对于数字添加一些常量但包含非确定性函数的东西，例如sign(rand()+1). “打破排序”的想法在这里仍然有效，只是我的方法并不理想。

    +1

    不，我不是说我同意任何事情，我的意思是作为一种解决方案。如果您将查询更改为ORDER BY cj.FILE_NUMBER + 1，则TOP 1行为会有所不同。

    您会看到，为有序查询设置了小行目标，系统将尝试按顺序使用数据，以避免使用排序运算符。它还将避免构建哈希表，因为它可能不需要做太多工作来找到第一行。在您的情况下，这是错误的 - 从这些箭头的粗细来看，看起来它必须消耗大量数据才能找到单个匹配项。

    这些箭头的粗细表明您的DOCUMENT_QUEUE(DQ) 表比您的CORRESPONDENCE_JOURNAL(CJ) 表小得多。最好的计划实际上是检查 DQ 行，直到找到 CJ 行。事实上，如果查询优化器 (QO) 没有这个讨厌ORDER BY的东西，这就是它会做的事情，CJ 上的覆盖索引很好地支持了这一点。

    所以如果你ORDER BY完全放弃了，我希望你会得到一个涉及嵌套循环的计划，迭代 DQ 中的行，寻找 CJ 以确保该行存在。并且TOP 1，在拉出单行后，这将停止。

    但是，如果您确实需要按FILE_NUMBER顺序排列第一行，那么您可以通过这样做欺骗系统忽略似乎（错误地）如此有用的索引ORDER BY CJ.FILE_NUMBER+1- 我们知道这将保持与以前相同的顺序，但重要的是 QO没有。QO 将专注于获得整个设置，以便满足 Top N 排序运算符。这个方法应该产生一个计划，其中包含一个计算标量运算符来计算排序值，以及一个前 N 个排序运算符来获取第一行。但在这些右侧，您应该会看到一个不错的嵌套循环，在 CJ 上执行了很多 Seek。并且比运行与 DQ 中的任何内容都不匹配的大型行表具有更好的性能。

    哈希匹配不一定很糟糕，但是如果您从 DQ 返回的行集比 CJ 小得多（正如我所期望的那样），那么哈希匹配将扫描更多的 CJ比它需要的。

    注意：我使用 +1 而不是 +0，因为查询优化器可能会识别出 +0 没有任何改变。当然，同样的事情可能适用于 +1，如果不是现在，那么在未来的某个时候。

    • 29

  4. 我已经阅读了这篇文章的结果并理解了行目标等的概念。我很好奇的是如何更改查询以便它使用更好的计划

    添加OPTION (QUERYTRACEON 4138)仅关闭该查询的行目标的效果，而不会对最终计划过于规范，并且可能是最简单/最直接的方法。

    如果添加此提示会给您带来权限错误（对于 是必需的DBCC TRACEON），您可以使用计划指南应用它：

    QUERYTRACEON在spaghettidba的计划指南中使用

    ...或者只使用存储过程：

    需要什么权限QUERYTRACEON？通过肯德拉小

    • 7

  5. 较新版本的 SQL Server 提供了不同的（并且可以说是更好的）选项来处理当优化器能够应用行目标优化时获得次优性能的查询。SQL Server 2016 SP1 引入了DISABLE_OPTIMIZER_ROWGOAL使用提示，它与跟踪标志 4138 具有相同的效果。（有关如何使用它的示例，请参阅此博客文章。）

    如果您不在该版本上，您还可以考虑使用OPTIMIZE FOR查询提示来获取旨在返回所有行而不是仅 1 的查询计划。下面的查询将返回与问题中的结果相同的结果，但不会以仅获得 1 行为目标而创建。

    DECLARE @top INT = 1;

SELECT TOP (@top) dc.DOCUMENT_ID,
        dc.COPIES,
        dc.REQUESTOR,
        dc.D_ID,
        cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
JOIN CORRESPONDENCE_JOURNAL cj
    ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
WHERE dc.QUEUE_DATE <= GETDATE()
  AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER
OPTION (OPTIMIZE FOR (@top = 987654321));
    • 3

  6. 由于您正在执行TOP(1)，我建议您ORDER BY首先确定确定性。至少这将确保结果在功能上是可预测的（对于回归测试总是有用的）。看起来您需要为此添加DC.D_ID和CJ.CORRESPONDENCE_ID。

    在查看查询计划时，我有时会发现简化查询很有帮助：可能提前将所有相关的 dc 行选择到一个临时表中，以消除在QUEUE_DATE和上的基数估计问题PRINT_LOCATION。考虑到低行数，这应该很快。然后，您可以在必要时向该临时表添加索引，而无需更改永久表。

    • 2

相关问题