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

我有一个非常简单的查询

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更改了查询计划这一事实并不令我感到惊讶，我对此感到有些惊讶它使情况变得更加糟糕。

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

编辑对于一些在阅读了此事实之后的读者，这里有一些额外的信息。

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

我刚开始时没有其他索引。我最后在Correspondence_Journal（Document_Id，File_Number）上得到一个

您是否有一个外键约束来强制两个表之间建立DOCUMENT_ID关系（或者CORRESPONDENCE_JOURNAL中的每个记录在DOCUMENT_QUEUE中是否都有匹配的记录）？

#1 楼

尝试强制执行散列连接*

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

优化器可能认为循环在前1位时会更好，这种做法很有意义，但实际上它没有用这里。在这里只是个猜测，但也许该线轴的估计成本已关闭-它使用TEMPDB-您的TEMPDB性能可能不佳。

*请谨慎使用连接提示，因为它们会强制计划表访问顺序以匹配查询中表的书面顺序（就像已指定OPTION (FORCE ORDER)一样）。从文档链接：

这可能不会在示例中产生任何不良影响，但总的来说，它可能会产生效果。 FORCE ORDER（隐式或显式）是一个非常强大的提示，它超出了执行顺序；

在适当的情况下，OPTION (HASH JOIN)查询提示可能不会那么麻烦，因为这并不意味着FORCE ORDER。但是，它确实适用于查询中的所有联接。其他解决方案也可用。

看起来像是正确的答案，它与较简单的计划之间的唯一区别是在前面附加了一个排序。

–肯尼斯·费舍尔
16 Jan 28'在19:34

不确定我喜欢这个答案。连接提示非常具有侵入性。首先应该尝试一些简单的索引更改，例如date列上的索引。

–usr
16年1月29日在13:04

@usr这是一个简单的PK连接，运行时间不到一秒钟。相当安全的选择。

–狗仔队
16年1月29日在15:50

在强制进行哈希联接时，您将对大型表进行扫描。有更好的选择。

–罗布·法利
16年1月30日在6:51

#2 楼

由于使用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运算符，其余的看起来应该像您的好计划。

实际上，尽管它确实给了顶层运算符（以及其他一些东西（序列项目，片段和排序）），但它仍然运行了不到一秒。我要给@frisbee正确答案，因为他是第一位，而且更简单。很好的答案。

–肯尼斯·费舍尔
16 Jan 28'在19:31

@KennethFisher，飞盘的答案比较简单，但是用大锤打钉子的方式要比标准锤子更简单。它还有很多风险，特别是如果长期放置在原地。除非在测试中使用，否则我将不会使用类似的提示，或者可能是一个边缘异常。

– Steve Mangiameli
16年1月28日在20:30

@SteveMangiameli在这种特殊情况下，只有一个连接，因此许多问题都消失了。我知道使用联接提示（或查询提示）的风险，我认为这种情况是合理的。

–肯尼斯·费舍尔
16年1月28日在20:34

@KennethFisher Imo，查询提示的主要风险是，随着数据的增长或更改，您强制执行的查询计划可能会变得比系统自己发现的查询计划差。您已经看到了计划中的小错误如何严重影响性能。在生产中使用提示表示：“我知道这个计划将永远是最好的，因为我非常了解计划器以及在生产过程中该查询的整个生命周期中数据的行为。”我从未对查询充满信心。

– jpmc26
16 Jan 30'6:31

#3 楼

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

+1

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

您会看到，对于有序查询，小行目标已到位，系统将尝试按顺序使用数据，以避免有一个排序运算符。它还可以避免构建哈希表，从而避免了寻找第一行的麻烦。在您的情况下，这是错误的-从这些箭头的粗细来看，似乎必须消耗大量数据才能找到单个匹配项。

这些箭头的粗细表明您的DOCUMENT_QUEUE（ DQ）表比您的CORRESPONDENCE_JOURNAL（CJ）表小得多。最好的计划实际上是检查DQ行，直到找到CJ行。实际上，如果其中没有这个讨厌的ORDER BY，那就是Query Optimizer（QO）会做的，这很好地得到了CJ上的覆盖索引的支持。

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

但是，如果您确实确实需要以FILE_NUMBER的顺序排列第一行，那么您可以通过执行ORDER BY CJ.FILE_NUMBER+1来欺骗系统忽略该索引（看起来（非常不正确）很有用），方法是我们知道该索引将保持与以前相同的顺序，但重要的是QO不会。 QO将着重于列出整个集合，以便可以满足Top N Sort运算符。此方法应产生一个计划，其中包含一个Compute Scalar运算符来计算排序值，以及一个Top N Sort运算符来获取第一行。但是在这些右边，您应该看到一个不错的嵌套循环，在CJ上进行了很多搜索。与在DQ中不匹配任何内容的大型表中运行相比，性能更好。

哈希匹配不一定很糟糕，但是如果您从DQ返回的行集比CJ小得多（正如我期望的那样），那么哈希匹配就可以了

注意：我使用+1而不是+0，因为查询优化器很可能会意识到+0不会改变。当然，如果不是现在，那么将来可能会在+1上出现同样的情况。

#4 楼

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

添加OPTION (QUERYTRACEON 4138)只会关闭该查询的行目标的效果，而不会对最终计划有过多的规定，这可能是最简单/最直接的方法。 br />
如果添加此提示会给您带来权限错误（DBCC TRACEON必填），则可以使用计划指南进行应用：

在spaghettidba的计划指南中使用QUERYTRACEON

...或仅使用存储过程：

QUERYTRACEON需要哪些权限？由Kendra Little

#5 楼

当优化器能够应用行目标优化时，较新版本的SQL Server提供了不同的（可能是更好的）选项来处理性能欠佳的查询。 SQL Server 2016 SP1引入了DISABLE_OPTIMIZER_ROWGOAL USE HINT，其作用与跟踪标志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));

#6 楼

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

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

编程黑洞网