Is it expensive to hold on to PreparedStatements? (Java & JDBC)

Question

I'm trying to figure out if it's efficient for me to cache all of my statements when I create my database connection or if I should only create those that are most used and create the others if/when they're needed..

It seems foolish to create all of the statements in all of the client threads. Any feedback would be greatly appreciated.

Answer 1

A bit decent database will already cache them. Just fire Connection#prepareStatement() at the moment you actually need to execute the query. You actually also have no other choice since connection, statement and resultset ought to be acquired and closed in the shortest possible scope, i.e. in a try-finally block in the very same method as you execute the query.

Opening and closing the connection on every query in turn may indeed be expensive. A common solution to that is using a connection pool, for example c3p0.

Answer 2

This sounds to me like the kind of premature optimization that I wouldn't worry about until I have some information telling me that it mattered. If your database access is inefficient, I'd suspect your schema or access of values before I'd think of caching prepared statements.

Answer 3

I think you're worrying too much, prepared statements already benefit from several level of caching:

• At the database level: a decent database will reuse the access plan for a given prepared statement.
• At the connection pool level: a decent connection pool will cache PreparedStatement objects for each database connection in the pool (and return a cached PreparedStatement on subsequent calls to preparedStatement on a connection).

So actually, I would even say that you might be looking in the wrong direction. The best practice if you want to design a scalable solution is to use a connection pool and to not held a connection longer than needed and to release it (to release database resources) when you're done with it.