July 24, 2009 at 12:01 pm
I have a stored procedure that returns record set so slow (normally 1 min). If I run separately the sql query which is the main portion of the stored procedure, It takes less than a sec. I may be wrong but it seems like index is not being used if I run it through the stored procedure. Any idea?
July 24, 2009 at 12:06 pm
Sounds like parameter sniffing. Please post the code for your stored procedure.
July 24, 2009 at 12:07 pm
Have you looked at the query plan for each. Also, was the index created after the sproc? Perhaps a recompilation of the sproc is necessary. If you are running the exact same query in both cases then the query alone should take slightly longer to execute, primarily due to compilation and optimization. you could use sp_recompile 'tablename' and force everything that uses that table to refresh their compiled query plan.
CEWII
July 24, 2009 at 12:35 pm
My first reaction was "what the heck is parameter sniffing !!!". Believe it or not, it was becacuse of it. Thank you so much guys.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply