Contains performs MUCH slower with variable vs constant string MS SQL Server
- by Greg R
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???
