SQL Server Full Text Search With NULL Parameters - performace hit

Question

I'm trying to use Contains() in a search procedure. The full text indexes are created and working. The issue arises because you cannot used Contains() call on a NULL variable or parameter, it throws an error.

This takes 9 sec to run (passing in non-null param):

--Solution I saw on another post    
IF @FirstName is null OR @FirstName = '' SET @FirstName = '""'
    ...
    Select * from [MyTable] m
    Where
    (@FirstName = '""' OR CONTAINS(m.[fname], @FirstName))

This runs instantly (passing in non-null param)

IF @FirstName is null OR @FirstName = '' SET @FirstName = '""'
...
Select * from [MyTable] m
Where
CONTAINS(m.[fname], @FirstName)

Just by adding that extra 'OR' in front of the 'contains' completely changed the Query Plan. I have also tried using a 'case' statement instead of 'OR' to no avail, I still get the slow query.

Has anyone solved this the problem of null parameters in full text searching or experience my issue? Any thoughts would help, thanks.

I'm using SQL Server 2012

Answer 1

You are checking value of bind variable in SQL. Even worse, you do it in OR with access predicate. I am not an expert on SQL Server, but it is generally a bad practice, and such predicates lead to full table scans.

If you really need to select all values from table when @FirstName is null then check it outside of SQL query.

IF @FirstName is null 
  <query-without-CONTAINS>
ELSE
  <query-with-CONTAINS>

I believe, in the majority of times @FirstName is not null. This way you will access table using your full text index most of the time. Getting all the rows from table is a lost cause anyway.