How does the MySQL JDBC driver handle prepared statements

Prepared statement types

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.

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.

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

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

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.

If you liked this article, you might want to subscribe to my newsletter too.

Advertisements

6 thoughts on “How does the MySQL JDBC driver handle prepared statements

  1. In a high-performance OLTP system, statement caching plays a very important role in lowering transaction latencies.

    Would be interesting to learn about what the scenario (per database) is when this is really a good idea. There seems to be quite a bit of premature optimisation risk in this area. With Oracle, I have not yet run into a situation where preventing soft-parses would have really helped substantially.

    1. Because Oracle caches the execution plans automatically, the soft-parse is already an optimization, as you mentioned it.
      Even if you reuse the statement on the client-side, you cannot escape the soft-parse which must apply the current parameter bind values.
      So on the server-side, there is not too much to improve with Oracle, other then making sure the execution plan is suitable for the current parameter value selectivity.
      On the client-side, the implicit client-side cache can speed-up the frequently executed queries (in some tests I got a 20% throughput improvement).

  2. Hi Vlad,

    Could u intro more about “If the data access logic doesn’t cache prepared statements, the extra database round-trip can actually hurt performance.” What is means of “the data access logic doesn’t cache prepared statements”? Could u provide some code snippets or references about this? Thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s