Bulk insert into a MySql Database

At RezzMap we use Java and MySQL for the RezzMap application and have the need to bulk insert lots of records into a large table. These inserts have started to become a bottleneck in the processing of the data required to run the service. So I started to look for a better way. Up until this point we were doing the classic batch inserts using a PreparedStatment and executeBatch. But even after adding the parameter rewriteBatchedStatements to our JDBC url the inserts that were too slow. So after reading all over the place that LOAD DATA INFILE was “up to 20 times faster” than batched inserts I was considering doing that. In looking at this solution I wasn’t really thrilled about writing the data out to temporary file and then running SQL to pull from that file. So then did a little digging around and found an old announcement by MySql of a, at the time, new method setLocalInfileInputStream (included in Connector/J 5.1.3 and later):

* New methods on com.mysql.jdbc.Statement: setLocalInfileInputStream() and
getLocalInfileInputStream():
* setLocalInfileInputStream() sets an InputStream instance that will be used
to send data to the MySQL server for a "LOAD DATA LOCAL INFILE"
statement rather than a FileInputStream or URLInputStream that represents the path given as an argument to the statement.

So after reading this the path seemed easy: build a InputStream that has a tab-delimited set of data that I can pass to this method and then call LOAD DATA LOCAL INFILE and get all of the benefits of the bulk loader. To get this working I took the path of least resistance and built up a String using StringBuilder and then used the method toInputStream(String input) of class org.apache.commons.io.IOUtils to convert my String to an InputStream. After doing this I compared the two methods of inserting data on my wimpy Dell Laptop and got the following results: