but I guess that isn't clear until too late. I think what it is, is that I'd *like* "with recompile" to act like it has "option(recompile)" on just the statements that can really benefit from it. Kendra's article does not discuss these aspects. So, you say, let's see, it's going to cost me 0.4 seconds of recompile every call, to keep it from blowing up to 10.0 seconds every second Wednesday. And, well, you may have a lot of slack to go with, when the SP normally runs in 0.2 seconds with 6k reads, until it goes nutsĪnd runs in 10.0 seconds with 9m reads (I begrudge the reads almost more than the time). So, the recompiles, either of these, is going to cost you something on every execution. So you go back to the SP and wonder, how do I keep it from blowing up at random times like that? Well, with option statements, Then it gets some bad parameters in its teeth and the plan blows up. Tom, I don't see that that link quite answers the question.Īnd, I didn't really mention the real problem, although it's as common as dirt and it's the reason I'm asking.įirst, you right an SP and it seems fine.
(And the behaviour with variables as constants appeared first in SQL 2008.) OPTION (RECOMPILE) was added in SQL 2005. Keep in mind that WITH RECOMPILE has been in the product since at least 4.x. So is there any situation you would use WITH RECOMPILE rather than OPTION (RECOMPILE)? From a pure performance point of view I cannot think of a case, but I know that I have used it in a few places where I have shared temp tables between stored procedures,Īnd I have gotten weird errors due to the cached plan for the inner procedure. Values, and this will be a scan of some sort. For this reason, the optimizer must build a plan which is correct for all If you use OPTION (RECOMPILE), the variables will be handles as constants, and the correct index will be used.īut with WITH RECOMPILE, SQL Server does not know the run-time values of to even of they are parameters to the procedure (since they can change inside the procedure). The other is that OPTION (RECOMPILE) can give you better query plans. One is that OPTION (RECOMPILE) only affects the statement in question, while WITH RECOMPILE requests recompilation of the lot. Is "option(recompile)" more efficient than "with recompile"?
0 kommentar(er)
