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.
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.