Adaptive Server optimizes stored procedures when they are first executed, as long as values passed for variables are available.
With deferred compilation, Adaptive Server has already executed statements that appear earlier in the stored procedure, such as statements that assign a value to a local variable or create a temporary table. This means 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.
Adaptive Server can reuse the same plan for subsequent executions of the stored procedure, as long as the data operated on is similar to the data used when the stored procedure was compiled.
Versions of Adaptive Server earlier than 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 unavailable during optimization. The compiled stored procedure, including the plan, was placed in the procedure cache.
Beginning with Adaptive Server 15.0.2, deferred compilation is used for stored procedures that reference local variables or temporary tables are not compiled until they are ready to be executed.
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.
