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.
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.
| Reference: | How does the MySQL JDBC driver handle prepared statements from our JCG partner Vlad Mihalcea at the Vlad Mihalcea’s Blog blog. |
