I’ve been asked to explain the difference between server-side and client-side caching of database prepared statements. So here is a brief overview.
First of all, query plans are not cached on the client-side (it would be a bit foolish, because it would not give any performance boost, because we would have to send the query plan over the network each time before execution, and it contains more structured information than just a SQL string).
The parsed query plan is only stored on the server side, in a common session cache, so all sessions can reuse it. This comprises the biggest part of queries optimisation by the database server (in terms of usage of both CPU time and memory).
What is cached on the client side? – It is an identifier of the parsed query (cache then looks like a map of [sql => id]). Another big difference from the server-side cache is that the client-side cache of prepared statements resides in scope of a session (connection). (I’m not sure about the rest of the JDBC drivers, but in the Oracle implementation query parameters are also cached, which would create a memory “leak” problem when pooled connections are actively used for some period of time, hence they don’t get closed, and the cache doesn’t get released.)
In general, the prepared statement execution looks like (in terms on commands sent to the database server):
1. id = PREPARE (sql) <- here on the server there will be an attempt to get the query plan from the cache.
2. EXECUTE (id, params1)
3. (optional) EXECUTE (id, params2) …
You can see that PREPARE for the same query is executed only once throughout the session (the lifetime of the connection). Considering that in most scenarios connection pool is used, it allows you to save relevant amount of time by not performing an additional PREPARE request on the network before each consecutive EXECUTE.
Tags: caching, database, jdbc, prepared statement
