using local variables in place of passed parameter

Question

Post reply

using local variables in place of passed parameter

jch50a

Newbie

Points: 8
More actions
August 29, 2011 at 7:56 am

#390293

I have run across several scenarios where my Stored Procedures, with a parameter, take an abnormal to process (3 minutes sometimes) but the query is nothing special or complex. When I create a local variable and assign my parameter value to it then use that to process the query my result set will come back drastically faster (3 seconds). I don't have an answer for this behavior and I was hoping someone could explain it to me.
Thanks.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

Check out Gail's blog. She has two great articles about just this behavior called parameter sniffing.

http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/%5B/url%5D

http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/%5B/url%5D

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

You are seeing a "random win". You should address the root cause by doing something such as OPTION (RECOMPILE) or even dynamic sql (be sure to guard against SQL Injection!!).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

jch50a Newbie Points: 8 More actions · Answer 3

Excellent! This makes a lot more sense then my previous "It's Magic" explanation. This gives me the info I need to combat this in the future. Thanks for the help.