SQL Server 2008 Full-Text Search (FTS) extremely slow when more than one CONTAINSTABLE in query

Question

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.

Answer 1

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.

Answer 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.

Answer 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)