Why is it impossible to serve connections from pool according to the prepared statement already executed with them?

Question

I've been researching all around the web the most efficient way to design a connection pool and tried to analyze into details the available libraries (HikariCP, BoneCP, etc.).

Our application is a heavy-load consumer webapp and most of the time the users are working on similar business objects (thus the underlying SQL queries executed are the often the same, but still there are numerous). It is designed to work with different DBMS (Oracle and MS SQL Server especially).

So a simplified use case would be :

• User goes on a particular JSP page (e.g. Enterprise).
• A corresponding Bean is created.
• Each time it realizes an action (e.g. getEmployees(), computeTurnover()), the Bean asks the pool for a connection and returns it back when done.

If we want to take advantage of the Prepared Statement caching of the underlying JDBC driver (as PStatements are attached to a connection - jTDS doc.), from what I understand an optimal way of doing it would be :

• Analyze what kind of SQL query a particular Bean want to execute before providing it an available connection from the pool.
• Find a connection where the same prepared statement has already been executed if possible.
• Serve the connection accordingly (and use the benefits of the cache/precompiled statement).
• Return the connection to the pool and start over.

Am I missing an important point here (like JDBC drivers capable of reusing cached statements regardless of the connection) or is my analysis correct ?

The different sources I found state it is not possible, but why ?

Answer 1

For your scheme to work, you'd need to be able to get the connection that already has that statement prepared.

This falls foul on two points:

1. In JDBC you obtain the connection first,
2. Cached prepared statements (if a driver or connection pool even supports that) aren't exposed in a standardized way (if at all) nor would you be able to introspect them.

The performance overhead of finding the right connection (and the subsequent contention on the few connections that already have it prepared) would probably undo any benefit of reusing the prepared statement.

Also note that some database systems also have a serverside cache for prepared statements (meaning that it already has the plan etc available), limiting the overhead from a new prepare from the client.

If you really think the performance benefit is big enough, you should consider using a data source specific for this functionality (so it is almost guaranteed that the connection will have the statement in its cache).

Answer 2

A solution could be for a connection pool implementation to delay retrieving the connection from the pool until the Connection.prepareStatement() is called. At that time a connection pool would look up available connections by the SQL statement text and then play forward all the calls made before Connection.prepareStatement(). This way it would be possible to get a connection with a ready PreparedStatement without the issues other guys suggested.

In other words, when you request a connection from the pool, it would return a wrapper that logs everything until the first operation requiring DB access (such as prepareStatement() is requested.

You'd need to ask a vendor of your connection pool functionality to add this feature.

I've logged this request with C3P0:

https://github.com/swaldman/c3p0/issues/55

Hope this helps.