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?

Answer 1

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");
   

2. 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();
           }
       }
   });
   

3. 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.