Here's the deal:
- I create a connection
conn
using theDriverManager
- I set
conn.autoCommit(false);
- Then I have
PreparedStatement pStat = conn.prepareStatement(insert_string_with_parameter);
- I set several parameters with pStat.set... then I add the batch with
pStat.addBatch();
- Every 10000 rows (I call
addBatch()
10000 times), I callpStat.executeBatch();
- 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?