Contains performs MUCH slower with variable vs constant string SQL Server

Question

For some unknown reason I'm running into a problem when passing a variable to a full text search stored procedure performs many times slower than executing the same statement with a constant value. Any idea why and how can that be avoided?

This executes very fast:

SELECT * FROM table
WHERE CONTAINS (comments, '123')

This executes very slowly and times out:

DECLARE @SearchTerm nvarchar(30)
SET @SearchTerm = '123'
SET @SearchTerm = '"' + @SearchTerm + '"'

SELECT * FROM table
WHERE CONTAINS (comments, @SearchTerm)

Does this make any sense???

Answer 1

I've seen the same issue with trying to use a variable for top. SQL Server is not able to tune a query that is using a variable in this way.

You should try using the execsql command.

Answer 2

does this run slow: SELECT * FROM table WHERE CONTAINS (comments, N'123') ??

you are using a varchar '123' in the first example and a nvarchar variable in the second example. This type conversion could be causing you the problem. What is the column defined as?

Also why wrap the variable's value in " double qoutes, but not do the same in the first example. When you run the exact same queries using a literal and a variable do the run differently?

Answer 3

I think Matt b is right. The first query you are searching for

123

In the second query, you are searching for

'123'

The second query with the quotes is probably returning no results, and your program is probably timing out, not the query.