Why are the performances of these 2 queries so different?

Question

I have a stored proc that searches for products (250,000 rows) using a full text index.

The stored proc takes a parameter that is the full text search condition. This parameter can be null, so I added a null check and the query suddenly started running orders of magnitude slower.

-- This is normally a parameter of my stored proc
DECLARE @Filter VARCHAR(100)
SET @Filter = 'FORMSOF(INFLECTIONAL, robe)'

-- #1 - Runs < 1 sec
SELECT TOP 100 ID FROM dbo.Products
WHERE CONTAINS(Name, @Filter)

-- #2 - Runs in 18 secs
SELECT TOP 100 ID FROM dbo.Products
WHERE @Filter IS NULL OR CONTAINS(Name, @Filter)

Here are the execution plans:

Query #1

Query #2

I must admit that I am not very familiar with execution plans. The only obvious difference to me is that the joins are different. I would try adding a hint but having no join in my query I am not sure how to do that.

I also do not quite understand why the index called IX_SectionID is used, since it is an index that only contains the column SectionID and that column is not used anywhere.

Answer 1

OR can crush performance, so do it this way:

DECLARE @Filter VARCHAR(100)
SET @Filter = 'FORMSOF(INFLECTIONAL, robe)'

IF @Filter IS NOT NULL
BEGIN
    SELECT TOP 100 ID FROM dbo.Products
    WHERE CONTAINS(Name, @Filter)
END
ELSE
BEGIN
    SELECT TOP 100 ID FROM dbo.Products
END

Look at this article: Dynamic Search Conditions in T-SQL by Erland Sommarskog and this question: SQL Server 2008 - Conditional Query.

Answer 2

The first query plan looks straightforward:

1. a full text search to resolve CONTAINS(Name, @Filter)
2. an index scan to look up the other columns of the matched rows
3. combine the two using a hash join

The concatenation operator forms a union of two recordsets. So it looks like the second query is doing:

1. an index scan (later used to look up other columns)
2. a constant scan. I assume it's treating your query as not parameterized, so the query plan doesn't have to work for any other value of @Filter. If correct, the constant scan resolves @Filter is not null.
3. a full text search to resolve CONTAINS(Name, @Filter)
4. unions the result of 3 with the empty set from 2
5. loop joins the result of 1 and 4 to look up the other columns

A hash join trades memory for speed; if your system has enough memory, it's much faster than a loop join. This can easily explan a 10-100x slowdown.

One fix is to use two distinct queries:

if @Filter is null
    SELECT TOP 100 ID FROM dbo.Products
else
    SELECT TOP 100 ID FROM dbo.Products WHERE CONTAINS(Name, @Filter)