Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

MySQL: Server vs. Client-Side Prepared Statements in Java

DZone's Guide to

MySQL: Server vs. Client-Side Prepared Statements in Java

Basically, there are two ways of preparing a statement: on the server-side or on the client-side.

· Database Zone
Free Resource

Finding a database that fits with a container-based deployment model can be frustrating. Learn what to look for in a Docker database

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.

Server-Side Prepared Statements

The most common type is the server-side statement, which requires two database round-trips:

  • The driver submits a prepare request and the database parses the statement into a query tree, which can also be transformed into a pre-optimized tree structure. Because it is very difficult to build an execution plan without the actual bind parameter values, the execution plan is deferred until the statement gets executed
  • The execution request contains the current bind values, which the database uses to transform the parse tree into an optimal execution plan. The executor takes the plan and builds the associated result set.

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 statement and execute a client-side statement preparation instead.

To enable server-side prepared statement, the useServerPrepStmts property must be enabled.

If you enjoy reading this article, you might want to subscribe to my newsletter and get a discount for my book as well.

Vlad Mihalcea's Newsletter


Client-Side Prepared Statements

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.

Caching Statements

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).

If you enjoyed this article, I bet you are going to love my book as well.






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 subscribe for the High-Performance Java Persistence book status notification.

When you're looking for a SQL database that can scale elastically, while still preserving ACID guarantees, you only have a few choices. Find out how these elastic SQL databases perform in thishead-to-head YCSB benchmark.

Topics:
database ,mysql ,sql ,jdbc ,java

Published at DZone with permission of Vlad Mihalcea. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}