June 19, 2009 at 9:15 am
we have a product that we purchased that uses stored procedures to generate XML. As you all probably know, this re-compiles from time to time and is very difficult to control when. (unless I am missing something, which is possible). We are on SQL 2008 and the procedure is 34,000 lines (attached). HELP!
- Tim
June 19, 2009 at 11:36 am
I was really nervous opening this up, expecting to get 34,000 lines. There are only 14. Had me going for a minute there.
Anyway, 34,000 is more than a little extreme. I'd go back to the vendor and start some quick negotiations. We had a procedure once that involved an 80 table join. Compile time was 2 minutes. We put a temporary fix on it by using the hint KEEP FIXED PLAN. Which prevented recompiles. We recompiled once a day. Long term, we rewrote the query to use fewer than 40 tables as well as changed the indexing structure to get compile times down to sub-second.
This much code is just going to take a while to process.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 29, 2009 at 8:09 am
Yeah, that's a huge query and it's going to take a long time to compile. What's more, the size of it, the number of tables it hits, it's going to recompile pretty often as statistics get updated on the underlying tables. What's more, because it's a single statement, you're not going to get any benefit from the fact that SQL Server does statement level recompiles instead of procedure level recompiles.
I'm back to my original suggestion. You need to talk to the vendor. They've delivered something that is untenable. XML Explicit is a pretty intensive way to retrieve XML from the procedure. You might see if the vendor has heard of the Path mode. It's much less cumbersome than this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply