Inserting 1million tuples in DB taking long time

Question

I have an scenario where i need to insert 1million entries using sql. Which is taking long time. I have Database structure with 9 tables, in every operation i need perform below tasks.

1. Open Connection
2. Insert tuples in 9 tables with maintaining Primary/foreign key relationship
3. Commit
4. close the connection

Repeat the operation for 1million time.

i am inserting 800 iterations/hour. Which i feel too slow, Do you have any better ways to improve on this?

You mean to say open connection only once and close only after all the insertions right? — Fairoz, Jun 29 '17 at 14:20

suguspnk · Answer 1 · 2017-07-03T10:33:20.240

Try inserting by Batch (i.e. PreparedStatement). Maybe you are inserting them individually.

e.g.

for(int i = 0; i < rows.length; i++){
   // set parameter 1 to rows[i][0]
   preparedStatement.setObject(1, rows[i][0]);
   // set parameter 2 to rows[i][1]
       .
       .
       .
   preparedStatement.addBatch();
   // insert 10k rows
   if(i % 10000 == 0)
       preparedStatement.executeBatch();
}
preparedStatement.executeBatch();

In this case where you have foreign keys, batch insert first the data to tables without FKs.

Why are batch inserts/updates faster? How do batch updates work?

score 0 · Answer 2 · answered Jul 03 '17 at 10:37

Try to run execute the query in bulk as suggested in above answers. make sure to take care of rollback strategy in case error is thrown for a particular data.

Different strategies if error is thrown by a batch are : 1. full rollback 2. Commit at logical point and rest rollback.

Inserting 1million tuples in DB taking long time

2 Answers2