CODEWITHSUNDEEP
javajdbcbulkinsert
Sivaraj Thavamani
Sivaraj Thavamani

Reputation: 139

Batch insert in JDBC taking long time

I tried to batch insert 10K record by jdbc connection in MSSQL, it took around 18 to 20 secs to bulk insert. I want to insert in less than 3 seconds. Is there any way to insert quickly.

My code given below

public void BatchInsert() {
    PreparedStatement pStmnt = null;
  try {
     final int batchSize = 1000;
     int count = 0;
     Connection con = createConnection();
     long end = System.currentTimeMillis();
     System.out.println("connection creation time :: " + (end - start));
     start = System.currentTimeMillis();
     pStmnt = con.prepareStatement("insert into testtable values(?)");
     for (int i = 1; i <= 10000; i++) {
        pStmnt.setInt(1, i);
        pStmnt.addBatch();

       if (++count % batchSize == 0) {
          pStmnt.executeBatch();
       }
     }
     pStmnt.executeBatch(); // insert remaining records
     end = System.currentTimeMillis();
     System.out.println("total insert time :: " + (end - start));
 } catch (SQLException e) {
     e.printStackTrace();
 } finally {
     try {
       pStmnt.close();
     } catch (SQLException e) {
       e.printStackTrace();
     }
 }
    }

Thanks in advance for yr help.

Upvotes: 1

Views: 4898

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123829

If your connection is defaulting to AutoCommit mode then you may get some significant improvement in performance by doing

con.setAutoCommit(false);

before entering the loop to addBatch, and then doing

con.commit();

after the final executeBatch.

Upvotes: 0

SkyWalker
SkyWalker

Reputation: 29168

As far I think that, you have used more indexing in your database table. That's why it is taking more time. For this reason, in inserting case, at first you need to stop indexing somehow(maybe deleting and recreating or removing unnecessary indexing).

For operational purpose, you can remove all indexing from your testtable and run your batch. Hopefully you will get minimal insertion time.

Resource Link:

  1. More indexes, slower INSERT - Use The Index, Luke!
  2. Fundamentals: Improving Insert and Update Performance by Dropping Unused Indexes.

UPDATE:

  1. Before you do the INSERT, Disable the Trigger and drop the Foreign Keys.
  2. Do the INSERT setting the fields normally set by the Trigger, and insuring that the FK Column's values are valid.
  3. Re-Enable the trigger, and re-create the Foreign Keys WITH NOCHECK.

Credit goes to @RBarryYoung

Resource Link:

  1. SQL Server INSERT into huge table is slow

Upvotes: 0

Related Questions