How to optimize the processing speed for inserting data using java?

Question

I have a requirement to read an Excel file with its headers and data and create a table in a Database (MySQL) on the basis of header and put value which is extracted from file. For that I am using JDBC for creating and inserting data (used prepared statement) in DB Table.

It works nicely but when the number of records are increased -suppose file contains 200000 or more records- it's going to be slow. Please guide me how I am able to optimize the processing speed of inserting data into an DB Table.

Thanks, Sameek

.. I thought, a single excel table can't hold more then 65535 records (rows) — Andreas Dolk
– Andreas Dolk, Commented Jun 27, 2011 at 7:36
@Andreas_D: Indeed. I guess he's inserting multiple rows for each Excel row or something... (or maybe he's using multiple sheets) — helios
– helios, Commented Jun 27, 2011 at 7:45
Additionaly to reusing prepared-statements and batches (that two are the most important ones) I think you could set the constraints off for inserting and then activate them when all records are inplace. That should check the constraints in faster way than doing it for every single record you add. But I don't know how to do it in MySQL or if it can be done inside a transaction or you must commit before setting checks on/off. — helios
– helios, Commented Jun 27, 2011 at 7:49

Asaf · Accepted Answer · 2011-06-27 07:34:56Z

7

To optimize it you should first use the same PreparedStatement object in all of the inserts.

To further optimize the code you can send batches of updates.

e.g. batches of 5:

//create table
PreparedStatement ps = conn.prepareStatement(sql);
for(int i =0; i < rows.length; ++i) {
  if(i != 0 && i%5 == 0) {
    pstmt.executeBatch();
  }
  pstmt.setString(1, rows[i].getName());
  pstmt.setLong(2, rows[i].getId());
  pstmt.addBatch();
}
pstmt.executeBatch();

answered Jun 27, 2011 at 7:34

Asaf

8006 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andreas Dolk Over a year ago

+1 - I learned that people sometimes forget, that we prepare a statement to (re-)use it again and again (sure, might not be the case here)

Piotr · Accepted Answer · 2011-06-27 07:37:09Z

1

Wrap your inserts in a transaction. Pseudo code:

1) Begin transaction 2) Create prepared statement 3) Loop for all inserts, setting prepared statement parameters and executing for each insert 4) Commit transaction

answered Jun 27, 2011 at 7:37

Piotr

5,0038 gold badges37 silver badges48 bronze badges

Comments

Houcem Berrayana · Accepted Answer · 2011-06-27 07:52:42Z

0

I'll take the example of hibernate. Hibernate have a concept called HibernateSession which stores the SQL command that are not yet sent to DB. With Hibernate you can do inserts and flush the session every 100 inserts which means sending SQL queries every 100 inserts. This helps to gain performance because it communicates with database every 100 inserts and not each insert.

So you can make the same thing by executing the executeUpdate every 100 (or what ever you want) times or use preparedStatement.

answered Jun 27, 2011 at 7:52

Houcem Berrayana

3,10025 silver badges41 bronze badges

Collectives™ on Stack Overflow

How to optimize the processing speed for inserting data using java?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related