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

Support for batch statements rewrite

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.1.7
    • Fix Version/s: 1.1.8
    • Component/s: None
    • Labels:
      None
    • Environment:
      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

              People

              Assignee:
              massimo.siani Massimo Siani (Inactive)
              Reporter:
              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.