How to add more OR searches with CONTAINS Brings Query to Crawl?
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
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.