Slow SQL query involving CONTAINS and OR
Why are you using
DescriptionColumn LIKE '%WhatEver%' instead of
CONTAINS is obviously a Full-Text predicate and will use the SQL Server Full-Text engine to filter the search results, however
LIKE is a "normal" SQL Server keyword and so SQL Server will not use the Full-Text engine to asist with this query - In this case because the
LIKE term begins with a wildcard SQL Server will be unable to use any indexes to help with the query either which will most likely result in a table scan and / or poorer performance than using the Full-Text engine.
difficult impossible to tell without an execution plan, however my guess on whats happening would be:
UNIONvariation of the query is performing a table scan against
table1- the table scan is not fast, however because there are relatively few rows in the table it is not performing that slowly (compared to a 35s benchmark).
ORvariation of the query SQL Server is first using the Full-Text engine to filter based on the
CONTAINSand then goes on to perform an RDI lookup on each matching row in the result to filter based on the
LIKEpredicate, however for some reason SQL Server has massively underestimated the number of rows (this can happen with certain types of predicate) and so goes on to perform several thousnad RDI lookups which ends up being incredibly slow (a table scan would have been much quicker).
To really understand whats going on you need to get a query plan.
Did you guys try this:
SELECT *FROM tableWHERE CONTAINS((column1, column2, column3), '"*keyword*"')
Instead of this:
SELECT *FROM tableWHERE CONTAINS(column1, '"*keyword*"') OR CONTAINS(column2, '"*keyword*"') OR CONTAINS(column3y, '"*keyword*"')
The first one is a lot faster.
I just ran into this. This is reportedly a bug on SQL server 2008 R2:
Your approach of using a UNION of two selects instead of an OR is the workaround they recommend in that article.