Store procedure runs slower than query run in QA

  • gregtm

    SSC Eights!

    Points: 836

    I have a Query that I can run from Query Analyer which takes 30 seconds. When I run the same Query as a store procedure it takes 35min! Does anyone have any idea why it takes this long to run as a stored procedure and how to fix it?

    Greg

  • Andy Warren

    SSC Guru

    Points: 119676

    Bad query plan. Run sp_recompile sp name.

    Andy

  • gregtm

    SSC Eights!

    Points: 836

    Andy

    I tried it on the Store procedure but no luck so I ran sp_recompile against the tables that the query calls and it worked. It brought the query down from 35min to 1 min and 12 sec. Running the code in Query Analyer still only takes 30 seconds, I'm still puzzle as to why it takes double the time when run as a stored procedure. I thought that it would be faster as a store procedure since the query plan is compiled in memory!

    Thanks for your help

    Greg

  • Andy Warren

    SSC Guru

    Points: 119676

    You'll see differing results in QA depending on whether you use grid or text mode (grid is faster), so if you tested in different setups that could account for it. You also need to have run the sp once so that data is cached (and the plan compiled).

    Might also look to see if you're doing anything else at all in the proc. I cant think of any reason for the same SQL to execute slower in a proc if everything else is equal.

    Andy

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716661

    try dbss freeproccache to remove all query plans, then recompile the sproc (or sp_recompile.

    Are you sure this is the exact same code? I might try recompiling the actual code to be sure. Maybe make a temp stored proc with the same code you are running in QA.

    Steve Jones

    steve@dkranch.net

  • gregtm

    SSC Eights!

    Points: 836

    I have another stored procedure that I thought I might try to run the sp_recompile against to see if it would improve the performance. It normally takes 15 hrs to run, Its our month end reporting procedure on our Reporting server. After running sp_recompile on the sp and table it increased the time it took to 29 hrs. Is there something else I should be doing? Since this is the first time since I've run it after exec sp_recompile will it decrease the time back to 15 or less hrs?. Greg

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716661

    sp_recompile forces the procedure to recompile when you run it and a new query plan is built at that time. We were thinking the query plan is out of date (new indexes, statistics, etc.) and the server is not choosing the optimal path.

    Recompiling it will not make it run faster. If you can, run it in Query analyzer, with the CTRL-K set to view the execution plan. Look for the high % items and try to tune that portion of the proc. If you can post the code and some results, we can try to help.

    Steve Jones

    steve@dkranch.net

  • Ripple

    Right there with Babe

    Points: 777

    Greg - Could you post more details? Queries/reports that run for hours might be restructured to run in less time.

    Also, I have found that adding 'with recompile' to stored proceduers which are not run often can speed things up by generating a new query plan each time they are run. This is similar to flagging tables as 'recompile'. The hit of recompiling a stored procedure when it takes minutes or hours to run is nominal.

    I'd be interested in seeing the number of tables, rows, etc. in your database.

    I've worked on databases with 200+ million row tables that gave sub-second or multiple second response times - so I think you should be able to dramitically reduce your run time with some tuning.

    Cheers.

    - Brendan

  • Andy Warren

    SSC Guru

    Points: 119676

    Thats a good tip Brendan. There are some cases where the compiled query plan is good in one situation, bad in another - forcing the recompile ends up making it run better even with the overhead of the recompile. You might also want to check that your statistics are up to date.

    Andy

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply