当查询中有多个CONTAINSTABLE时，SQL Server 2008全文本搜索（FTS）极其缓慢 [英] SQL Server 2008 Full-Text Search (FTS) extremely slow when more than one CONTAINSTABLE in query

问题描述

在这种情况下，SQL Server 2008全文搜索（FTS）非常缓慢：

b
$ b

  SELECT [...] FROM ContentItem CI WHERE 
（** EXISTS **（SELECT TOP 1 * FROM ** CONTAINSTABLE ** （[** Table1 **]，*，'[search_string] *''）FT 
 WHERE FT。[Key] = CI.ContentItem_Id））
 ORDER BY [...] 
  

结果：在SQL 2005和SQL 2008上超快速

查询2：

  SELECT [...] FROM ContentItem CI WHERE 
（** EXISTS **（SELECT TOP 1 * FROM ** CONTAINSTABLE **（[** Table2 **]，*，'[search_string] *''）FT 
 WHERE FT。[Key] = CI.ContentItem_Id））
 ORDER BY [...] 
  

结果：在SQL 2005和SQL 2008上超快速

查询3：

  SELECT [...] FROM ContentItem CI WHERE 
（** EXISTS **（SELECT TOP 1 * FROM ** CONTAINSTABLE **（[ **表格1 **]，*，'[search_string] *'）FT 
 WHERE FT。[Key] = CI.ContentItem_Id）
 **或EXISTS **（SELECT TOP 1 * FROM ** CONTAINSTABLE **（[** Table2 **]，*，'[search_string] *'）FT 
 WHERE FT [Key] = CI.ContentItem_Id））
 ORDER BY [...] 
  

结果： SQL 2005上的超快速（大约一秒） （SQL Server 2008）中的性能问题（甚至在stackoverflow上），但避难所找不到任何合理的解决方案。

解决方案

您可以将Query 3重写为

  SELECT ... WHERE EXISTS ... CONTAINSTABLE（表1 ...）
 UNION 
 SELECT ... WHERE EXISTS ... CONTAINSTABLE（表2 ...）
 ORDER BY ... 
  

？

UNION ALL可能比UNION更快，但可能会导致重复的记录。

SQL Server 2008 Full-Text Search (FTS) is extremely slow in this scenario:

Query 1:

SELECT [...] FROM ContentItem CI WHERE 
(**EXISTS** (SELECT TOP 1 * FROM **CONTAINSTABLE**([**Table1**], *, '"[search_string]*"') FT 
WHERE FT.[Key] = CI.ContentItem_Id)) 
ORDER BY [...]

Results: super fast on SQL 2005 and SQL 2008

Query 2:

SELECT [...] FROM ContentItem CI WHERE 
(**EXISTS** (SELECT TOP 1 * FROM **CONTAINSTABLE**([**Table2**], *, '"[search_string]*"') FT 
WHERE FT.[Key] = CI.ContentItem_Id)) 
ORDER BY [...]

Results: super fast on SQL 2005 and SQL 2008

Query 3:

SELECT [...] FROM ContentItem CI WHERE 
(**EXISTS** (SELECT TOP 1 * FROM **CONTAINSTABLE**([**Table1**], *, '"[search_string]*"') FT 
WHERE FT.[Key] = CI.ContentItem_Id) 
**OR EXISTS** (SELECT TOP 1 * FROM **CONTAINSTABLE**([**Table2**], *, '"[search_string]*"') FT 
WHERE FT.[Key] = CI.ContentItem_Id)) 
ORDER BY [...]

Results: super fast on SQL 2005 (about a second), but extremely slow (3 min+) on SQL 2008

I'm aware of performance issues with SQL 2008 FTS (even on stackoverflow), but haven't find any reasonable solution yet.

解决方案

Can you rewrite Query 3 to

SELECT ... WHERE EXISTS ... CONTAINSTABLE(Table1...)
UNION
SELECT ... WHERE EXISTS ... CONTAINSTABLE(Table2...)
ORDER BY ...

?

UNION ALL may be faster than UNION, but possibly result in duplicate records.

这篇关于当查询中有多个CONTAINSTABLE时，SQL Server 2008全文本搜索（FTS）极其缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！