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 the figure below. 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.
supports the
ParameterMetaData interface. This interface describes the number, type, and properties
of prepared statement parameters. See the for
more information.
A connection need only compile a PreparedStatement once. Subsequent
executions can use the same statement execution plan even if the parameter
values are different, as shown in the following figure.
(PreparedStatements are not shared across connections.)
Prepared statements and the
statement cache
This figure shows multiple executions of the same PreparedStatement over a single connection. The single PreparedStatement object uses the same statement execution plan and statement cache.
- Even if your statement uses Statements instead of PreparedStatements, 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 the figure below.
When, in the same database, an application submits
an SQL Statement that exactly matches one already in the cache, 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
If your application executes statements that are almost
but not exactly alike, it is more efficient to use PreparedStatements with
dynamic or IN parameters.
The following figure shows how
can reuse a statement
execution plan that is already in the statement cache when the SQL text matches
a prior statement exactly, even when the statement is executed from a
different connection. PreparedStatements are much more efficient,
however.
Statements and the statement
cache
This figure shows how Derby can reuse a statement execution plan that is already in the statement cache, even when the statement is executed from a different connection. The figure shows three executions of two similar statements over two different database connections. Each database connection has its own statement cache. One statement is "SELECT * FROM mytable WHERE id = ?". The other statement is "SELECT * FROM mytable WHERE id = 2". The statement that uses the dynamic parameter is executed on both Connection One and Connection Two. When it is executed the second time, on Connection Two, it can use the statement execution plan that is already in the statement cache of Connection One. The version that does not use a dynamic parameter is executed on Connection Two only and uses the statement cache for Connection Two.