StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAdding more OR searches with CONTAINS Brings Query to Crawl
text
Body
copied!<p>I have a simple query that relies on two full-text indexed tables, but it runs extremely slow when I have the <strong>CONTAINS</strong> combined with any additional <strong>OR</strong> 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 <strong>OR</strong> into the mix the query becomes ill-fated.</p> <p>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).</p> <p>I was able to solve the performance by splitting the two <strong>CONTAINS</strong> searches into their own <strong>SELECT</strong> queries and then <strong>UNION</strong> the three together. Is this UNION workaround my only hope?</p> <p>Thanks.</p> <pre><code>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*"') </code></pre> <p>Execution Plan (guess I need more reputation before I can post the image): <a href="http://desmond.yfrog.com/Himg265/scaled.php?tn=0&server=265&filename=slowcontains.jpg&xsize=640&ysize=640">http://yfrog.com/7dslowcontainsj http://desmond.yfrog.com/Himg265/scaled.php?tn=0&server=265&filename=slowcontains.jpg&xsize=640&ysize=640</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload