How to speed up Hibernate batch processing and avoid OutOfMemoryException

Question

Description

I have a Spring application which is using Hibernate ORM to communicate with database. I have this function which populates the database tables named orders, order_line and cc_xacts. The relations between tables are shown below:

--------            --------------
|orders|--- 1:m --->| order_line |
--------            --------------
   |                ------------
   -------- 1:m --->| cc_xacts |
                    ------------

So orders have one-to-many relationship with cc_xacts and order_line entity.

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 occurs when I want to populate around 250000 orders in orders entity, because doing it like I'm doing it now it is very slow. It takes a lot of hours to populate database with so many orders. Sometimes I also run out of Java Heap size and get OutOfMemoryException.

Do you have any suggestions how can I speed up this and possibly not run out of Java Heap size?

Vlad Mihalcea · Accepted Answer · 2018-01-06 09:28:31Z

1

You need to activate 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 OutOfMemoryError you need to clear the current Session whenever the batch is ready to be flushed:

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 maintain a Map of entities so that you don't have to fetch all these entities in every batch step:
```
customerDao.findById( O_C_ID );
countryDao.findById( CX_CO_ID );
```
You can either fetch them at the beginning of the batch process or at least cache the result in a local Map. Each select triggers the current Session to flush (if you use AUTO flushing), therefore decreasing batch performance.

edited Jan 6, 2018 at 9:28

answered Apr 25, 2015 at 21:26

Vlad Mihalcea

156k85 gold badges599 silver badges984 bronze badges

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

1 Comment

Ivansek Over a year ago

I followed your instructions and now it is a lot faster. Thanks!

StanislavL · Accepted Answer · 2015-04-25 08:33:57Z

0

Use batches e.g. here. flush/commit partial results e.g. commit every 1000 inserts to reduce rollback data stored.

answered Apr 25, 2015 at 8:33

StanislavL

57.5k9 gold badges75 silver badges100 bronze badges

Comments

xrcwrn · Accepted Answer · 2015-04-26 05:05:54Z

0

Some times if you are logging hibernate information and also printing it to consol may cause slow fetch from hibernate. You can turn off printing log to console

answered Apr 26, 2015 at 5:05

xrcwrn

5,33517 gold badges72 silver badges130 bronze badges

Collectives™ on Stack Overflow

How to speed up Hibernate batch processing and avoid OutOfMemoryException

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