SQL Batch

대용량 SQL을  한꺼번에 수행할때

for(..){
 pstmt.setXX
 pstmt.executeUpdateXX
}
tx.commit

이 코드는 느리다.
for(..){
 pstmt.setXX
 pstmt.addBatch
}
pstmt.executeBatchXX
tx.commit

이렇게 하는게 성능에 5~10배까지 차이가 난다.

===

아래는 직접 테스트 한 코드 10배 정도 차이가 나는것을 볼 수 있다.

<%

        Context ctx = new InitialContext();
        javax.sql.DataSource ds = (javax.sql.DataSource)
                ctx.lookup("bchoDS");
        Connection conn = ds.getConnection();
        conn.setAutoCommit(false);
        Statement stmt = conn.createStatement();
        stmt.executeUpdate("delete from bcho");
        conn.commit();
        stmt.close();

        long start = System.currentTimeMillis();
        out.println("start :"+start);

        String sql = "insert into bcho values( ?,'seoul')";
        PreparedStatement pstmt = conn.prepareStatement(sql);

        for(int j=0;j<10;j++){
          for(int i=0;i<10000;i++){
                pstmt.setString(1,"DUMMY");
                pstmt.executeUpdate();
          }
          Statement cstmt = conn.createStatement();
          cstmt.executeUpdate("commit work write batch nowait");
          cstmt.close();

        }// for j
        pstmt.close();
        conn.close();

        long end = System.currentTimeMillis();
        out.println("<BR>end :"+end);
        out.println("<BR>elapsed :"+(end-start));

%>

==== 아래는 BATCH

<%

        Context ctx = new InitialContext();
        javax.sql.DataSource ds = (javax.sql.DataSource)
                ctx.lookup("bchoDS");
        Connection conn = ds.getConnection();
        conn.setAutoCommit(false);
        Statement stmt = conn.createStatement();
        stmt.executeUpdate("delete from bcho");
        conn.commit();
        stmt.close();

        long start = System.currentTimeMillis();
        out.println("start :"+start);

        String sql = "insert into bcho values( ?,'seoul')";
        PreparedStatement pstmt = conn.prepareStatement(sql);

        for(int j=0;j<10;j++){
          for(int i=0;i<10000;i++){
                pstmt.setString(1,"DUMMY");
                pstmt.addBatch();
          }
          pstmt.executeBatch();
          conn.commit();

        }// for j
        pstmt.close();
        conn.close();

        long end = System.currentTimeMillis();
        out.println("<BR>end :"+end);
        out.println("<BR>elapsed :"+(end-start));

%>

BATCH elapsed :270  msec
COMMIT elapsed :24512 msec

===

애플리케이션에서 다른 업무와 함께 테스트 했을때는 2배 정도 차이가 났음.