How To Add More Or Searches With Contains Brings Query To Crawl?

I have a simple query that relies on two full-text indexed tables, but it runs extremely slow when I have the CONTAINS combined with any additional OR search. As seen in the execut

Solution 1:

I'd be curious to see if a LEFT JOIN to an equivalent CONTAINSTABLE would perform any better. Something like:

SELECT     a.CollectionID
FROM       collections    a
INNERJOIN determinations b ON a.CollectionID = b.CollectionID 
LEFTJOIN CONTAINSTABLE(a, *, '"*fa*"') ct1 on a.CollectionID = ct1.[Key]
LEFTJOIN CONTAINSTABLE(b, *, '"*fa*"') ct2 on b.CollectionID = ct2.[Key]
WHERE      a.CollrTeam_Text LIKE'%fa%'OR ct1.[Key] ISNOTNULLOR ct2.[Key] ISNOTNULL

Solution 2:

I was going to suggest to UNION each as their own query, but as I read your question I saw that you have found that. I can't think of a better way, so if it helps use it. The UNION method is a common approach to a poor performing query that has several OR conditions where each performs well on its own.

Solution 3:

I would probably use the UNION. If you are really against it, you might try something like:

SELECT a.CollectionID
FROM collections a
  LEFTOUTERJOIN (SELECT CollectionID FROM collections WHERECONTAINS(*, '"*fa*"')) c
    ON c.CollectionID = a.CollectionID
  LEFTOUTERJOIN (SELECT CollectionID FROM determinations WHERECONTAINS(*, '"*fa*"')) d
    ON d.CollectionID = a.CollectionID
WHERE a.CollrTeam_Text LIKE'%fa%'OR c.CollectionID ISNOTNULLOR d.CollectionID ISNOTNULL

Solution 4:

We've experience the exact same problem and at the time, put it down to our query being badly formed - that SQL 2005 had let us get away with it, but 2008 wouldn't.

In the end, we split the query into 2 SELECTs that were called using an IF. Glad someone else has had the same problem and that it's a known issue. We were seeing queries on a table with ~150,000 rows + full-text going from < 1 second (2005) to 30+ seconds (2008).