Table of Contents

Stored Procedure Language

Advantages of SPL

Disadvantages of SPL

When are Stored Procedures optimized?

When a stored procedure is created all optimization will be attempted at that time. If the tables cannot be examined at compile time (they may not exist or may not be available), the create procedure will not fail. The SQL in this case will be optimized the first time the stored essay writing procedure is executed, and the query plan will be stored for use by other processes.

An SQL statement will also be optimized at execution time if any DDL statement (e.g. alter table, drop index, create index) has been run that might alter the query plan. Altering a table which is linked to another table via a referential constraint (in either direction) will cause re-optimization of procedures which operate on the referenced table. The dependency list is used to track which changes would cause re-optimization.

If “update statistics for table” is run for any table involved in the query, the SQL statement will be re-optimized the next time the stored procedure is executed.

Dynamic SPL

Dynamic SPL