3 ms·
the penalty for using a cached plan that is no longer optimal can be devastating, I've often had to use "with recompile" to get around this on SQL Server - I do
by gfody 7y ago
the penalty for using a cached plan that is no longer optimal can be devastating, I've often had to use "with recompile" to get around this on SQL Server - I don't think global plan cache is a no-brainer, the scenario you describe is probably better served by a short-lived results cache
- cogman10 7y agoOr worse, I've ran into cases where just enough "bad" parameters flow into the planner to topple the statistics on a previously well behaving stored proc. SQL server does not recover quickly after a cached plan goes wrong. More than once, either adding "WITH RECOMPILE" or manually recompiling a SP has fixed our performance issues. "Everyone has a plan until they get punched in the face" - Mike Tyson
- SigmundA 7y agoI have rarely run into this issue, but they do occur which is why WITH RECOMPILE exists. The majority of the time caching the plans is a benefit and therefore has been the default behavior with a opt out option. With that kind a of plan instability I start looking at query hints though which PG doesn't have either...