The Only Thing Worse Than Optional Parameters…

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.

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

Sample Run

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

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

The query plan looks like this:

Badness

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.

Messy

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!

8 thoughts on “The Only Thing Worse Than Optional Parameters…”

  1. There are things like searh screens that have multiple possible parameters and are drived by a stored proc. We got round this by writing dynamic SQL. Not ideal but ran a lot better than the way described.

    Not sure How you can get around it.
    Some screens might have 5 to 10 parameters (from/to as well) so impossible to write a SP per permutation, and the maintenance would be awful.

Leave a Reply

Your email address will not be published. Required fields are marked *