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.

Solution 1

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.

Solution 2

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.

Comments

[j23]

Is there any rule of thumb ? My app executes sequentially around 100 different sqls, Thus I guess with PreparedStatement pool equal to 50 hit ratio will be 0 (LRU ?).

[Steve Waldman]

the simplest thing to do is use maxStatementsPerConnection and set it to the number of PreparedStatements frequently used by your application. (that is ignore Statements that are used on app initialization, or much less frequently used for administrative purpose, etc.) if your app always sequentially executes 100 distinct PreparedStatements then, sure, try setting maxStatementsPerConnection to 100, but there is a risk that the memory and resource footprint of that will outweigh any benefit and you'll be better off without. you'll have to see.

[DanielCuadra]

@j23 there's a rule of thumb: never use statement caching on DB objects (tables, views, functions, etc) that are not stable (i.e., altering a table, dropping a column, etc), since statement caching will start having issues such as columns types mismatch, binding values mismatch, etc.