Increase Speed to Insert a Large Number of Data into Database

This topic I'm going to talk about how to increase speed when insert a large number of data into database. I would like to talk about 2 methods, they are Batch insert and Java object.

First of all we need to create a table to store data.

Code:

CREATE OR REPLACE TABLE USER_LIST (
  USER_ID VARCHAR2(5),
  NAME VARCHAR2(30),
  SURNAME VARCHAR2(30)
);

First method I would like to use the Batch insert process which is pretty easy and faster.

Code:

String sql;
String user_id;
try {
  sql = "insert into user_list values(?,?,?)";
  PreparedStatement pstmt = conn.prepareStatement(sql); // need to create a connection prior
  conn.setAutoCommit(false);
  for (int i=0; i<TOTAL_RECORD; i++) {
    user_id = createUserID(i);
    pstmt.setString(1, user_id);
    pstmt.setString(2, "Name_"+user_id);
    pstmt.setString(3, "Surname_"+user_id);
    pstmt.addBatch();
  }
  int[] result = pstmt.executeBatch();
  for (int i=0; i<result.length; i++) {
    if (result[i] >= 0) {
      // Successfully executed; the number represents number of affected rows
    } else if (result[i] == Statement.SUCCESS_NO_INFO) {
      // Successfully executed; number of affected rows not available
    } else if (result[i] == Statement.EXECUTE_FAILED) {
      // Failed to execute
      System.out.println("Failed to execute statement, "+i);
      // You may need to re-process this record
    }
  }
  conn.commit();
  pstmt.close();
} catch (SQLException e) {
  System.out.println("Error "+e.getMessage());
}

Second method using the Java object. There is a bit more complicate and a result is slower than the first method. 

1. Create the object type.

Code:

CREATE OR REPLACE TYPE USER_OBJ AS OBJECT (
  USER_ID_OBJ VARCHAR2(5),
  NAME_OBJ VARCHAR2(30),
  SURNAME_OBJ VARCHAR2(30)
);

2. Create the table of the object.

Code:

CREATE OR REPLACE TYPE USER_OBJ_ARRAY AS TABLE OF USER_OBJ;

3. Create a store package.

Code:

CREATE OR REPLACE PACKAGE "USER" AS PROCEDURE INSERT_USER_LIST (
  USER_ID USER_LIST.USER_ID%TYPE,
  NAME USER_LIST.NAME%TYPE,
  SURNAME USER_LIST.SURNAME%TYPE);
END;

Code:

CREATE OR REPLACE PACKAGE BODY "USER" AS PROCEDURE INSERT_USER_LIST (
  USER_ID USER_LIST.USER_ID%TYPE,
  NAME USER_LIST.NAME%TYPE,
  SURNAME USER_LIST.SURNAME%TYPE) IS
BEGIN
  INSERT INTO USER_LIST ("USER_ID", "NAME", "SURNAME")
  VALUES (USER_ID, NAME, SURNAME);
END INSERT_USER_LIST;
END;

4. Create a stored procedure.

Code:

CREATE OR REPLACE PROCEDURE INSERT_USER_PROC ( users IN USER_OBJ_ARRAY ) AS
BEGIN FOR i IN 1 .. users.COUNT
LOOP
  USER.INSERT_USER_LIST (
    users(i).USER_ID_OBJ,
    users(i).NAME_OBJ,
    users(i).SURNAME_OBJ);
END LOOP;
END;

5. Using it in the Java class.

Code:

int TOTAL_RECORD = 100;
String user_id;
Object[][] user_obj = new Object[TOTAL_RECORD][3];
for (int i=0; i<TOTAL_RECORD; i++) {
  user_id = createUserID(i);
  user_obj[i][0] = user_id;
  user_obj[i][1] = "Name_"+user_id;
  user_obj[i][2] = "Surname_"+user_id;
}
try {
  String sql = "{call INSERT_USER_PROC(?)}";
  conn.setAutoCommit(false);
  CallableStatement cs = conn.prepareCall(sql);
  ArrayDescriptor des = ArrayDescriptor.createDescriptor("USER_OBJ_ARRAY", conn);
  ARRAY a = new ARRAY(des, conn, user_obj);
  cs.setObject(1, (Object) a);
  cs.execute();
  if (cs != null) {
    cs.close();
  }
  conn.commit();
} catch (SQLException e) {
  System.out.println("Error "+e.getMessage());
}

Result from experiment by inserting data amount of 1000 records with the same environment.

using Batch execute process took 187 ms.
using Java object process took 1953 ms.

Let try anather thing, by cancel the step 3 and change the step 4 like this.

Code:

CREATE OR REPLACE PROCEDURE INSERT_USER_PROC ( users IN USER_OBJ_ARRAY ) IS
BEGIN FORALL i IN 1 .. users.COUNT 
  INSERT INTO USER_LIST VALUES(
    TREAT(users(i) AS USER_OBJ).USER_ID_OBJ, 
    TREAT(users(i) AS USER_OBJ).NAME_OBJ, 
    TREAT(users(i) AS USER_OBJ).SURNAME_OBJ);
END;

Result from the same number of data (1000 records).

using Java object process took 765 ms