Post reply

Performance difference between sql query and stored procedure

  • July 24, 2009 at 12:01 pm

    #95880

    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

    #1029947

    Sounds like parameter sniffing. Please post the code for your stored procedure.

  • July 24, 2009 at 12:07 pm

    #1029948

    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

    #1029968

    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