We've had an ongoing issue with one of our custom report procedures. At some point during the day (usually first thing in the morning) it begins taking an excessively long time to generate. Execution time goes from a few seconds to 10 minutes or more.
Through a lot of trial and error we've found that running FREEPROCCACHE for the procedures plan_handle (forcing the system to recompile the procedure the next time it is called) seems to resolve the issue. At least for the rest of the day. The next morning, we start all over again.
I have been digging and learning and trying things, but so far to no avail. I've become familiar with Parameter Sniffing and am thinking about rewriting the procedure to help eliminate problems related to that.
The other option I am looking at is to include WITH RECOMPILE in the procedure itself. Effectively forcing it to recompile itself every time it is executed. There is, technically, a performance hit when you do this. But in this specific case, I feel like it would be worth it.
So I ask the wise and sage Tess community.... Have you tried any of these options? Is there something else I should be looking at? Does anyone else have one stored proc that haunts their dreams?
The procedure in question is a modified version of the Performance Sales Summary. We've added a lot of parameters and created columns for exchange in and exchange out.
- Levi
We've had an ongoing issue with one of our custom report procedures. At some point during the day (usually first thing in the morning) it begins taking an excessively long time to generate. Execution time goes from a few seconds to 10 minutes or more. Through a lot of trial and error we've found that running FREEPROCCACHE for the procedures plan_handle (forcing the system to recompile the procedure the next time it is called) seems to resolve the issue. At least for the rest of the day. The next morning, we start all over again. I have been digging and learning and trying things, but so far to no avail. I've become familiar with Parameter Sniffing and am thinking about rewriting the procedure to help eliminate problems related to that. The other option I am looking at is to include WITH RECOMPILE in the procedure itself. Effectively forcing it to recompile itself every time it is executed. There is, technically, a performance hit when you do this. But in this specific case, I feel like it would be worth it. So I ask the wise and sage Tess community.... Have you tried any of these options? Is there something else I should be looking at? Does anyone else have one stored proc that haunts their dreams? The procedure in question is a modified version of the Performance Sales Summary. We've added a lot of parameters and created columns for exchange in and exchange out. - Levi--View this message online at http://www.tessituranetwork.com/Community/forums/p/11362/35109.aspx#35109 or reply to this message --This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Gawain Lavers f. 510.643.6707