Why are the performances of these 2 queries so different?
OR
can crush performance, so do it this way:
DECLARE @Filter VARCHAR(100)SET @Filter = 'FORMSOF(INFLECTIONAL, robe)'IF @Filter IS NOT NULLBEGIN SELECT TOP 100 ID FROM dbo.Products WHERE CONTAINS(Name, @Filter)ENDELSEBEGIN SELECT TOP 100 ID FROM dbo.ProductsEND
Look at this article: Dynamic Search Conditions in T-SQL by Erland Sommarskog and this question: SQL Server 2008 - Conditional Query.
The first query plan looks straightforward:
- a full text search to resolve
CONTAINS(Name, @Filter)
- an index scan to look up the other columns of the matched rows
- 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:
- an index scan (later used to look up other columns)
- 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
. - a full text search to resolve
CONTAINS(Name, @Filter)
- unions the result of 3 with the empty set from 2
- 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.Productselse SELECT TOP 100 ID FROM dbo.Products WHERE CONTAINS(Name, @Filter)
You've introduced an OR condition.In most cases it is simply much faster to check explicitly for NULL and perform one query vs your method.
For instance try this:
IF @Filter IS NULL BEGINSELECT TOP 100 ID FROM dbo.ProductsENDELSEBEGINSELECT TOP 100 ID FROM dbo.ProductsWHERE @Filter CONTAINS(Name, @Filter)END