Details

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

    Description

      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.

      JDBCBatchStatements.java

      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;
       
          @Before
          public void setUp() throws SQLException {
              String url = "jdbc:mysql://127.0.0.1/test?user=root&password=";
              //rewriteBatchedStatements is supported by Mysql JDBC
              //http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html
              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))");
          }
       
          @Test
          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);
                  preparedStatement.addBatch();    
              }
              //Mysql JDBC driver will rewrite the SQL into:
              //INSERT INTO persons VALUES(0, 'name0'),(1, 'name1'),(2, 'name2'),(3, 'name3') ...
              preparedStatement.executeBatch();
          }
          
          @Test
          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);
                  preparedStatement.addBatch();    
              }
              
              //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; ...
              preparedStatement.executeBatch();
              
              //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
          }
      }

      Attachments

        Issue Links

          Activity

            ams2990 Andy Shulman added a comment -

            Isn't the first part of this already implemented? I add rewriteBatchedStatements=true as a parameter and I see multiple batched insert statements rewritten.

            ams2990 Andy Shulman added a comment - Isn't the first part of this already implemented? I add rewriteBatchedStatements=true as a parameter and I see multiple batched insert statements rewritten.

            How it looks in Wireshark when running MariaDB JDBC version 1.1.7

            lennartschedin Lennart Schedin added a comment - How it looks in Wireshark when running MariaDB JDBC version 1.1.7
            lennartschedin Lennart Schedin added a comment - - edited

            I don't think there ins any batch rewrite support in version 1.1.7 of MariaDB JDBC. This is how it looks in Wireshark when I run my test case JDBCBatchStatements.testInsert():

            lennartschedin Lennart Schedin added a comment - - edited I don't think there ins any batch rewrite support in version 1.1.7 of MariaDB JDBC. This is how it looks in Wireshark when I run my test case JDBCBatchStatements.testInsert():

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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