Using with recompile

It is documented in Adaptive Server 12.5 that if the data to be operated on for various executions of a stored procedure is not uniform, then the stored procedure should be created using with recompile, so that the stored procedure will be recompiled for each execution rather than using the plan from a previous execution. Using this option is much more important in Adaptive Server 15.0, and becomes crucial with the introduction of deferred compilation in 15.0.2.

The problem of using optimizer plans from a previous execution can be exacerbated, when multiple copies of a stored procedure are put into the procedure cache at the same time, as a result of simultaneous executions of the stored procedure. If the different executions of the stored procedure used very different data sets, the result is two or more copies of the stored procedure in the procedure cache, each using very different plans. Subsequent executions of the stored procedure will use the copy chosen on the basis of the most recently used (MRU) algorithm.

This problem can cause dramatic performance swings on different executions of the same stored procedure. The same thing can happen in Adaptive Server 12.5, but since the procedure is optimized using magic numbers the plans are probably the same, so the dramatic performance swings are much less likely.