How to add more OR searches with CONTAINS Brings Query to Crawl?

Question

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 execution plan, the two full text searches crush the performance. If I query with just 1 of the CONTAINS, or neither, the query is sub-second, but the moment you add OR into the mix the query becomes ill-fated.

The two tables are nothing special, they're not overly wide (42 cols in one, 21 in the other; maybe 10 cols are FT indexed in each) or even contain very many records (36k recs in the biggest of the two).

I was able to solve the performance by splitting the two CONTAINS searches into their own SELECT queries and then UNION the three together. Is this UNION workaround my only hope?

SELECT     a.CollectionID
FROM       collections    a
INNER JOIN determinations b ON a.CollectionID = b.CollectionID 
WHERE      a.CollrTeam_Text LIKE '%fa%'
           OR CONTAINS(a.*, '"*fa*"')
           OR CONTAINS(b.*, '"*fa*"')

Execution Plan:

Answer 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
INNER JOIN determinations b ON a.CollectionID = b.CollectionID 
LEFT JOIN CONTAINSTABLE(a, *, '"*fa*"') ct1 on a.CollectionID = ct1.[Key]
LEFT JOIN CONTAINSTABLE(b, *, '"*fa*"') ct2 on b.CollectionID = ct2.[Key]
WHERE      a.CollrTeam_Text LIKE '%fa%'
           OR ct1.[Key] IS NOT NULL
           OR ct2.[Key] IS NOT NULL

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

Answer 3

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

SELECT a.CollectionID
FROM collections a
  LEFT OUTER JOIN (SELECT CollectionID FROM collections WHERE CONTAINS(*, '"*fa*"')) c
    ON c.CollectionID = a.CollectionID
  LEFT OUTER JOIN (SELECT CollectionID FROM determinations WHERE CONTAINS(*, '"*fa*"')) d
    ON d.CollectionID = a.CollectionID
WHERE a.CollrTeam_Text LIKE '%fa%'
   OR c.CollectionID IS NOT NULL
   OR d.CollectionID IS NOT NULL