Prepared Statement and Statement/Query Caching

Question

I am trying to understanding if Statement caching is useful in case of parametrized prepared statements.

As per my understanding

• If I use caching, then query will cached based on its 'String'.
• In this case, if a query has different values of parameter then it is a different / new statement/string w.r.t. caching.
• Also, when parameters change, the results also change.
• Since prepared statements are parameterized, is it really useful to use caching in this case.

I am using JDBC/Snaq DB Pool/ MySQL database.

Statement Caching here is referred to two different cases:

• JAVA: Snaq DB pool provided ConnectionCache class which facilitates caching of Statements.
• MySQL: DB also provided ways to cache query and result.http://dev.mysql.com/doc/refman/5.1/en/query-cache.html

My confusion is simple:

• If I were to search a Query / Corresponding Result in a Cache, I will search it on basis of String comparison.
• If parameter values change, so does String of query.
• This will result in to different entries in cache for same query with different set of values of parameters.

Hope I clarify my question.

Answer 1

Statement caching is about caching the execution plan

JDBC query caching is done on the database side of things and it caches the execution plan, what the values of the parameters are is of no consequence, only that they are in the same order every time. If the values actually mattered there would be no point in caching anything.

Long time ago you had to use PreparedStatements to get execution plans to be cached, but since about 2005 - 2008 all the modern databases worth mentioning cache execution plans regardless of the type of statement that is executed.

There is some minimal client side caching of the actual Java Object that represents the PreparedStatement or CallableStatement but any actual savings in time or space will be minimal in a modern JDBC driver.

The overhead of calculating the execution plan on the server side is orders of magnitude larger than doing to simple String manipulation on the client side. This means there is no meaningful performance benefit on the client side of using a PreparedStatement, there are other more important benefits like SQL Injection protection to justify using one.