Performance of embedded SQL applications

Performance is an important factor to consider when developing database applications. Embedded SQL applications can perform well, because they support static SQL statement execution and a mix of static and dynamic SQL statement execution. Due to how static SQL statements are compiled, there are steps that a developer or database administrator must take to ensure that embedded SQL applications continue to perform well over time.

Embedded SQL application performance is impacted by these factors because the package is created once when a database might have a certain set of characteristics. These characteristics are factored into the creation of the package run time access plans which define how the database manager will most efficiently issue SQL statements. Over time a database schema and data might change rendering the run time access plans sub-optimal. This can lead to degradation in application performance.

For this reason it is important to periodically refresh the information that is used to ensure that the package run-time access plans are well-maintained.

The RUNSTATS command is used to collect current statistics on tables and indexes, especially if significant update activity has occurred or new indexes have been created since the last time the RUNSTATS command was issued. This provides the optimizer with the most accurate information with which to determine the best access plan.