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