当查询中有多个CONTAINSTABLE时，SQL Server 2008全文搜索（FTS）非常慢

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

在这种情况下,SQL Server 2008全文搜索(FTS)非常慢:

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

结果:SQL 2005和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

结果:SQL 2005和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

结果:在SQL 2005上超级快速(约一秒钟),但在SQL 2008上速度极慢(3分钟+)

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

我知道SQL 2008 FTS的性能问题(即使在*上),但还没有找到任何合理的解决方案。

3 个解决方案

#1

Can you rewrite Query 3 to

你能否重写Query 3?

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.

UNION ALL可能比UNION更快,但可能导致重复记录。

#2

You probably get a bad plan. If you can post plan, it will help diagnose the problem. In case a bad join was choose, you can use query hint to solve the problem.

你可能得到一个糟糕的计划。如果您可以发布计划,它将有助于诊断问题。如果选择了错误的连接,您可以使用查询提示来解决问题。

#3

We just upgraded to SQL 2008 and ran into this issue. I found if I put this at the bottom of the query, it worked great for me: OPTION (MAXDOP 1)

我们刚刚升级到SQL 2008并遇到了这个问题。我发现如果我把它放在查询的底部,它对我很有用:OPTION(MAXDOP 1)

#1