I have a scenario where I need to write lots of records into SingleStore via a java application. This is a scenario where I would normally use JDBC “batching” to ensure it performs well. However, I am experiencing very poor performance when using JDBC batching with SingleStore.
We are on SingleStore 8.0.8 with mysql java connector version 8.1.0.
To demonstrate:
CREATE TABLE JDBC_BATCH_TEST(
COL1 varchar(64)
);
Class.forName("com.mysql.jdbc.Driver");
String sql = "insert into JDBC_BATCH_TEST (COL1) values (?)";
try (Connection conn = DriverManager.getConnection(
"jdbc:mysql://hostname:3306/dbname",
"username",
"password"); PreparedStatement pstmt = conn.prepareStatement(sql)) {
conn.setAutoCommit(false);
long start = System.currentTimeMillis();
int count = 100;
for (int i = 0; i < count; i++) {
int col = 1;
pstmt.setString(col++, "" + i);
pstmt.addBatch();
}
System.out.println("Done binding " + count + " rows in " + (System.currentTimeMillis() - start) + "ms");
start = System.currentTimeMillis();
pstmt.executeBatch();
System.out.println("Wrote " + count + " rows in " + (System.currentTimeMillis() - start) + "ms");
conn.commit();
}
Output:
Done binding 100 rows in 2ms
Wrote 100 rows in 6122ms
That is a very long time for inserting 100 rows.
By comparison, if I do the whole thing as a single insert, it is very fast.
Class.forName("com.mysql.jdbc.Driver");
int count = 100;
String sql = "insert into JDBC_BATCH_TEST (COL1) values ";
for (int i = 0; i < count; i++) {
if (i != 0) { sql += ", "; }
sql += "(?)";
}
try (Connection conn = DriverManager.getConnection(
"jdbc:mysql://hostname:3306/dbname",
"username",
"password"); PreparedStatement pstmt = conn.prepareStatement(sql)) {
conn.setAutoCommit(false);
long start = System.currentTimeMillis();
int col = 1;
for (int i = 0; i < count; i++) {
pstmt.setString(col++, "" + i);
}
System.out.println("Done binding " + count + " rows in " + (System.currentTimeMillis() - start) + "ms");
start = System.currentTimeMillis();
pstmt.execute();
System.out.println("Wrote " + count + " rows in " + (System.currentTimeMillis() - start) + "ms");
conn.commit();
}
Done binding 100 rows in 1ms
Wrote 100 rows in 125ms
I don’t want to use this workaround for the final solution (my real table has dozens of columns, and it doesn’t make sense to generate such a huge VALUES statement) This is just to illustrate the fact that the insert of 100 rows itself is fast.
The issue here has something to do with mysql jdbc batching not playing properly with SingleStore. Or perhaps I am misusing it and you can clarify?