October 25, 2001 at 4:36 pm
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
October 25, 2001 at 8:08 pm
Bad query plan. Run sp_recompile sp name.
Andy
October 26, 2001 at 12:06 pm
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
October 26, 2001 at 12:29 pm
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
October 26, 2001 at 4:19 pm
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
November 5, 2001 at 11:46 am
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
November 5, 2001 at 12:55 pm
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
November 8, 2001 at 9:39 am
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
November 8, 2001 at 5:38 pm
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply