Deferred Compilation in Stored Procedures

SAP ASE optimizes stored procedures when they are first executed, as long as the values that are passed for variables are available.

With deferred compilation, SAP ASE 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.

SAP ASE 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.

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.