Using the statement cache

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

Remember: 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 Derby 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.

Figure 1. 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.

The derby.language.statementCacheSize property controls the number of precompiled statements which Derby keeps in its statement cache. Consider raising this number if statement preparation is taking too much time. See the Derby Reference Manual for more information on this property.