sql-server-2005 - SQL Server 中的 TOP 性能大幅下降

Question

考虑一下 SQL 性能差异，在第一行中，我选择所有 26,000 行，在第二行中，我只想要前 5 行。

SELECT tw.*
FROM entity e
JOIN entity_tag et on et.entity_id = e.id
JOIN tag t on t.tag_id = et.tag_id
JOIN tagrelatedtweets trt on trt.FK_Tag_ID = t.tag_id
JOIN tweets tw on tw.PK_Tweet_ID = trt.FK_Tweet_ID
WHERE e.id = 765131
ORDER BY tw.[timestamp]

对比

SELECT TOP (5) tw.*
FROM entity e
JOIN entity_tag et on et.entity_id = e.id
JOIN tag t on t.tag_id = et.tag_id
JOIN tagrelatedtweets trt on trt.FK_Tag_ID = t.tag_id
JOIN tweets tw on tw.PK_Tweet_ID = trt.FK_Tweet_ID
WHERE e.id = 765131
ORDER BY tw.[timestamp]

Without: CPU =    201 | Reads:    6880 | Writes:    0 | Duration:   451
With:    CPU = 302439 | Reads: 7453199 | Writes: 3169 | Duration: 74188

这对我来说没有意义......还有其他方法可以解决这个问题吗？

在 Martin 建议对所有涉及的表进行 REBUILD STATISTICS 之后，有一个小的改进，但是将 TOP 金额更改为参数的技巧效果最好。

统计数据重建之前:

CPU = 302439 | Reads: 7453199 | Writes: 3169 | Duration: 74188 

统计数据重建后:

CPU = 127734 | Reads: 4100436 | Writes: 2656 | Duration: 16880 

带参数:

CPU =    218 | Reads:    6899 | Writes:    0 | Duration:    83 

带参数查询:

DECLARE @TOP INT; SET @TOP=5; 
SELECT TOP (@TOP) tw.*
FROM entity e
JOIN entity_tag et on et.entity_id = e.id
JOIN tag t on t.tag_id = et.tag_id
JOIN tagrelatedtweets trt on trt.FK_Tag_ID = t.tag_id
JOIN tweets tw on tw.PK_Tweet_ID = trt.FK_Tweet_ID
WHERE e.id = 765131
ORDER BY tw.timestamp desc

对于那些使用 Entity Framework 的人来说最后一句话；如果您遇到此行为，您可以模拟相同的基于参数的行为，如下所示:

.Take(100).ToList().Take(5)

我知道这并不漂亮，但据我所知，如果您使用 Entity Framework ，这是触发正确执行计划的唯一方法。

非常感谢马丁为我指明了正确的方向!

Answer 1

根据评论中的讨论，似乎由于某种原因，SQL Server 选择针对“仅前 5 行”情况进行优化的计划并不是最优的。它可能会使用非阻塞运算符(例如嵌套循环连接)，而不是阻塞运算符(例如哈希连接)。

由于您无法发布执行计划，因此很难确切地知道原因，但是比较两个版本的查询的计划并查看问题查询的实际执行计划中的实际行数与估计行数应该对此事进行一些澄清。

在这种情况下，在编译时隐藏优化器的 TOP 5 信息似乎足以为您提供您想要的计划!