JDBC Batch Bulk Data Insertion

At work I did dwell on finding a faster way to insert bulk data into an Oracle datasource. My project contained huge amount of data retrieval, processing and bulk insertion into a target database. I’ve implemented a standardized way of data insertion, e.g. establishing a connection, proceeding data insertion as well as terminating the concurrent connection. This issue had yielded a big performance and delay issue. The processes took longer half days or a full day completion time. So I’ve conducted a research on how to proceed to insert bulk data into database. So here what I’ve prepared for this tutorial.

MainApp.java

package com.tugrulaslan.batchinsert;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by Tugrul on 9/4/2015.
 */
public class MainApp {
    private static List<Long> numberList = new ArrayList<Long>();

    static {
        for (int i = 0; i < 1000; i++) {
            numberList.add(System.currentTimeMillis());
        }
    }

    public static void main(String[] args) {

    /*
    * Single insertion
    **/
        long singleInsertStarted = System.currentTimeMillis();
        DatabaseDAO dao = new DatabaseDAO();
        for (Long obj : numberList) {
            dao.singleAdd(obj);
        }
        long singleInsertFinished = System.currentTimeMillis();
        long singleInsertDuration = singleInsertFinished - singleInsertStarted;

        System.out.println("Single insertion duration: " + singleInsertDuration + " ms.");

        /*
         * Bulk insertion
         **/
        long bulkInsertStarted = System.currentTimeMillis();

        dao.bulkAdd(numberList);
        long bulkInsertFinished = System.currentTimeMillis();
        long bulkInsertDuration = bulkInsertFinished - bulkInsertStarted;

        System.out.println("Bulk insertion duration: " + bulkInsertDuration + " ms.");


    }
}

DatabaseDAO.java

package com.tugrulaslan.batchinsert;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by Tugrul on 9/4/2015.
 */
public class DatabaseDAO {

    private static Connection connection;
    private static PreparedStatement preparedStatement;

    private static final String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
    private static final String dBConnection = "jdbc:oracle:thin:@//172.21.130.62:1529/TAKB112";
    private static final String dBUser = "REPORTING";
    private static final String dBPass = "REPORTING_01";

    private static final int batchSize = 100;
    private static int count = 0;

    public void singleAdd(Long obj) {
        try {
            Class.forName(jdbcDriver);
            connection = DriverManager.getConnection(dBConnection,
                    dBUser, dBPass);

            connection.setAutoCommit(false);

            String q = "INSERT INTO REPORTING.JDBCINSERTTEST (VALUE) VALUES (?)";
            preparedStatement = connection.prepareStatement(q);

            preparedStatement.setLong(1, obj);

            preparedStatement.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            System.err.println(e);
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException e1) {
                    System.err.println(e);
                }
            }

        } catch (ClassNotFoundException e) {
            System.err.println(e);
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                System.err.println(e);
            }
        }
    }

    public void bulkAdd(List<Long> numberList) {
        try {
            Class.forName(jdbcDriver);
            connection = DriverManager.getConnection(dBConnection,
                    dBUser, dBPass);

            connection.setAutoCommit(false);

            String q = "INSERT INTO REPORTING.JDBCINSERTTEST (VALUE) VALUES (?)";
            preparedStatement = connection.prepareStatement(q);
            for (Long obj : numberList) {
                preparedStatement.setLong(1, obj);
                // This will execute batches by a given batch size number  volume
                if (++count % batchSize == 0)
                    preparedStatement.executeBatch();
            }
            // inserts remaining records
            preparedStatement.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            System.err.println(e);
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException e1) {
                    System.err.println(e);
                }
            }

        } catch (ClassNotFoundException e) {
            System.err.println(e);
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                System.err.println(e);
            }
        }
    }
}

Console Output

Single insertion duration: 218330 ms.
Bulk insertion duration: 1664 ms.

Process finished with exit code 0

as you notice here the outcome leads to a different of a big time! I’ve managed to insert 1K of long literal values into Oracle datasource, bulk insertion elapsed 218 seconds and 33 miliseconds which is approximately 3 minutes and 63 seconds, last of all the bulk insertion with the same amount of data and system did elapse 1 seconds and 664 miliseconds there is almost 75% performance difference!

 

Resources:

http://docs.oracle.com/cd/E11882_01/java.112/e16548/oraperf.htm#JJDBC28757

http://www.onjava.com/pub/a/onjava/excerpt/oraclejdbc_19/index.html?page=2

http://blog.lishman.com/2008/06/jdbc-batch-updates.html

Leave a Reply

Your email address will not be published. Required fields are marked *