[ACCEPTED]-PreparedStatement caching - what does it mean (how does it work)-c3p0

Accepted answer
Score: 17

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

Score: 12

John Watts' answer is very good.

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

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

More Related questions