I did some tests with inserting large amounts of data into MySQL 5.0.15, to see what the fastest way was and to insert via JDBC.

I used the a table format, which has about 16 rows in it and I inserted into an empty table 100,000 of the same rows. There were no indexes and no keys on the table.

Three scenarios.

 1. 100,000 different inserts each. With one commit at the end. Took 1917  seconds.

 2. 100,000 rows inserted with a statement in 1000 batches of 100. Took 35 seconds.

 3. 100,000 rows inserted with a preparedstatement in 1000 batches of 100. Took a blistering 15 seconds!.

This of course has no business logic and no string handling implications as I’m inserting the same row over and over again. I just wanted to see what

 MySQL can handle and its pretty interesting that it handles things at this speed.

 After doing some research the biggest expenses with JDBC are in this order:

    1. network I/O

    2. disk I/O

    3. DB server CPU cycles are way down on this list being relatively

       cheap.

 Moral of the story is fastest way to load tables in Java is with prepared statements and batches. I have not tried LOAD DATA IN in mysql and this may be faster but of  course this gives you no ability to insert any business logic.

Example of Batch Insert Code

Add a comment

Posted by Robert Sinner on November 7, 2007 5:30:00 PM PST #