The Only Thing Worse Than Optional Parameters For SQL Server Query Performance

Kitchen Stink

---

A lot has been written about “kitchen sink” queries. A couple of my favorites are by Aaron Bertrand and Gail Shaw.

Both articles have similar start and end points. But I’m going to start at an even worse point.

Catch All Parameters

---

This is the worst possible idea.

    CREATE OR ALTER PROCEDURE dbo.AwesomeSearchProcedure (@SearchString NVARCHAR(MAX))
    AS
    SET NOCOUNT, XACT_ABORT ON;
    SET STATISTICS TIME, IO OFF;
    BEGIN
    
    DECLARE @AltString NVARCHAR(MAX) = N'%'
    
    SELECT TOP (1000) p.OwnerUserId, p.Title, p.CreationDate, p.LastActivityDate, p.Body
    FROM dbo.Posts AS p
    WHERE p.OwnerUserId LIKE ISNULL(N'%' + @SearchString + N'%', @AltString)
    OR    p.Title LIKE ISNULL(N'%' + @SearchString + N'%', @AltString)
    OR    p.CreationDate LIKE ISNULL(N'%' + @SearchString + N'%', @AltString)
    OR    p.LastActivityDate LIKE ISNULL(N'%' + @SearchString + N'%', @AltString)
    OR    p.Body LIKE ISNULL(N'%' + @SearchString + N'%', @AltString);
        
    END;
    GO

It doesn’t get any better if you do this, either.

    SELECT TOP (1000) p.OwnerUserId, p.Title, p.CreationDate, p.LastActivityDate, p.Body
    FROM dbo.Posts AS p
    WHERE (p.OwnerUserId LIKE N'%' + @SearchString + N'%' OR @SearchString IS NULL)
    OR    (p.Title LIKE N'%' + @SearchString + N'%' OR @SearchString IS NULL)
    OR    (p.CreationDate LIKE N'%' + @SearchString + N'%' OR @SearchString IS NULL)
    OR    (p.LastActivityDate LIKE N'%' + @SearchString + N'%' OR @SearchString IS NULL)
    OR    (p.Body LIKE N'%' + @SearchString + N'%' OR @SearchString IS NULL);

Sample Run

---

In both cases, just searching for a single value results in a query that runs for >2 minutes.

    EXEC dbo.AwesomeSearchProcedure @SearchString = N'35004';
    GO

The problem is that we’re just searching for an OwnerUserId, but SQL Server doesn’t know that.

The query plan looks like this:

See that Filter? That’s where we do all of our search work. We scan the whole Posts table, and push every row across the pipe to the Filter.

Irritable

---

This pattern might work on a small amount of data, but like most things that are efficient in small doses this will quickly fall apart when your database reaches a mature size.

My example is pretty simple, too, just hitting one table. In real life, you monsters are going this across joins, throwing in row numbers, distincts, and ordering by the first 10 columns.

It only gets worse as it gets more complicated.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.