FreeText 查询很慢 - 包括 TOP 和 Order By

Question

Product 表中有 700K 条记录。查询：

<代码> 选择前 1 个 ID， 姓名 来自产品 WHERE contains(Name, '“白色连衣裙”') ORDER BY DateMadeNew 描述

运行大约需要 1 分钟。 DateMadeNew 上有非聚集索引，Name 上有 FreeText 索引。

如果我删除 TOP 1 或 Order By - 运行时间不到 1 秒。

这是执行计划的链接。 http://screencast.com/t/ZDczMzg5N

看起来 FullTextMatch 的执行次数超过 40 万次。为什么会发生这种情况？怎样才能让它更快呢？

更新 5/3/2010

看来多字 FreeText 搜索的基数不正常：

优化器估计有 28K 条记录匹配“White Dress”，而实际上只有 1 条。 http://screencast.com/t/NjM3ZjE4NjAt

如果我将“白色连衣裙”替换为“白色” ，估计数量是“27,951”，而实际数量是“28,487”，这要好得多。

优化器似乎只使用搜索基数的短语中的第一个单词。

Answer 1

看起来 FullTextMatch 的执行次数超过 400K。为什么会发生这种情况？

由于您有一个与 TOP 1 组合的索引，优化器认为最好遍历索引，检查条目的每条记录。

怎样才能让它更快？

如果更新统计信息没有帮助，请尝试向您的查询添加提示：

SELECT  TOP 1 *
FROM    product pt
WHERE   CONTAINS(name, '"test1"')
ORDER BY
        datemadenew DESC
OPTION (HASH JOIN)

这将强制引擎使用 HASH JOIN 算法来连接您的表和全文查询的输出。

全文查询被视为返回由 FULLTEXT INDEX 定义中提供的 KEY INDEX 索引的值集的远程源。

更新：

如果您的 ORM 使用参数化查询，您可以创建计划指南。

• 使用 Profiler 拦截 ORM 发送的查询逐字
• 使用提示在 SSMS 中生成正确的计划并将其保存为 XML code>
• 将 sp_create_plan_guide 与 OPTION USE PLAN 结合使用，强制优化器始终使用此计划。

Answer 2

编辑

来自http://technet.microsoft .com/en-us/library/cc721269.aspx#_Toc202506240

最重要的是
选择正确的连接类型
全文查询。基数
FulltextMatch STVF 的估计
对于正确的计划非常重要。
所以首先要检查的是
FulltextMatch 基数估计。
这是估计的点击数
在全文搜索的索引中
细绳。例如，在查询中
图 3 这应该接近
包含以下内容的文档数量
术语“词”。大多数情况下应该
非常准确，但如果估计
距离很远，你可以
制定糟糕的计划。估计为
单一条款通常非常好，
但估计多个项，例如
短语或 AND 查询更复杂
因为不可能知道什么
索引中术语的交集
将基于频率
索引中的术语。如果基数
估计是好的，计划是坏的
可能是由查询引起的
优化器成本模型。唯一的方法是
修复计划问题是使用查询
提示强制某种类型的连接
或优化。

因此，它根本无法从存储的信息中得知这两个搜索词是否可能是完全独立的，还是经常一起出现。也许您应该有 2 个单独的过程，一个用于单词查询，让优化器执行其操作，另一个用于多词过程，您强制执行“足够好”的计划（如果您不想要一个“足够好”的计划，sys.dm_fts_index_keywords 可能会有所帮助）一种尺寸适合所有计划）。

注意：您的单字程序可能需要使用“WITH RECOMPILE”选项来查看本文的这一部分。

在 SQL Server 2008 全文搜索中，我们能够更改根据所用搜索词的基数估计生成的计划。如果查询计划是固定的（因为它位于存储过程内的参数化查询中），则不会执行此步骤。因此，编译后的计划始终为该查询提供服务，即使该计划对于给定的搜索词并不理想。

原始答案

不过，您的新计划看起来仍然很糟糕。看起来它只从全文查询部分返回 1 行，但扫描了 Product 表中的所有 770159 行。

这表现如何？

CREATE TABLE #tempResults
(
ID int primary key,
Name varchar(200),
DateMadeNew datetime
)

INSERT INTO #tempResults
SELECT 
      ID, Name, DateMadeNew 
      FROM Product 
      WHERE contains(Name, '"White Dress"')


SELECT TOP 1
    *
    FROM #tempResults
    ORDER BY DateMadeNew desc

Answer 3

我看不到链接的执行计划，网络警察正在阻止它，所以这只是一个猜测......

如果没有 TOP 和 ORDER BY 它运行得很快，尝试这样做：

SELECT TOP 1
    *
    FROM (SELECT 
              ID, Name, DateMadeNew 
              FROM Product 
              WHERE contains(Name, '"White Dress"')
         ) dt
    ORDER BY DateMadeNew desc

Answer 4

对此有几点想法：

1）您是否更新了“产品”表上的统计信息？查看操作的估计行数和实际行数也很有用。

2) 您使用的 SQL Server 版本是什么？我在使用 SQL Server 2008 时遇到了类似的问题，结果只是没有安装 Service Pack 1。安装 SP1 后，原本需要几分钟的 FreeText 查询（由于与实际情况相比有大量的实际执行）减少到只需要一秒钟。

Answer 5

我之前也遇到过同样的问题。

性能取决于您选择用于全文索引的唯一索引。
我的表格有两个独特的列 - ID 和 article_number。

查询：

select top 50 id, article_number, name, ... 
from ARTICLE 
CONTAINS(*,'"BLACK*" AND "WHITE*"')
ORDER BY ARTICLE_NUMBER

如果全文索引连接到ID，那么根据搜索的单词，它会很慢。
如果全文索引连接到 ARTICLE_NUMBER UNIQUE 索引，那么它总是很快。

Answer 6

我有更好的解决方案。

I. 让我们首先概述建议的解决方案，因为它们也可能在某些情况下使用：

1. 选项（散列连接） - 不好，因为您可能会收到错误“由于提示，查询处理器无法生成查询计划重新提交查询，而不指定任何提示，也不使用 SET FORCEPLAN。”

2. SELECT TOP 1 * FROM (ORIGINAL_SELECT) ORDER BY ... - 不好，当您需要使用 ORIGINAL_SELECT 的分页结果时

3. sp_create_plan_guide - 不好好，至于使用 plan_guide 你必须保存特定 sql 语句的计划，这对于动态 sql 语句（例如由 ORM 生成）不起作用

II。我的解决方案包含两部分
1.用于全文搜索的自连接表
2. 使用 MS SQL HASH 连接提示 MSDN 连接提示

您的 SQL ：

SELECT TOP 1 ID, Name FROM Product WHERE contains(Name, '"White Dress"') 
ORDER BY DateMadeNew desc

应重写为：

SELECT TOP 1 p.ID, p.Name FROM Product p INNER HASH JOIN Product fts ON fts.ID = p.ID
WHERE contains(fts.Name, '"White Dress"') 
ORDER BY p.DateMadeNew desc

如果您使用带/不带 Castle Active Records 的 NHibernate，我已在 post 如何编写拦截器来修改查询以用 INNER HASH JOIN 替换 INNER JOIN