Take the 2-minute tour ×

Sql server 2008 R2

I have two queries

DECLARE @title NVARCHAR(500) = '"Finite" AND "Elements"'

select * from papers p
where (@title = '""' OR CONTAINS(p.name, @title))

select * from papers p
where (CONTAINS(p.name, @title))

first works about 7 seconds, second several miliseconds. WHY????


share|improve this question
Please show the plans for both. –  Martin Smith Jan 20 '12 at 10:07
add comment

1 Answer

up vote 2 down vote accepted

The @title = '""' OR ... bit is the problem.

At compile time it does not know whether @title is in fact going to contain the value "" and thus need to return all rows or not.

You could try adding OPTION(RECOMPILE) to the query so it is recompiled after the variable is assigned or just splitting it up into two cases.

IF @title = '""'
  FROM   papers
  FROM   papers p
  WHERE  ( CONTAINS(p.name, @title) )  
share|improve this answer
Thanx. Problem solved –  Александр Махин Jan 20 '12 at 10:25
add comment

Your Answer


