Insert data in batch mode - Real's Java How-to

Insert data in batch modeTag(s): JDBC

The regular approach

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PerfInsert {
    public static void main(String[] args) throws ClassNotFoundException,
            SQLException, IOException {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = "jdbc:oracle:thin:@//oracle.server:1529/prod";
        Connection conn = DriverManager.getConnection(url, "scott", "tiger");
        conn.setAutoCommit(false);
        ExecutionTimer t = new ExecutionTimer();  // see this HowTo
        PreparedStatement pstmt = conn.prepareStatement(
           "INSERT INTO PROD.COUNTER VALUES (?,?,?)");
        int i =0;
        t.start();
        while (i < 100) {
            pstmt.setString(1, "test");
            pstmt.setString(2, String.valueOf(i));
            pstmt.setFloat(3, 0);
            pstmt.executeUpdate();
            i++;
           }
        conn.commit();   
        t.end();
        System.out.println("Timer : " + t.duration());
        pstmt.close();
        System.out.println("Ok.");
    }
}

When inserting many INSERTs (or DELETE/UPDATE), JDBC provides the addBatch() method to cumulate the SQL statements. When ready, the whole batch is sent in one shot to minimize the network traffic. A special attention to the error handling should made.

(Sun) Sending Batch Updates

The performance gain is good. For example, 100 individual INSERTS == 140ms 10 batched INSERTS == 32ms

Using PreparedStatement in batch mode

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PerfInsert {
    public static void main(String[] args) throws ClassNotFoundException,
            SQLException, IOException {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = "jdbc:oracle:thin:@//oracle.server:1529/prod";
        Connection conn = DriverManager.getConnection(url, "scott", "tiger");
        conn.setAutoCommit(false);
        ExecutionTimer t = new ExecutionTimer();  // see this HowTo
        PreparedStatement pstmt = conn.prepareStatement(
           "INSERT INTO PROD.COUNTER VALUES (?,?,?)");
        int i =0;
        t.start();
        while (i < 100) {
           pstmt.setString(1, "test");
           pstmt.setString(2, String.valueOf(i));
           pstmt.setFloat(3, 0);
           pstmt.addBatch();
         i++;
        }
        int[] upCounts = stmt.executeBatch();
        conn.commit();   
        t.end();
        System.out.println("Timer : " + t.duration());
        pstmt.close();
        System.out.println("Ok.");
    }
}

Using Statement in batch mode

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;

public class PerfInsert {
    public static void main(String[] args) throws ClassNotFoundException,
            SQLException, IOException {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = "jdbc:oracle:thin:@//oracle.server:1529/prod";
        Connection conn = DriverManager.getConnection(url, "scott", "tiger");
        conn.setAutoCommit(false);
        ExecutionTimer t = new ExecutionTimer(); // see this HowTo
        int i =0;
        t.start();
        while (i < 100) {
          stmt.addBatch(
            "INSERT INTO PROD.COUNTER VALUES "
                + "(\"test\", " + i + ", 0)");
          stmt.addBatch();
          i++;
        }
        int[] upCounts = stmt.executeBatch();
        conn.commit();
        t.end();
        System.out.println("Timer : " + t.duration());
        stmt.close();
        System.out.println("Ok.");
    }
}