Full Text Query takes minutes instead of sub seconds after upgrade

Question

Hello,

We just upgraded our SQL Server 2005 to SQL server 2008 R2 and noticed some performance problems. The query below was already slow but now in 2008 it just times out. We rebuild the catalog to make sure its freshly made on 2008

DECLARE @FREETEXT varchar(255) = 'TEN-T'

select  Distinct ...
from
    DOSSIER_VERSION 
    inner join
    DOSSIER_VERSION_LOCALISED ...
where

    CONTAINS(DOSSIER_VERSION.*,@FREETEXT) 
or 
    CONTAINS(DOSSIER_VERSION_LOCALISED.*,@FREETEXT)

The query takes minutes if you have both conditions enabled.

If you just put the following in the where

CONTAINS(DOSSIER_VERSION.*,@FREETEXT)

Its super fast. Same goes for the case if its just

CONTAINS(DOSSIER_VERSION_LOCALISED.*,@FREETEXT)

Since we are or'ing the results I would expect the time for this query to run to be less than the sum but as stated above it takes minutes/times out.

Can anyone tell me what is going on here? If I use a union (which is conceptually the same as the or) the performance problem is gone but I would like to know what issue I am running into here since I want to avoid rewriting queries.

Regards, Tom

Answer 1

See my answers to these very similar questions:

• Adding more OR searches with CONTAINS Brings Query to Crawl
• SQL Server full text query across multiple tables - why so slow?

The basic idea is that using LEFT JOINs to CONTAINSTABLE (or FREETEXTTABLE) performs significantly better than having multiple CONTAINS (or FREETEXT) ORed together in the WHERE clause.