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