November 1, 2012 at 7:10 pm
Dear All,
A stored procedure with 10 parameters. I have 2 issues with this proc.
1. From application user generally passes at least 1 parameter. For each execution these parameters are different. user may pass 1-10 parameters.
2. Even if the user passes the @Parm1, @Parm2 with different values at different times of execution, the proc takes more than 30 sec for some values.
How to improve the performance of this proc for any kind of parameters.
I appreciate your suggestions.
Thanks,
Ram.
November 2, 2012 at 2:14 am
Can you please post your Store Procedure ?
and also please explain what you want in more details.
November 2, 2012 at 2:23 am
Gut feel is that this is a case of parameter sniffing, and if the user can enter anywhere between 1-10 parameters its most likely that the engine isnt chosing an optimal plan.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 2, 2012 at 2:30 am
If I had to guess, and it's a complete guess since I can't see the proc, I'd say it's probably this:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply