Details
-
Task
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
1.1.7
-
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
|
}
|
}
|
