SQL Server Performance Forum – Threads Archive

SP – internal vars fast; params v. slow??

Hello and thanks for reading this, I have T-SQL code that returns results in under 30 seconds when run as an ad hoc query or when contained in a stored procedure that takes no parameters but has 8 internal variables set within it. When I use the exact same code in a stored procedure that takes parameter values from a calling program (either Query Analyzer or an ASP page), it takes forever. When I run the parameterized stored procedure without running the code but print the values of passed parameters, they are exactly as expected. Do you have any ideas why there would be such a discrepancy between running ad hoc code and running the same code as a parameterized stored procedure? Thank you

Parameter sniffing or just execution plan optimal for the set of parameters stored procedure first executed but not optimal for current set of parameter values.

as Marovic says, sounds like parameter sniffing problem… You could perhaps try executing the procedure by adding with recompile to force sql to recompile the proc (and therefore sniff the correct parameters…) Cheers
Twan

Read this for an explanation of parameter sniffing:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11611 —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

… also intermittent RECOMPILE of such procedures would help the performance. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

On a side note – I always thought parameter sniffing was the solution to the problem, but now I see that it’s actually the name for the problem that is solved with the local variables. How silly of me.[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

]]>