使用SELECT TOP SQL巨大的性能差异X即使当x大于所选行高得多(SQL massive p

我从一个表值函数选择一些行，但发现通过将SELECT TOP查询一股莫名的巨大的性能差异。

SELECT   col1, col2, col3 etc
FROM     dbo.some_table_function
WHERE    col1 = @parameter
--ORDER BY col1

被向上取的5或6分钟来完成。

然而

SELECT   TOP 6000 col1, col2, col3 etc
FROM     dbo.some_table_function
WHERE    col1 = @parameter
--ORDER BY col1

完成在约4或5秒。

如果返回的一组数据是巨大的，这将让我感到吃惊， 但特定的查询涉及的回报〜5000行了20万。

因此，在这两种情况下，整个表的处理，如SQL Server将继续在搜索的6000行，它永远不会到达终点。为什么巨大的差异呢？这是什么做的方式SQL Server分配空间，在结果集的大小（顶部6000从而使它这样更容易在内存中分配的要求低）的预期？有没有其他人目睹了这样的事情？

谢谢

Answer 1:

表值函数可以具有非线性的执行时间。

让我们考虑功能相当于此查询：

SELECT  (
        SELECT  SUM(mi.value)
        FROM    mytable mi
        WHERE   mi.id <= mo.id
        )
FROM    mytable mo
ORDER BY
        mo.value

这个查询（计算运行SUM ）是快速在开始和缓慢末，因为从各行mo应总结所有上述值这需要倒带行源。

所需的时间来计算SUM每一行随着行数增加。

如果您mytable足够大（比如， 100,000行，如在你的例子），并运行此查询，你会看到，它需要相当长的时间。

但是，如果你申请TOP 5000到这个查询，你会发现它比完成速度远远1/20 ，为全表所需的时间。

最可能的是，类似的事情发生在你的情况了。

要多说一些肯定，我需要看到函数定义。

更新：

SQL Server可推谓词到函数。

举例来说，我只是创造了这个TVF ：

CREATE FUNCTION fn_test()
RETURNS TABLE
AS
RETURN  (
        SELECT  *
        FROM    master
        );

这些查询：

SELECT  *
FROM    fn_test()
WHERE   name = @name

SELECT  TOP 1000 *
FROM    fn_test()
WHERE   name = @name

产生不同的执行计划（第一个使用群集扫描，第二个使用一个索引查找与TOP ）

Answer 2:

你最没有ORDER BY，所以它只是同SET ROWCOUNT 6000第一。 BY将要求所有行的顺序先计算，它是将需要更长的时间。

如果dbo.some_table_function是一个内联表值UDF，那么它仅仅是组织扩大，以便在没有特定的顺序提到它返回第一个6000行的宏。

如果UDF是多值的，那么它是一个黑盒子，将在完整数据集过滤之前总是拉。我不认为这正在发生。

没有直接关系，但对另TVFs SO问题

Answer 3:

我有同样的问题，一个简单的查询连接五个表返回1000行花了两分钟时间。当我加入“TOP 10000”到它，它在不到一秒钟完成。原来，在桌子上的一个聚集索引是大量碎片。

重建索引后的查询现在完成了不到一秒钟。

Answer 4:

您可能正在运行到的缓存在这里简单的东西 - 也许（无论何种原因）的“TOP”的查询缓存？使用索引对方是不是？

在任何情况下，以解渴你的好奇心的最好方式是检查两个查询完整的执行计划。你可以做在SQL管理控制台这个权利，它会告诉你正在完成什么操作，每一方都预测多长时间服用。

所有SQL实现都以自己的方式有些古怪 - SQL Server的也不例外。这几样“whaaaaaa？！” 时刻是很常见的。 ; ^）

Answer 5:

这不一定是真实的，如果COL1有一个索引整个表进行处理。

在SQL优化将选择是否要使用索引。也许你的“TOP”迫使它使用索引。

如果您使用的是MSSQL查询分析器（名字我想不起来了），然后按Ctrl-K。这将显示查询的执行计划，而不是执行它。鼠标悬停在图标将显示IO / CPU使用率，我相信。

我敢打赌，一个是使用一个索引查找，而另一个则不是。

如果你有一个通用的客户端：SET SHOWPLAN_ALL ON; GO选择...; 走

见http://msdn.microsoft.com/en-us/library/ms187735.aspx了解详情。

Answer 6:

我认为Quassnois的建议，似乎很有道理。加入TOP 6000你是隐给优化一个暗示，20万行的相当小的一部分将要返回。优化器然后使用一个索引查找，而不是聚集索引扫描或表扫描。

另一个可能的原因缓存，因为吉姆戴维斯建议。这是很容易通过再次运行查询，以排除。尝试运行一个与TOP 6000第一。

文章来源: SQL massive performance difference using SELECT TOP x even when x is much higher than selected rows