Seize the deal!
Caching Best Practices
Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!
While researching for the Statement Caching chapter in my High-Performance Java Persistence book, I got the chance to compare how Oracle, SQL Server, PostgreSQL and MySQL handle prepare statements.
Thanks to Jess Balint (MySQL JDBC driver contributor), who gave a wonderful answer on StackOverflow, I managed to get a better understanding of how MySQL handles prepared statements from a database performance point of view.
Basically, there are two ways of preparing a statement: on the server-side or on the client-side.
The most common type is the server-side statement, which requires two database round-trips:
If the data access logic doesn’t cache prepared statements, the extra database round-trip can actually hurt performance. For this purpose, some database systems don’t default to server-side prepared statements and execute a client-side statement preparation instead.
To enable server-side prepared statement, the useServerPrepStmts property must be enabled.
When the statement is prepared on the client-side, the bind parameter tokens are replaced with actual parameter values prior to sending the statement to the database server. This way, the driver can use a single request to fetch the result set.
If you enjoyed this article, I bet you are going to love my upcoming Online Workshops!
- Caching Best Practices with JPA and Hibernate (2.5 hours) on the 30th of September
- High-Performance SQL (4 hours) on the 6th of October in collaboration with Voxxed Days Ticino
- High-Performance SQL (12 hours) starting on the 28th of October in collaboration with Bouvet
In a high-performance OLTP system, statement caching plays a very important role in lowering transaction latencies. To avoid preparing a statement multiple times, the MySQL driver offers a client-side statement cache. Being disabled by default, the cache is activated by the cachePrepStmts Connection property.
For client-side statements, the tokenized statement structure can be reused in-between different preparing statement calls. The cache is bound to a database connection, but when using a connection pool, the physical connection lifetime spans over multiple application-level transactions (so frequently executed statements can benefit from using the cache).
For server-side statements, the driver caches the ServerPreparedStatement, as well as the check for server-side statement support (not all statements are allowed to be prepared on the server-side).
Caching statements can have a significant impact on application performance. If you are interested in this topic, then you might as well check High-Performance Java Persistence since it dedicates one chapter to this particular topic.