Client-side emulation prepared statement in MySQL JDBC driver

3
votes

I am trying to understand how client-side emulation prepared statement in MySQL JDBC driver works.

Part 1 I read online that for prepared statements, there are four steps involved when a relational database handles a JDBC/SQL query and they are as follows:

  1. Parse the incoming SQL query
  2. Compile the SQL query
  3. Plan/optimize the data acquisition path
  4. Execute the optimized query / acquire and return data

The pre-execution of steps compiles the SQL statement and hence provides pre-optimization. For server-side prepared statement, an additional round-trip will be made to the database to precompile the SQL statement.

Question How do the client-side emulation prepared statement do step 3 if it does not make a round-trip to the database? Or does a client-side emulation prepared statement works differently?

Part 2 I have also done two experiments.

  1. Experiment 1 - using one client-side prepared statement for each query
  2. Experiment 2 - 'reusing' client-side prepared statements for the same query multiple times

Both experiments show improvement in performance such as response time. Experiment 1 has an improvement of about 18% and Experiment 2 has an improvement of about 30%.

Question

  1. Am I right to assume that preoptimization still exists for client-side prepared statement?
  2. If yes, does it preoptimizes in a way that is similar to server-side (the four steps mentioned in part 1) prepared statements or in a much different way?
  3. If no, why is there still improvement?

Thanks for your help!

1
javamysqljdbcprepared-statementconnector-j
You are having the - as far as I am aware - false assumption that client side prepared statement in MySQL Connector/J have a performance benefit. Have you actually tested your part 2 with a normal Statement? The performance benefits you observe most likely stem from caching. – Mark Rotteveel
Hi Mark, I have tested the part 2 with a normal statement as well. The normal statement was a baseline for my experiments. In fact, I ran two other experiments with server-side prepared statements as well. If I reuse the server-side prepared statements, the performance improves in comparison to normal statement. If I do not reuse the server-side prepared statements, performance drops in comparison to normal statement. For the server-side, it is working as hypothesis since an additional round-trip has to be made to the database. – Tay Wen Bin
I just checked the default setting for cached preparedstatements, it is set to false for Connector/J. Thanks mark, you have been a great help! :) – Tay Wen Bin

1 Answers

3
votes

Simple answer: It doesn't. The MySQL driver - by default - will simply execute a query string created from the parameterized query and the (escaped) parameter values. It will create this query locally by replacing the parameter placeholders with the escaped values and then send it to the server

Note that JDBC does not require that a PreparedStatement is actually prepared server side, nor that it 'improves' performance. JDBC only requires that they work; as in: you can specify queries with ? as parameterplaceholders, and the driver will correctly execute the statement with the values set through the setXXX methods.

Also note that MySQL does have server side prepared statements, but you need to explicitly enable this using the connection property useServerPrepStmts with value true.