SQL Stored Procedure Parameter sniffing

I came across this by accident.  I was having issues with queries taking forever to return results.  Turns out, that if you pass parameters into a SP and use them in more than one place in the query, it will slow down the results returned to the user.

So, here's what you do...

  • Define your parameters as usual
  • At the the very beginning of the SP, declare a variable for each parameter making sure they have the same data type.
  • Set each of the declared variables equal to the matching parameter.
  • Use the declared variables in the query where you would usually use the parameters.

You will be amazed at the speed increase.

 

This article was updated on September 8, 2019

Jerry P