Oracle JDBC driver statement cache vs BoneCP statement cache?

Question

I'm using Oracle JDBC driver and evaluate BoneCP. Both implement a statement cache.

I am asking myself whether I should use the one or the other for statement caching. What do you think? What are the advantages or disadvantages for each way?

Answer 1

As author of BoneCP, I can fill in my part:

• Using the pool cache gives you the possibility of giving you a stack trace if you forget to close off your statements properly. If you're using hibernate/spring jdbc template or some other managed connection this is irrelevant since it will always be closed off for you.

• The statement cache is tied to each connection so if you have 100 connections and you keep executing the same statement each time, it will take a while until every connection fills up it's cache. If the DB supports it, the driver might have some special tweaks to only prepare this statement once but this is not in the JDBC spec and so a connection pool will not have any such facility to optimize for this if at all possible. On the other hand, you can tell the pool to give you a connection in LIFO mode which will greatly improve the odds of you hitting a hot cache.

• Performance wise there shouldn't be too much of a difference since in the end they both try to reuse a statement. However several driver adopt the blind approach to synchronize at a method level while in BoneCP I always try to use as fine-grain a lock as possible so in theory this should provide for greater scalability.

Summary: Both should perform roughly the same -- if not it's probably a design bug somewhere.