JDBC for MySQL - Herong's Tutorial Examples - v3.13, by Herong Yang
Performance of Inserting Rows with a ResultSet
This section describes how to measure the performance of inserting rows using a ResultSet object.
Since ResultSet objects can also be used to insert rows, I wrote the following Java sample program to measure the performance of inserting multiple rows using a ResultSet object:
/* MySqlPerformanceResultSet.java * Copyright (c) HerongYang.com. All Rights Reserved. */ import java.util.*; import java.sql.*; import javax.sql.*; public class MySqlPerformanceResultSet { public static void main(String [] args) { Connection con = null; try { com.mysql.cj.jdbc.MysqlDataSource ds = new com.mysql.cj.jdbc.MysqlDataSource(); // com.mysql.jdbc.jdbc2.optional.MysqlDataSource ds // = new com.mysql.jdbc.jdbc2.optional.MysqlDataSource(); ds.setServerName("localhost"); ds.setPortNumber(3306); ds.setDatabaseName("HerongDB"); ds.setUser("Herong"); ds.setPassword("TopSecret"); ds.setServerTimezone(java.util.TimeZone.getDefault().getID()); con = ds.getConnection(); // Delete all rows from the table Statement sta = con.createStatement(); sta.executeUpdate("DELETE FROM Profile"); // Start the test int count = 10000; long t1 = System.currentTimeMillis(); // ResultSet to insert rows Statement rs = con.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); ResultSet res = rs.executeQuery("SELECT * FROM Profile"); res.moveToInsertRow(); Random r = new Random(); for (int i = 0; i < count; i++) { res.updateString("FirstName", Integer.toHexString(r.nextInt(9999))); res.updateString("LastName", Integer.toHexString(r.nextInt(999999))); res.insertRow(); } rs.close(); // End the test long t2 = System.currentTimeMillis(); System.out.println("ResultSet insert "+count +" rows with "+(t2 -t1) +" milliseconds"); con.close(); } catch (Exception e) { System.err.println("Exception: "+e.getMessage()); e.printStackTrace(); } } }
Here is the result on a macOS system with a 2.6 GHz processor, running JDK 13, MySQL 8.0.17 and MySQL Connector/J 8.0.19:
ResultSet insert 10000 rows with 5492 milliseconds
When I look at the inserted records, I notice that the insertRow() method failed to store default values on BirthDate and ModTime except for the first records. Here is the output of the first 10 records:
+-------+-----------+----------+------------+---------------------+ | ID | FirstName | LastName | BirthDate | ModTime | +-------+-----------+----------+------------+---------------------+ | 60006 | 547 | 22741 | 2000-12-31 | 2027-01-01 00:00:00 | | 60007 | a2f | 8954f | NULL | NULL | | 60008 | 2632 | 74f88 | NULL | NULL | | 60009 | 1680 | a1708 | NULL | NULL | | 60010 | 1234 | 984fc | NULL | NULL | | 60011 | 20d4 | 789e8 | NULL | NULL | | 60012 | 2037 | 79ba0 | NULL | NULL | | 60013 | f71 | d6b24 | NULL | NULL | | 60014 | 34f | add23 | NULL | NULL | | 60015 | 2310 | 889ca | NULL | NULL | +-------+-----------+----------+------------+---------------------+
I am not sure what is the root cause of this issue.
Here is summary of performance results on this program in the past with different versions of storage engine, MySQL, Java, and JDBC driver:
Operation: ResultSet INSERT Execution Storage MySQL Java JDBC Computer CPU Time (ms) Engine Server Version Driver System GHz --------- ------- ------ ------- ------ ------- --- 5492 MyISAM 8.0 13 8.0 macOS 10 2.6 2312 MyISAM 5.5 8 5.1 Windows 7 2.5 923466 InnoDB 5.5 8 5.1 Windows 7 2.5 2312 MyISAM 5.0 6 5.0 Windows XP 1.0
As you can see, MySQL and JDBC driver 8.0 is much slower than MySQL and JDBC driver 5.5. Why? Maybe macOS 10 is slower than Windows 7?
Here is the comparison between PreparedStatement, regular and ResultSet INSERT operations on a macOS system with a 2.6 GHz processor, running JDK 13, MySQL 8.0.17 and MySQL Connector/J 8.0.19. Using ResultSet to insert records is about 1.7 times slower.
Storage engine: MyISAM Operations # of inserts Time in ms. Comparison PreparedStatement 10000 3314 100% Regular INSERT 10000 3348 101% ResultSet 10000 5492 166%
Table of Contents
JDBC (Java Database Connectivity) Introduction
MySQL JDBC Driver (MySQL Connector/J)
PreparedStatement with Parameters
PreparedStatement in Batch Mode
Performance of Inserting Rows with a PreparedStatement
InnoDB (MySQL 5.5 Default Engine) Slower on INSERT
Performance of Inserting Rows with a Regular Statement
►Performance of Inserting Rows with a ResultSet
MySQL - Reference Implementation of JdbcRowSet
MySQL - JBDC CallableStatement
MySQL CLOB (Character Large Object) - TEXT
MySQL BLOB (Binary Large Object) - BLOB