Adaptive Server optimizes stored procedures when they are first executed (when values passed for variables are available). Adaptive Server releases prior to 15.0.2 compiled all statements in a stored procedure before the statements were executed. This meant that the actual values of local variables or knowledge of temporary tables created within the stored procedure were not available during optimization. The compiled stored procedure, including the plan, was placed in the procedure cache so that the next time it was executed the user did not need to spend time compiling and optimizing it again.
Adaptive Server 15.0.2 adds deferred compilation with stored procedures that reference local variables or temporary tables are not compiled until they are ready to be executed.
Statements appearing earlier in the stored procedure, such as ones that assign a value to a local variable or create a temporary table, will have already been executed.This means that the statement is optimized based on known values and temporary tables, rather than on magic numbers. Using real values allows the optimizer to select a better plan for executing the stored procedure for the given data set.
The same plan can be reused for subsequent executions of the stored procedure, so long as the data operated on is similar to the data used when the stored procedure was compiled.
Since the plan is optimized specifically for the values and data set used in the first execution, it may not be a good plan for subsequent executions of the stored procedure with different values and data sets. This was true in 12.5, and is even more true and more important in 15.0, as the optimizer has many more options to choose from.
