Comparing Dynamic SQL with Static SQL

Determining whether to use static or dynamic SQL for performance is usually of great interest to programmers. The answer, of course, is that it all depends on your situation. Refer to Table 9.6 to help you decide whether to use static or dynamic SQL. Certain considerations, such as security, might dictate static SQL, or your environment (such as whether you are using DB2 CLI or the CLP) might dictate dynamic SQL.

Consider the following recommendations on whether to choose static or dynamic SQL in a particular situation. In the following table, “Either” means that there is no advantage to either static or dynamic SQL. Note that these are general recommendations only. Your specific application, its intended usage, and working environment dictate the actual choice. When in doubt, prototyping your statements as static SQL, then as dynamic SQL, and then comparing the differences is the best approach.

In general, an application using dynamic SQL has a higher start-up (or initial) cost per SQL statement due to the need to compile the SQL statements prior to using them. Once compiled, the execution time for dynamic SQL compared to static SQL should be equivalent and, in some cases, faster due to better access plans being chosen by the optimizer. Each time a dynamic statement is executed, the initial compilation cost becomes less of a factor. If multiple users are running the same dynamic application with the same statements, only the first application to issue the statement realizes the cost of statement compilation.

In a mixed DML and DDL environment, the compilation cost for a dynamic SQL statement might vary because the statement can be implicitly recompiled by the system while the application is running. In a mixed environment, your choice between static and dynamic SQL must also factor in the frequency in which packages are invalidated. If the DDL invalidates packages, dynamic SQL might be more efficient because only those queries executed are recompiled when they are next used. For static SQL, the entire package is rebound after it has been invalidated.

Now, suppose your particular application contains a mixture of these characteristics, some of which suggest that you use static while others suggest dynamic. In this case, there is no clear cut decision and you should probably use whichever method you have the most experience with, and with which you feel most comfortable. Note that the considerations in the Table 9.6 are listed roughly in order of importance.

Static and dynamic SQL each come in two types that make a difference to the DB2 optimizer. The Static types are:

• Static SQL containing no host variables— This is an unlikely situation which you might see only for initialization code and novice training examples.

  This is actually the best combination from a performance perspective because there is no run-time performance overhead and yet the DB2 optimizer's capabilities can be fully realized.

• Static SQL containing host variables— The traditional legacy style of DB2 applications. It avoids the run-time overhead of a PREPARE and catalog locks acquired during statement compilation. Unfortunately, the full power of the optimizer cannot be harnessed because it does not know the entire SQL statement. A particular problem exists with highly nonuniform data distributions.

The dynamic types are:

• Dynamic SQL containing no parameter markers— The typical style for random query interfaces (such as the CLP) and the optimizer's preferred flavor of SQL. For complex queries, the overhead of the PREPARE statement is usually worthwhile due to improved execution time.

• Dynamic SQL containing parameter markers— The most common type of SQL for CLI applications. The key benefit is that the presence of parameter markers allows the cost of the PREPARE to be amortized over the repeated executions of the statement, typically a SELECT or INSERT. This amortization is true for all repetitive dynamic SQL applications. Unfortunately, just like static SQL with host variables, parts of the DB2 optimizer will not work because complete information is unavailable. The recommendation is to use static SQL with host variables or dynamic SQL without parameter markers as the most efficient options.