Here is an interesting case I encountered recently.
I have a procedure that takes >45 sec to complete.
The users would very much like to have it execute much faster.
It has > 11,000 lines of code and originally, it went something like this:
SELECT <some_new_ID> ….
WHERE ID = <Old_ID>…
Some are straight forward trivial, some have additional logic, sub-queries etc. but nothing very complex, and there are many hundreds of those.
It is looking to ‘multiply’ an entity which may or may not have rows in some of these tables. The original designer’s line of thinking was that there was no harm in doing all of them since those that have no source rows for that particular entity will result in an INSERT with 0 rows… Typically, an entity will have rows in 3-5 tables out of those hundreds.
So my initial line of thinking was that the execution of the redundant code with the joins and all is slowing things down so I encapsulated each insert with a simplified ‘sanity’ check:
IF EXISTS (SELECT NULL FROM A WHERE ID = <Old_ID>
Hoping that the much simpler execution of the trivial EXISTS statement will be faster.
That didn’t help. It turned out that the parse and compile was taking most of the time so I figured that we don’t really need to compile all statements if only a handful get executed, so I further encapsulated the INSERTS in sp_executesql to save compile until actual execution:
EXECUTE sp_executesql ‘INSERT…’
But that didn’t help either. Just the parsing and compilation of the trivial IF EXISTSs still takes about 30 sec but less than 1 sec to execute.
It doesn’t get executed often enough to keep the plan in cache despite the high cost.
So I said, OK… all I need to do is make sure it doesn’t get recompiled, something like KEEPFIXEDPLAN query hint, only for the whole procedure, but there is none.
My only options to work around this are:
1. Use KEEPFIXEDPLAN (or plan guides) for every statement which I really don’t like and won’t save the parse and plan lookup time.
2. Have a job invoke the procedure in ‘null’ mode (have a flag to exit immediately – thanks for the idea Andy Kelly) often enough to keep it in cache. That doesn’t sound very reliable either.
But wouldn’t it be so much simpler and elegant to have something like:
CREATE PROCEDURE …. WITH NO_RECOMPILE
CREATE PROCEDURE … WITH SCHEMA_BINDING, KEEPFIXEDPLAN
Unless plan stability issues arise, if I know the plans are trivial and will not change, and I’m willing to risk it, I want to have the ability to force a procedure plan to stay in cache.
If you think so too, please vote:
If you have another idea, please comment here!