Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes Object Relational Mapping and the fly likes Optimum batch size Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Optimum batch size" Watch "Optimum batch size" New topic
Author

Optimum batch size

Ritika Saxena
Ranch Hand

Joined: Jul 02, 2006
Posts: 51
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

Joined: Feb 05, 2001
Posts: 17250
    
    6

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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Ritika Saxena
Ranch Hand

Joined: Jul 02, 2006
Posts: 51
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

Joined: Feb 05, 2001
Posts: 17250
    
    6

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
 
Consider Paul's rocket mass heater.
 
subject: Optimum batch size