sql - SQL使用SELECT TOP x的巨大性能差异，即使x远高于选定的行

我正在从表值函数中选择一些行，但通过将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秒内完成。

如果返回的数据集很大，这并不令我感到惊讶，但是所涉及的特定查询返回了200,000行中的〜5000行。

因此，在两种情况下，整个表都将得到处理，因为SQL Server继续搜索6000行，而行将永远无法到达。为什么会有如此巨大的差异呢？这是否与SQL Server预期结果集大小的方式分配空间有关（TOP 6000从而给出了较低的要求，因此更容易在内存中进行分配）？
还有其他人目睹过这样的事情吗？

谢谢

最佳答案

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

让我们考虑与此查询等效的功能：

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的索引查找）

关于sql - SQL使用SELECT TOP x的巨大性能差异，即使x远高于选定的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/1393508/