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.

The following factors can impact embedded SQL application performance:
  • Changes in database schemas over time
  • Changes in the cardinalities of tables (the number of rows in tables) over time
  • Changes in the host variable values bound to SQL statements

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 runtime 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.

Performance of Embedded SQL applications can be improved in several ways:
  • Run the RUNSTATS command to update database statistics.
  • Rebind application packages to the database to regenerate the run time access plans (based on the updated statistics) that the database will use to physically retrieve the data on disk.
  • Using the REOPT bind option in your static and dynamic programs.