Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-99

Support for batch statements rewrite



    • Task
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 1.1.7
    • 1.1.8
    • None
    • None
    • Long network delay between client and server.


      Mysql JDBC Connector has a rewriteBatchedStatements proptery (http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html) that does 2 things:
      1. INSERT statements are is rewritten into a long INSERT INTO table VALUES(0, 'name0'), (1, 'name1') … query
      2. Try to uses the “multiple queries” feature for things that does not fit (1.).

      Would it be possible to implement support for this in MariaDB JDBC connector as well? It could be useful if the distance (ping) between client and server is high.

      The performance gain for (1.) is very high. Although to be fair, a workaround is to use LOAD DATA INFILE that has equal or greater performance.

      The performance gain for (2.) is medium, because it looks like (at least for me) that the server will reply with one TCP segment per query. At least the transfer of data to the server is more efficient.


      package se.lesc;
      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.PreparedStatement;
      import java.sql.SQLException;
      import java.sql.Statement;
      import org.junit.Before;
      import org.junit.Test;
      public class JDBCBatchStatements {
          private Connection connection;
          public void setUp() throws SQLException {
              String url = "jdbc:mysql://";
              //rewriteBatchedStatements is supported by Mysql JDBC
              url += "&rewriteBatchedStatements=true";
              connection = DriverManager.getConnection(url);
              Statement statement = connection.createStatement();
              statement.execute("DROP TABLE IF EXISTS persons");
              statement.execute("CREATE TABLE persons (ssn int, name VARCHAR(40))");
          public void testInsert() throws SQLException  {
              PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO persons VALUES(?, ?)");
              for (int i = 0; i < 1000; i++) {
                  preparedStatement.setInt(1, i);
                  preparedStatement.setString(2, "name" + i);
              //Mysql JDBC driver will rewrite the SQL into:
              //INSERT INTO persons VALUES(0, 'name0'),(1, 'name1'),(2, 'name2'),(3, 'name3') ...
          public void testUpdate() throws SQLException  {
              testInsert(); //Populate the table
              PreparedStatement preparedStatement = connection.prepareStatement("UPDATE persons SET name = ? WHERE SSN = ?");
              for (int i = 0; i < 1000; i++) {
                  preparedStatement.setString(1, "updated name" + i);
                  preparedStatement.setInt(2, i);
              //Mysql JDBC driver will rewrite the SQL into:
              //UPDATE persons SET name = 'updated name0' WHERE SSN = 0;UPDATE persons SET name = 'updated name1' WHERE SSN = 1; ...
              //Unfortunately there will be one response packet per query: 
              //Rows matched: 1  Changed: 1  Warnings: 0
              //It resulted in 1501 TCP segments (including ACK) for me


        Issue Links



              massimo.siani Massimo Siani (Inactive)
              lennartschedin Lennart Schedin
              0 Vote for this issue
              2 Start watching this issue



                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.