Avoiding compiling SQL statements

When you submit an SQL statement to Derby, Derby compiles and then executes the statement. Compilation is a time-consuming process that involves several steps, including optimization, the stage in which Derby makes its statement execution plan. A statement execution plan includes whether to use an index, the join order, and so on.

Unless there are significant changes in the amount of data in a table or new or deleted indexes, Derby will probably come up with the same statement execution plan for the same statement if you submit it more than once. This means that the same statements should share the same plan, and Derby should not recompile them. Derby allows you to ensure this in the following ways (in order of importance):

The statement cache is enabled by default. You can use it to avoid extra compilation overhead:

• Your application can use PreparedStatements instead of Statements.

  PreparedStatements are JDBC objects that you prepare (compile) once and execute multiple times. (See Figure 1.) If your application executes statements that are almost but not exactly alike, use PreparedStatements, which can contain dynamic or IN parameters. Instead of using the literals for changing parameters, use question marks (?) as placeholders for such parameters. Provide the values when you execute the statement.

Derby supports the ParameterMetaData interface, new in JDBC 3.0. This interface describes the number, type, and properties of prepared statement parameters. See the Derby Developer's Guide for more information.

Figure 1. A connection need only compile a PreparedStatement once. Subsequent executions can use the same statement execution plan even if the parameter values are different. (PreparedStatements are not shared across connections.)

• Even if your statement uses Statements instead of PreparedStatements, Derby can reuse the statement execution plan for the statements from the statement cache. Statements from any connection can share the same statement execution plan, avoiding compilation, by using the single-statement cache. The statement cache maintains statement plans across connections. It does not, however, maintain them across reboots. (See Figure 2.)

  When, in the same database, an application submits an SQL Statement that exactly matches one already in the cache, Derby grabs the statement from the cache, even if the Statement has already been closed by the application.

  To match exactly with a statement already in the cache, the SQL Statement must meet the following requirements:

  • the text must match exactly
  • the current schema must match
  • the unicode flag that the statement was compiled under must match the current connection's flag

Note:

Remember that if your application executes statements that are almost but not exactly alike, it is more efficient to use PreparedStatements with dynamic or IN parameters.

Figure 2. A database can reuse a statement execution plan when the SQL text matches a prior statement exactly. (PreparedStatements are much more efficient.)