March 27, 2008 at 9:10 am
I looked at the statistics io - scan count, logical reads,physical reads, etc.
and also looked at the estimated execution plan.
For the storeprocedure that contains the a select statement with the passing one input parameter that does some joins and a where clause.
For the same select statement inside the storeprocedure and ran it directly (without exec storeprocedure, just as select statement itself)-- with this one , i have to say declare @var to substitute the parameter which would have been done in sql procedure.
The execution plan was so different. How could that be? And the surprising fact is that the select statement (the one that is standalone) was a better one than it would have been inside a store procedure.
March 27, 2008 at 6:35 pm
One possibility is that the plan used by the stored procedure was based on statistics that existed when the procedure was made and that the statistics changed considerably by the time you ran the select statement by itself. Try forcing a recompiling of the procedure with sp_recompile 'theprocname'.
April 3, 2008 at 2:27 pm
Google "Parameter Sniffing" - I ran into this issue on a large, custom import process. Straight SQL from QA ran in about 45 minutes; calling the same SQL via EXEC took over 13 hours and had a drastically different Explain Plan.
The solution was to call the sp via EXEC @variable, where @variable was re-declared within the stored proc itself.
HTH:
MJM
April 4, 2008 at 11:14 am
Thank you. And I think y ou are right about that. I've tested it similarly to your suggestion and now the storeprocedure plan is in par with the stand alone sql.
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply