Derby JDBC client driver statement cache
========================================
Revision: 1.1, 2008-01-24
Created :      2008-01-10

1. Purpose

The statement cache primarily avoids the performance penalty of going
over the network from the client to the server when preparing a
statement that has already been prepared on the same connection.


2. What will be cached

The objects that will be cached are objects implementing
java.sql.PreparedStatement. Note that this also implies
java.sql.CallableStatment, which is a subclass of the former class.


3. Beneficial scenarios

The need for a client side JDBC statement cache typically arise when
using connection pooling, for instance as provided by an application
server in a multi-tier system. In many of these applications, the common
JDBC usage pattern is:
  a) Get a JDBC connection from the connection pool
  b) Prepare one or more statements as required
  c) Perform database operations
  d) Return connection to the connection pool

As noted earlier, a client side JDBC statement cache can reduce the cost
of step b. It should also be noted that the time from step a to step d
can be very short, which is why one or more extra round-trips hurt
performance severely.


4. Current situation

Derby already has a cache in the embedded driver for shared execution
plans, but neither the embedded nor the client driver have a JDBC
statement cache.


5. Current issues

There are two potential issues with the current implementation when used
in a scenario as described in chapter 2:
  a) Cost of unnecessary round-trips over the network
  b) Contention on the shared plan cache in the embedded driver on the
     server side.

6. Known prerequisites

To be able to determine if a JDBC statement in the cache is valid for
another prepare request, the current compilation schema on the server
must be known. This information is currently not readily available in
the client driver. Work is going on to fix it, see DERBY-3192.


7. Negative impacts

The client driver will have to maintain more state for connections with
a JDBC statement cache, and will use more memory resources than a
connection without such a cache. Note that only PooledConnection objects
will have this drawback, and that the JDBC statement cache can be
disabled by setting a property in the data source object creating the
connections. Normal Connection objects will not be affected.
XAConnections inherit from PooledConnection, and will experience the
same drawbacks and advantages.


8. Standard interfaces

Statement caching is described as part of chapter 11 ("Connection
Pooling") in the JDBC 4.0 specification. Chapters 11.6 and 11.7 contain
the most relevant information.


9. Implementation priorities

Work done under the issue DERBY-3313 will be prioritized as follows:
 1) Implement caching for PooledConnection
 2) If enough time, enable and verify implementation for XAConnection