The documentation for Adaptive Server version 12.5 states 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 Adaptive Server recompiles the stored procedure for each execution rather than using the plan from a previous execution. Using this option is much more important in Adaptive Server version 15.0, and becomes crucial with the introduction of deferred compilation in version 15.0.2 and later.
The problem of using query plans from a previous execution can be exacerbated from simultaneous executions of the stored procedure, when multiple copies of a stored procedure are included in the procedure cache. 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 dramatic performance swings are much less likely.
When troubleshooting performance problems with stored procedures, use with recompile to make sure that each of the stored procedures used during the test are recompiled, so that no plan from a previous compilation is used during the test.
