Dynamic SQL Performance

The performance of dynamic SQL is one of the most widely debated DB2 issues. Some shops avoid it, and many of the ones that allow it place strict controls on its use. Completely avoiding dynamic SQL is unwise, but placing controls on its use is prudent. As new and faster versions of DB2 are released, many of the restrictions on dynamic SQL use will be eliminated.

You can find some good reasons for prohibiting dynamic SQL. You should avoid dynamic SQL when the dynamic SQL statements are just a series of static SQL statements in disguise. Consider an application that needs two or three predicates for one SELECT statement that is otherwise unchanged. Coding three static SELECT statements is more efficient than coding one dynamic SELECT with a changeable predicate. The static SQL takes more time to code but less time to execute.

Another reason for avoiding dynamic SQL is that it almost always requires more overhead to process than equivalent static SQL. Dynamic SQL incurs overhead because the cost of the dynamic bind, or PREPARE , must be added to the processing time of all dynamic SQL programs. However, this overhead is not quite as costly as many people think it is. To determine the cost of a dynamic bind, consider running some queries using SPUFI with the DB2 Performance trace turned on. Then examine the performance reports or performance monitor output to determine the elapsed and TCB time required to perform the PREPARE . The results should show elapsed times less than 1 second and subsecond TCB times. The actual time required to perform the dynamic prepare will vary with the complexity of the SQL statement. In general, the more complex the statement, the longer DB2 will take to optimize it. So be sure to test SQL statements of varying complexity.

Of course, the times you get will vary based on your environment, the type of dynamic SQL you use, and the complexity of the statement being prepared. Complex SQL statements with many joins, table expressions, unions, and subqueries take longer to PREPARE than simple queries. However, factors such as the number of columns returned or the size of the table being accessed have little or no effect on the performance of the dynamic bind.

Performance is not the only factor when deciding whether or not to use dynamic SQL. For example, if a dynamic SQL statement runs a little longer than a static SQL statement but saves days of programming cost, then perhaps dynamic SQL is the better choice. It all depends on what is more important ”the cost of development and maintenance or squeezing out every last bit of performance.

Overhead issues notwithstanding, there are valid performance reasons for favoring dynamic SQL, too. For example, dynamic SQL can enable better use of indexes, choosing different indexes for different SQL formulations. Properly coded, dynamic SQL can use the column distribution statistics stored in the DB2 catalog, whereas static SQL is limited in how it can use these statistics. Use of the distribution statistics can cause DB2 to choose different access paths for the same query when different values are supplied to its predicates.

The REOPT(VARS) bind parameter can be used to allow static SQL to make better use of non-uniform distribution statistics. When dynamic reoptimization is activated, a dynamic bind similar to what is performed for dynamic SQL is performed. For more information on reoptimization of static SQL, refer to Chapter 13, "Program Preparation."

Additionally, consider that the KEEPDYNAMIC bind option can enhance the performance of dynamic SQL. When a plan or package is bound specifying KEEPDYNAMIC(YES) , the prepared statement is maintained across COMMIT points. Contrast that with KEEPDYNAMIC(NO) , where only cursors using the WITH HOLD option keep the prepared statement after a COMMIT .

Dynamic SQL usually provides the most efficient development techniques for applications with changeable requirements (for example, numerous screen-driven queries). In addition, dynamic SQL generally reduces the number of SQL statements coded in your application program, thereby reducing the size of the plan and increasing the efficient use of system memory. If you have a compelling reason to use dynamic SQL, ensure that the reason is sound and complies with the considerations listed in the following section.