Dynamic SQL is Slow

The true essence of the “Dynamic SQL is Slow” myth is rather simple; dynamic SQL can be slow—when done wrong.

The problem is that dynamic SQL is often used for the wrong reason, sometimes even without knowing. To clarify the confusion, I will use the following terms as explained:

Embedded SQL

Embedding SQL directly into program source code is very common in procedural database languages such as Oracle PL/SQL or Microsoft Transact-SQL. It is also possible to embed SQL into other languages such as C.

The benefit of embedded SQL is the smooth integration with the respective programming language. However, embedded SQL is compiled into the program. It can not change a runtime—it’s static.

Dynamic SQL

Dynamic SQL is handled as string within the application. The application may change the SQL string at runtime before passing it to the database layer. It is actually the most common way to access databases.

Static SQL

I use the term static SQL to describe SQL statements that do not change at runtime. No matter if it is embedded SQL that cannot change at runtime, or dynamic SQL that could change, but doesn’t.

The crux of these definitions is that a statement can be dynamic and static SQL at the same time. In other words, there are different levels of dynamic SQL. Consider the following example:

String sql = "SELECT first_name, last_name"
           + "  FROM employees"
           + " WHERE employee_id = " + employeeId;

ResultSet rs = con.executeQuery(sql);

Is that dynamic SQL? According to the definition above, it is. The SQL statement is prepared as string and passed to the database layer. But is it static SQL as well? Assuming that value of the employeeId variable changes, it’s not static SQL because the SQL string changes at runtime. It is an example for dynamic SQL that will actually hurt performance. The problem is not that it is dynamic SQL but that it doesn’t use bind variables. SQL bind variables—e.g., the question mark ? or :name—are placeholders for values that change during execution. That means, that the example can be turned into static SQL by using a bind variable instead of the actual value of the employeeId variable.

A reasonable use of dynamic SQL is to change the structure of the statement at runtime. That’s something that cannot be done with bind parameters. E.g., a conditional where clause:

String where = "";
if (subsidiaryId != null) {
   where += (where == "") ? " WHERE " : " AND " 
         +  "subsidiary_id = " + subsidiaryId;
}
if (employeeId != null) {
   where += (where == "") ? " WHERE " : " AND " 
         +  "employee_id = " + employeeId;
}
if (lastName != null) {
   where += (where == "") ? " WHERE " : " AND " 
         +  "UPPER(last_name) = '"+lastName.toUpperCase()+"'";
}
String SQL = "SELECT employee_id, first_name, last_name "
           + "  FROM employees" 
           +   where;
// execute SQL

The code builds an SQL statement to fetch employees based on any combination of three filter criteria. Although it is rather awkward, the constructed SQL can be executed using the best available index. Nevertheless, this approach is troublesome because of the possible SQL injection vulnerability and the high optimization overhead: that the database has to recreate the execution plan every time, because the search terms—that can be different every time—prevent caching. “Parameterized Queries” explains the optimization overhead in detail. Again, dynamic SQL is again not the problem but not using bind parameters is.

An example that builds the where clause dynamically and uses bind parameters is omitted because it is even more awkward as the example above. However, most ORM frameworks offer a sufficiently convenient way to dynamically create SQL using bind parameters. The following overview shows some samples:

Using dynamic SQL with bind parameters allows the optimizer to choose the best execution plan for the particular combination of where clauses. That will yield better performance than constructions like described in “Smart Logic”:

SELECT first_name, last_name
  FROM employees
 WHERE (     employee_id = ? OR ? IS NULL)
   AND (   subsidiary_id = ? OR ? IS NULL)
   AND (UPPER(last_name) = ? OR ? IS NULL)

The reason for the observation that dynamic SQL is slow is very often not using bind parameters—that is, using dynamic SQL for the wrong reason.

However, there are some—I’d say rare—cases when dynamic SQL can be slower than “smart logic” like above. That’s when very cheap (fast) SQL statements are executed at a very high frequency. But, first of all, there are two more terms to explain:

Hard Parsing

Hard parsing is constructing an execution plan based on the SQL statement. That’s a major effort; inspecting all parts of the SQL; considering all indexes; considering all join orders and so on. Hard parsing is very resource intensive.

Soft Parsing

Soft parsing is searching, finding and using a cached execution plan. There are some minor checks done, e.g., access rights, but the execution plan can be re-used as is. That is a rather fast operation.

The key to the cache is basically the literal SQL string—usually a hash of it. If there is no exact match, a hard parse is triggered. That’s why inlined literals—as opposed to bind parameters—trigger a hard parse unless the very same search terms are used again. But even in that case there are good chances that the previous execution plan was already expired from the cache because new ones are coming over and over again.

However, there is a way to execute a statement without any parsing at all—not even soft parsing. The trick is to keep the parsed statement open, e.g., like in the following Java pseudo-code:

PreparedStatement pSQL = con.prepareStatement("select ...");
for (String last_name:last_names) {
    pSQL.setString(1, last_name.toUpperCase());
    ResultSet rs = pSQL.executeQuery();
    // process result
}
pSQL.close();

Note that the PreparedStatement is opened and closed once only—yet it can be executed many times. That means that there is only one parsing operation—during prepare—but non inside the loop.

The pitfall is that converting the statement to dynamic SQL moves the prepareStatement call into the loop—causing a soft-parse for each execution. The parsing overhead, that might also include network latencies, can exceed the savings of a better execution plan when the statement is executed often and runs fast anyway. That’s especially true if the actual execution plan doesn’t vary for the different where clauses—e.g., because one well indexed where clause is always present.

Even though the “prepare before loop” trick is seldom used explicitly, it is very common in stored procedures—but implicit. Languages such as PL/SQL—with real static SQL—prepare the SQL when the procedure is compiled or, at most, once per execution. Changing that to dynamic SQL can easily kill performance.