Reusing a PreparedStatement

[sm@h]

I realise this is more a question on spring usage then on spring batch usage, but it is caused due to the way spring-batch works so bare with me...

One of our jobs is taking up way too much processing time, we have seen that a lot of time is lost in preparing the same statement over and over again. So I would like to created the prepared statement once and reuse it. Correct me if I'm wrong but using Spring's JdbcTemplate the only way to do this is to use a callback:

jdbcTemplate.execute("select count(*) from A where B = ?", new PreparedStatementCallback() {
public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
for (int i=0; i<listOfBs.length; i++) {
ps.setString(1, listOfBs[i]);
ResultSet rs = ps.executeQuery();
if rs.next()
listOfBCounts[i] = ps.getLong
}
return null;
}
});

This is fine if you can do all your processing inside a single callback. However, in spring batch typically the listOfBs is being provided by a reader and the execute is happening inside a processor. (Or more likely inside a dao called by the processor)

Obviously this means the preparedstatement can be reused multiple times for the same B (inside a single callback) but not for all B's. Is there another way to accomplish this?

[wpoitras]

You can use a version of execute which also takes a PreparedStatementCreator. You can then create an implementation which takes an SQL string and caches the resulting PreparedStatement. There may be issues with this because I believe Spring always calls close on the PreparedStatement objects. So you might have to return a proxy which suppresses the close.

This shouldn't be necessary if your database connection pool does PreparedStatement caching itself. DBCP does

[sm@h]

I'll give the PreparedStatementCreator in combination with a proxied PreparedStatment a try, thanks for the tip.

I am aware of the possibility to cache statements in a connection pool, however we need to use a DB2Datasource because it exposes some specific features that DBCP does not. Unfortunately this datasource does not appear to support such a cache.

[wpoitras]

Another more verbose but cleaner solution would be to create a DataSource proxy which wraps your DB2DataSource object which creates proxies that mostly passes through most calls, but caches the PreparedStatements.

This allows you to add functionality at the JDBC level without affecting your application code. I imagine this is similar to what Websphere (and other pool implementations) do when implementing various types of caching and pooling.

[lucasward]

This shouldn't be necessary if your database connection pool does PreparedStatement caching itself.

This is the key issue here, even if you use the same java object PreparedStatement, it doesn't mean that you will save any time. When the database is given a sql statement, it creates an access plan, then executes the statement for the given plan. If you pass the same statement (i.e. the same sql with the question marks in the same place, etc) then it will not regenerate the access plan, but use the one from before, this is what will save you time. This is usually refferred to as PreparedStatementCaching, but it's really separate from whether or not you use the same object in java.

[wpoitras]

Yeah, caching can take place on the database and the middleware. I think its more important to at least enable it on the database side, but for real high performance applications, depending on how long the JDBC driver takes to create a PreparedStatement, the middleware side can be a savings too.

[lucasward]

That's a fair point, you may need to do a bit of investigation in your driver. You can use a custom PreparedStatementCreator to cache it, although I think there may be some managers that do this for you (certainly there are in app servers, unless I'm mistaken)

[sm@h]

When the database is given a sql statement, it creates an access plan, then executes the statement for the given plan. If you pass the same statement (i.e. the same sql with the question marks in the same place, etc) then it will not regenerate the access plan, but use the one from before, this is what will save you time.

In our case the database creates a new plan every single time, eventhough we are passing the same sql string with the question marks in the same place. I'm assuming that prepared statement caching is enabled on the database as none of the other batches (written in Cobol) seem to suffer from this excessive prepare phase for the statements.

This is usually refferred to as PreparedStatementCaching, but it's really separate from whether or not you use the same object in java. Even if you use the same java object PreparedStatement, it doesn't mean that you will save any time.

I thought reusing the same object would somehow force the JDBC driver to behave correctly and make the DB take advantage of the existing plan, but perhaps you are right and the 2 concepts are totally seperated. I'll find out soon enough...