JDBC SLOW Batch Insert! I mean, REALLY SLOW?

Question

Here's the deal:

• I am creating a conn connection using DriverManager
• I installed conn.autoCommit(false);
• Then I have PreparedStatement pStat = conn.prepareStatement(insert_string_with_parameter);
• I set a few parameters using pStat.set ... then add the package with pStat.addBatch();
• Every 10,000 lines (I call addBatch() 10,000 times), I call pStat.executeBatch();
• Not sure if necessary, but I also call pStat.clearBatch() right after

Even if all this sounds good to me, it is SLOW !!! .

I have an average of only 35 records (only 8 columns in total, only a technical one that automatically increases the primary key and some non-zero restrictions) per second. I figured it would take me a week to insert all my 20mm rows ...

Am I doing something wrong?

How many rows should I try to add in each periodic cycle? Too many 10,000?

Answer 1

If you use MySQL with the JDBC driver near version 5.1.7, you may encounter an error that slows down the package insert , Update 5.1.10 or later should take care of this.