How to speed up Hibernate batching and avoid OutOfMemoryException
Description
I have a Spring application that uses the Hibernate ORM to communicate with a database. I have this function which populates database tables named orders, order_line and cc_xacts. Below are the relationships between the tables:
-------- --------------
|orders|--- 1:m --->| order_line |
-------- --------------
| ------------
-------- 1:m --->| cc_xacts |
------------
Thus, the order has a one-to-many relationship with the cc_xacts and order_line objects.
public void populateOrdersAndCC_XACTSTable()
{
GregorianCalendar cal;
String[] credit_cards = {"VISA", "MASTERCARD", "DISCOVER", "AMEX",
"DINERS" };
int num_card_types = 5;
String[] ship_types = {"AIR", "UPS", "FEDEX", "SHIP", "COURIER", "MAIL" };
int num_ship_types = 6;
String[] status_types = {"PROCESSING", "SHIPPED", "PENDING", "DENIED" };
int num_status_types = 4;
// Order variables
int O_C_ID;
java.sql.Timestamp O_DATE;
double O_SUB_TOTAL;
double O_TAX;
double O_TOTAL;
String O_SHIP_TYPE;
java.sql.Timestamp O_SHIP_DATE;
int O_BILL_ADDR_ID, O_SHIP_ADDR_ID;
String O_STATUS;
String CX_TYPE;
int CX_NUM;
String CX_NAME;
java.sql.Date CX_EXPIRY;
String CX_AUTH_ID;
int CX_CO_ID;
System.out.println( "Populating ORDERS, ORDER_LINES, CC_XACTS with "
+ NUM_ORDERS + " orders" );
System.out.print( "Complete (in 10,000's): " );
for ( int i = 1; i <= NUM_ORDERS; i++ )
{
if ( i % 10000 == 0 )
System.out.print( i / 10000 + " " );
int num_items = getRandomInt( 1, 5 );
O_C_ID = getRandomInt( 1, NUM_CUSTOMERS );
cal = new GregorianCalendar();
cal.add( Calendar.DAY_OF_YEAR, -1 * getRandomInt( 1, 60 ) );
O_DATE = new java.sql.Timestamp( cal.getTime().getTime() );
O_SUB_TOTAL = (double) getRandomInt( 1000, 999999 ) / 100;
O_TAX = O_SUB_TOTAL * 0.0825;
O_TOTAL = O_SUB_TOTAL + O_TAX + 3.00 + num_items;
O_SHIP_TYPE = ship_types[getRandomInt( 0, num_ship_types - 1 )];
cal.add( Calendar.DAY_OF_YEAR, getRandomInt( 0, 7 ) );
O_SHIP_DATE = new java.sql.Timestamp( cal.getTime().getTime() );
O_BILL_ADDR_ID = getRandomInt( 1, 2 * NUM_CUSTOMERS );
O_SHIP_ADDR_ID = getRandomInt( 1, 2 * NUM_CUSTOMERS );
O_STATUS = status_types[getRandomInt( 0, num_status_types - 1 )];
Orders order = new Orders();
// Set parameter
order.setCustomer( customerDao.findById( O_C_ID ) );
order.setODate( new Date( O_DATE.getTime() ) );
order.setOSubTotal( O_SUB_TOTAL );
order.setOTax( O_TAX );
order.setOTotal( O_TOTAL );
order.setOShipType( O_SHIP_TYPE );
order.setOShipDate( O_SHIP_DATE );
order.setAddressByOBillAddrId( addressDao.findById( O_BILL_ADDR_ID ) );
order.setAddressByOShipAddrId( addressDao.findById( O_SHIP_ADDR_ID ) );
order.setOStatus( O_STATUS );
order.setCcXactses( new HashSet<ICcXacts>() );
order.setOrderLines( new HashSet<IOrderLine>() );
ordersDao.shrani( order );
for ( int j = 1; j <= num_items; j++ )
{
int OL_ID = j;
int OL_O_ID = i;
int OL_I_ID = getRandomInt( 1, NUM_ITEMS );
int OL_QTY = getRandomInt( 1, 300 );
double OL_DISCOUNT = (double) getRandomInt( 0, 30 ) / 100;
String OL_COMMENTS = getRandomAString( 20, 100 );
OrderLine orderLine = new OrderLine();
orderLine.setItem( itemDao.findById( OL_I_ID ) );
orderLine.setOlQty( OL_QTY );
orderLine.setOlDiscount( OL_DISCOUNT );
orderLine.setOlComment( OL_COMMENTS );
orderLine.setOrders( order );
orderLineDao.shrani( orderLine );
order.getOrderLines().add( orderLine );
}
CX_TYPE = credit_cards[getRandomInt( 0, num_card_types - 1 )];
CX_NUM = getRandomNString( 16 );
CX_NAME = getRandomAString( 14, 30 );
cal = new GregorianCalendar();
cal.add( Calendar.DAY_OF_YEAR, getRandomInt( 10, 730 ) );
CX_EXPIRY = new java.sql.Date( cal.getTime().getTime() );
CX_AUTH_ID = getRandomAString( 15 );
CX_CO_ID = getRandomInt( 1, 92 );
CcXacts ccXacts = new CcXacts();
ccXacts.setCountry( countryDao.findById( CX_CO_ID ) );
ccXacts.setOrders( order );
ccXacts.setCxType( CX_TYPE );
ccXacts.setCxNum( CX_NUM );
ccXacts.setCxName( CX_NAME );
ccXacts.setCxExpiry( CX_EXPIRY );
ccXacts.setCxAuthId( CX_AUTH_ID );
ccXacts.setCxXactAmt( O_TOTAL );
ccXacts.setCxXactDate( O_SHIP_DATE );
ccXacts.setCountry( countryDao.findById( CX_CO_ID ) );
order.getCcXactses().add( ccXacts );
ccXactsDao.shrani( ccXacts );
}
System.out.println( "" );
}
Problem
The problem arises when I want to fill about 250,000 orders in the essence of orders, because doing it as if I am doing it now is very slow. It takes many hours to populate the database with so many orders. Sometimes I also run out of Java Heap size and throw OutOfMemoryException.
Do you have any suggestions how I can speed this up and may not end up with Java heap size?
source to share
-
You need to enable Hibernate batch support , so you need to set the following Hibernate properties:
properties.put("hibernate.jdbc.batch_size", "50"); properties.put("hibernate.order_inserts", "true"); properties.put("hibernate.order_updates", "true"); properties.put("hibernate.jdbc.batch_versioned_data", "true");
-
To avoid the OutOfMemoryError, you need to clear the current session whenever the package is ready to be painted:
doInTransaction(session -> { int batchSize = batchSize(); for(int i = 0; i < itemsCount(); i++) { //batch insert logic if(i % batchSize == 0 && i > 0) { session.flush(); session.clear(); } } });
-
Try to keep the entity map so you don't have to fetch all of these objects at every step:
customerDao.findById( O_C_ID ); countryDao.findById( CX_CO_ID );
You can either get them at the beginning of the batch process , or at least cache the result in a local map. Each select starts the current cleanup session (if you are using automatic flush), which reduces batch performance.
source to share