Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Optimum batch size

 
Ritika Saxena
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

We need to insert a large number of records in the database. In order to enhance performance, we are performing bacth insert as:
----------------------------
1. Setting the batch size to reasonable number:
hibernate.jdbc.batch_size 20

2. The code is modified as:
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
for ( int i=0; i<100000; i++ ) {
Customer customer = new Customer(.....);
session.save(customer);
if ( i % 20 == 0 ) { //20, same as the JDBC batch size
//flush a batch of inserts and release memory:
session.flush();
session.clear();
}
}
tx.commit();
session.close();
---------------------------

But can any one please tell how should we determine the optimum batch size (as mentioned in Step 1.) We have to insert approx 50 lakhs records in the table.

Thanks in advance!!
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well for the code it is should be equal to what you have set for your JDBC driver. The only way to know the optimum on your system is to test and try things out and time them.

Mark
 
Ritika Saxena
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mark, thanks for your reply.

I have read some where that the batch size could be anything between 10 and 50. But I wonder, what would be the impact if we make the batch size as 150 or 200. Do you have any idea about this?

Thanks once again!!!
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The only way to find out is to try it out, time it and see if it makes sense. There is a thing about how much data you send accross the wire versus packet size on the connection, but no one can give you a this is the answer, you have to find out yourself through metrics that you gather on your system.

Mark
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic