I have a store procedure with 5 parameters. It runs slow. If I declare and init these 5 parameters inside the sp and highlight the scripts to run it, it runs fast, about 1 sec. But if I run it with statement as:
exec mysp p1, p2, p3 ..
it runs but takes 2 min to finish. not showing any error. What could be the reasons to cause this? Thanks in advance.
Most likely the stored proc ends up using a stored execution plan that is not the most appropriate for the given set of parameters. People refer to this as "parameter-sniffing" problem - you will find references if you google for it. Examine the query plan and adding statement level recompilations at the approriate places may help.
Thanks James, it's very helpful. I ran the query plan and found warnings showing MISSING INDEX ... then I did research on Google got a very good source at http://sqlfool.com/2009/04/a-look-at-missing-indexes/ ... then problem solved, it runs FAST now. THANKS again.