PreparedStatement caching - what does it mean (how does it work)

Question

I'm using for example c3p0 with some defined "maxStatements" for preparedStatement caching. What does this caching really do? What kind of data it caches. On what level (db, application,..)? It would be nice to understand it from example. For example i have a query

select * from sometable where somecolumn=?

Now i send it in prepared statement that is not cached. And now i'm sending it and it is cached. What is the difference. What happened in the first case and in the second. What is sent to DB server in the first case and in the second?

Thanks.

Answer 1

John Watts' answer is very good.

Note that there is no example code that can be provided because Statement caching is transparent: code that uses it looks exactly like code that does not. You just turn Statement caching on, in c3p0, by setting maxStatements and or maxStatementsPerConnection to a positive value.

Any performance benefit from statement caching is database/JDBC driver dependent. To see if statement caching helps, try to profile your app first with statement caching off and then with maxStatementsPerConnection set to the number of prepared statement queries that your app uses repeatedly. For some apps/databases/drivers, you'll see a significant benefit. For others you won't see any material benefit.

Answer 2

Without caching, you will get a new PreparedStatement each time you request one from the Connection. With caching, you will frequently get the exact same Java object of type PreparedStatement if you provide the same SQL string. If you provide the same SQL to a PreparedStatement, even with different parameters, often the database can reuse information like the execution plan, but only if you continue to use the same PreparedStatement. Caching makes that easier by not requiring your app to hold on to that PreparedStatement reference itself.