In the create procedure statement, the optional clause with recompile comes immediately before the SQL statements. It instructs SAP ASE not to save a plan for this procedure. A new plan is created each time the procedure is executed.
In the absence of with recompile, SAP ASE stores the execution plan that it created. This execution plan is usually satisfactory. However, a change in the data or parameter values supplied for subsequent executions may cause SAP ASE to create an execution plan that is different from the one it created when the procedure was first executed. In such situations, SAP ASE needs a new execution plan. Use with recompile in a create procedure statement when you think you need a new plan.
If the operated data for various executions of a stored procedure is not uniform, then the stored procedure should be created using with recompile, so that SAP ASE recompiles the stored procedure for each execution rather than using the plan from a previous execution.
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.