SQL Server "CONTAINS" slower than "LIKE" with multiple expressions
It takes about a minute:
SELECT *
FROM someBigTable
WHERE someColumn LIKE '%foo bar%'
OR someColumn LIKE '%hey macarena%'
It takes 4-6 minutes:
SELECT *
FROM someBigTable
WHERE CONTAINS(someColumn, '"foo bar" OR "hey macarena"')
I repeated each request several times to make sure it wasn't just a fluke.
someColumn
indexed.
This happens with some pairs of expressions (say "foo bar" and "hey macarena"), but not others. But are "CONTAINERS" always faster than "HOW"? What can cause "CONTAINS" to be slower than "LIKE" in some cases?
(Windows Server 2012, SQL Server 2012)
EDIT
Execution plan for the first query:
Execution plan for the second query:
EDIT 2: Execution plans for the "normal" case ("CONTAINS" is faster than "LIKE")
Execution plan for the first query (~ 59 seconds):
Execution plan for the second query (~ 4 seconds):
source to share
Returning more results is not a realistic metric for tuning your query. This affects networking, etc., but not actual performance. What matters is how much data is scanned to retrieve the data, which indexes are used and how (ordered = true / false). As well as other actions that the optimizer performs.
In your example, the two procedures are not equal. Without the '*' indicator, Contain () acts like a full-text search here and as such uses a specific full-text index. A "%%" like this simply performs a full table scan on the smallest index containing the column in the where clause, and searches for columns not in that index.
So in this case it is highly dependent on how the indexes are declared.
source to share