sql - 具有 TOP 10 的查询如何比没有 TOP 10 的相同查询花费无限长的时间来完成？

Question

我的情况非常简单:

我有一个名为 FullTextPagina 的表值函数，定义如下:

select * from Pagina as p where contains(p.PageText, @term)

然后我有两个疑问:

declare @term nvarchar(4000)= N'"DIEGO NUNES J COMBINADO"'

SELECT Id, DtPagina
FROM FullTextPagina(@term)
ORDER BY DtPagina DESC

SELECT TOP 10 Id, DtPagina
FROM FullTextPagina(@term)
ORDER BY DtPagina DESC

除了第二个包含 TOP 10 语句之外，它们是相同的。他们不返回任何东西。 0 行。

第一个立即执行。秒数需要 1:20m 才能完成。

为什么？

附注:

1. 我已正确设置全文索引
2. 我在 DtPagina 上有一个非聚集、非唯一的降序索引
3. 执行计划在这里:/image/PYb0I.png

<小时/>

编辑

回应@MartinSmith，奇怪的是，表值函数的“执行次数”对于 TOP 10 案例是 118 万次，而其他案例是 1

编辑2

执行计划 XML http://tecnologia.novaprolink.com.br/Execution%20plan.xml

编辑3

添加选项(重新编译)或去掉参数不影响结果

SELECT Id, DtPagina
FROM FullTextPagina(N'"DIEGO NUNES J COMBINADO"')
ORDER BY DtPagina DESC

SELECT TOP 10 Id, DtPagina
FROM FullTextPagina(N'"DIEGO NUNES J COMBINADO"')
ORDER BY DtPagina DESC
OPTION (RECOMPILE)

编辑4

FullTextPagina的完整代码

USE [RexConsumo_2011_11]
GO

/****** Object:  UserDefinedFunction [dbo].[FullTextPagina]    Script Date: 11/24/2011 11:43:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[FullTextPagina] (@term nvarchar(4000))
RETURNS TABLE 
AS
RETURN 
(
    select * from Pagina as p where contains(p.PageText, @term)
)

GO

Answer 1

您遇到的问题是因为 SQL Server 无法准确估计有多少行与谓词匹配。

您的查询正在执行SELECT TOP 10 Id, DtPagina ... ORDER BY DtPagina DESC。关于如何做到这一点，有几种选择

选项 1

它可以按顺序扫描 DtPagina DESC 索引，查看每行是否与全文谓词匹配，然后在找到索引顺序中的前 10 行时退出。

选项 2

1. 评估全文谓词
2. 检索所有匹配行的 DtPagina 列值
3. 对它们进行排序并获取前 10 名。

在计算第一个选项的成本时，底部计划显示它预计必须扫描大约 600 行，然后才能获得 10 条匹配的行并能够退出。这是一个巨大的低估，因为实际上没有行与谓词匹配，并且需要对整个 1,186,533 行执行此操作。

在计算顶部计划中的第二个选项的成本时，可以看出它假设将从全文索引查询中带回 13,846.2 个匹配行，并且需要进行连接和排序。这是一个很大的高估，因为实际数字为零。

因此，这些错误的估计导致它错误地支持第一个选项。

我不确定可以采取哪些措施来提高全文索引统计数据的准确性。也许尝试使用 containstable

重写查询

编辑:这有点破解，但可能很有效。如果你尝试一下会怎样

declare @term nvarchar(4000)= N'"DIEGO NUNES J COMBINADO"'
declare @num int = 10

SELECT TOP (@num) Id, DtPagina
FROM FullTextPagina(@term)
ORDER BY DtPagina DESC

然后它会假设TOP 100，这很可能足以提示它选择其他更有效的计划。