javamysqljdbcprepared-statementconnector-j

Client-side emulation prepared statement in MySQL JDBC driver

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!

Solution

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