Improving Dynamic SQL Performance

The Problem

With dynamic SQL, the DB2 Optimizer determines the data access path before every execution of the SQL statement. With static SQL, the access path is determined once during SQLPREP and stored in a package, which is subsequently used for execution.

Access path determination is a costly process in terms of CPU usage and contention on the system catalogs. Benchmarks show that path determination (the SQL "PREPARE" command) may be responsible for up to 90% of the CPU time used by a dynamic SQL statement.

There is no problem with spare use of dynamic SQL (e.g. an occasional QMF session). However, with the advent of PC database access, e-business and ERP applications, dynamic SQL tends to become a major bottleneck. Most ERP applications perform all their SQL in dynamic mode. When these applications are used for OLTP, their "prepare" cost becomes a problem (and a waste of resources). Such applications indeed "compile" each statement before each execution!

The Solution

Some databases (e.g. DB2 for OS390) implement a "prepare cache" to reduce the prepare overhead. Since DB2 for VSE & VM has no such facilities, we have developed an AutoPrep facility, which is included in the latest version of our SQL/MF monitor product.

AutoPrep works as follows:

- Literal-based statements are converted to parameter-marker format (see note at the end of this document).
- The dynamic SQL statement text is hashed.
- Using the hash, a "statement execution frequency" table is updated. When the (daily) execution frequency for a given statement reaches a customer-defined trigger, an SQLPREP is initiated for the statement by the AutoPrep server. The SQLPREP produces a regular DB2 package.
- When the same statement is executed again after AutoPrep, the facility replaces the dynamic SQL sequence with a static sequence that invokes the generated AutoPrep package.
- As a result, prepare no longer occurs and its overhead is completely avoided.

The AutoPrep facility is completely transparent for developers and users.
The Results

We have conducted the beta test of the AutoPrep facility at a customer site where e-business applications are heavily used. These applications execute entirely in dynamic mode. The customer complained about high CPU loads and (often dramatic) lock contention on the DB2 catalogs, particularly on SYSACCESS.

With Autoprep, catalog contention has disappeared and average CPU load has been reduced with a factor 5. Average application response times have dropped from 172 to 34 measurement units. No need to say that the customer loves AutoPrep!

Note

Counting the execution frequency is possible with invariant SQL texts only. Dynamic SQL can be performed:

- using "parameter markers" :

- PREPARE SELECT ... FROM ... WHERE column = ?
- OPEN <prepared_statement> USING 'value'

- using literals in the statement text

- PREPARE SELECT .. WHERE column = 'value'

It is obvious that the literal-based format does not produce invariant text. Therefore, AutoPrep will reduce it to parameter marker format before proceeding.