JDBC batch insert being SLOW! I mean, REALLY SLOW?

Question

Here's the deal:

1. I create a connection conn using the DriverManager
2. I set conn.autoCommit(false);
3. Then I have PreparedStatement pStat = conn.prepareStatement(insert_string_with_parameter);
4. I set several parameters with pStat.set... then I add the batch with pStat.addBatch();
5. Every 10000 rows (I call addBatch() 10000 times), I call pStat.executeBatch();
6. Not sure if needed but I call also pStat.clearBatch() right after

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

I have an average of only 35 records (only 8 columns total, only a technical auto-incrementing primary key and some not null constraints) per second. I calculate that it would take me a week to insert all my 20M rows...

Am I doing anything wrong?

How many rows should I try to add at every batch cycle? Are 10000 too many?

Answer 1

If you happen to be using MySQL with a JDBC driver around version 5.1.7 you may be affected by a bug that slows down batch inserts. Updating to 5.1.10 or later should take care of it.