Author: kristwaa Date: Thu Jul 10 04:01:52 2008 New Revision: 675508 URL: http://svn.apache.org/viewvc?rev=675508&view=rev Log: DERBY-3313: JDBC client driver statement cache. Added initial package description (package.html). Patch file: n/a Added: db/derby/code/trunk/java/client/org/apache/derby/client/am/stmtcache/package.html (with props) Added: db/derby/code/trunk/java/client/org/apache/derby/client/am/stmtcache/package.html URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/client/org/apache/derby/client/am/stmtcache/package.html?rev=675508&view=auto ============================================================================== --- db/derby/code/trunk/java/client/org/apache/derby/client/am/stmtcache/package.html (added) +++ db/derby/code/trunk/java/client/org/apache/derby/client/am/stmtcache/package.html Thu Jul 10 04:01:52 2008 @@ -0,0 +1,126 @@ + + + + org.apache.derby.client.am.stmtcache + + + +

Core client side JDBC statement cache implementation files. The + purpose of the client side JDBC statement cache is primarily to avoid + 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.

+ +

Core cache implementation

+

The core cache implementation has three responsibilities: +

    +
  1. Hold items in the cache +

    The current implementation stores either + PreparedStatements or CallableStatements in + the cache. A key is computed for each item, and there can only + by one instance of an item per key.

  2. +
  3. Determine if an item is in the cache, and if so return it +

    When a statement is prepared, the cache is consulted to + see if there is already a statement available. All statements + in the cache are by design free and available for use. A key + for the statement is computed to do the lookup. If there is a + statement available, it is taken out from the cache. When the + client is done with the statement, the cache is consulted again + to see if an identical statement (with the same key) has been + inserted. If so, the statement is closed. If not, it is + reinserted into the cache. This procedure allows us to use the + LRU algorithm for eviction.

  4. +
  5. Throw out items when the cache capacity is exceeded +

    A LRU (least recently used) algorithm is used to select + which item to throw out. When a statement is thrown out, it is + permanently closed and all references to the object are + released.

  6. +

+ +

Behavioral implications

+

When the JDBC cache is enabled, there is one difference in behavior. + Some errors that were previously encountered during the prepare call, + might now happen during execute. As an example, consider a statement + referring a deleted table.

+

Say table A is created and populated, then queried using a + prepared statement. This statement will be cached. If table A + is later deleted, the prepared query referring to it will not be + invalidated. If the query is prepared again on the same connection, + the cached object will be fetched from the cache and the prepare seems + to have succeeded (it actually hasn't been performed). When the prepared + statement is executed, the error is detected on the server side and the + client is notified.

+ +

Performance considerations and requirements

+

In order to allow for improved performance from using the JDBC + statement cache, there are three requirements:

+ + +

Using the JDBC statement cache will make each physical connection + use more memory. The amount depends on how many statements the + connection is allowed to cache and how many statements are actually + cached.

+ +

Enabling the cache through JDBC

+

Currently, the JDBC statement cache can only be enabled by using + ClientConnectionPoolDataSource. Sample code:

+
+ClientConnectionPoolDataSource cpds = new ClientConnectionPoolDataSource();
+// Set the number of statements the cache is allowed to cache.
+// Any number greater than zero will enable the cache.
+cpds.setMaxStatements(20);
+// This physical connection will have JDBC statement caching enabled.
+PooledConnection pc = cpds.getPooledConnection();
+// Create a logical connection.
+Connection con = pc.getConnection();
+// Interact with the database.
+PreparedStatement ps = con.prepareStatement(
+    "select * from myTable where id = ?");
+...
+ps.close(); // Inserts/returns statement to the cache
+...
+con.close();
+
+// The next logical connection can gain from using the cache.
+con = pc.getConnection();
+// This prepare will cause a statement to be fetched from the local cache.
+PreparedStatement ps = con.prepareStatement(
+    "select * from myTable where id = ?");
+...
+
+// This disposes of the cache.
+pc.close();
+        
+ + +

Implementation note

+

The client XA connection pool reuses the same code as the + connection pool data source, and enabling the JDBC statement cache + for XA connections is a matter of changing a few lines of code. + However, it has not been investigated whether the XA aspect requires + something more with regards to statement caching.

+ +

The main JIRA issue for the client side JDBC statement cache is + DERBY-3313. + +

Most important classes interacting with the statement cache + (directly or indirectly):

+ + + Propchange: db/derby/code/trunk/java/client/org/apache/derby/client/am/stmtcache/package.html ------------------------------------------------------------------------------ svn:eol-style = native