[Spring Batch] addBatch로 다량 쿼리문 한번에 실행 (bulk insert)

결과 

10만건 데이터 > 

JPA 27분 , addBatch 28초

problem

엔터티 indexes 를 설정하고 검색최적화를 위해

더미데이터를 DB에 저장 하려고 하는데 10만건의 데이터를 넣는데 굉장히 많은 시간이 걸렸다.

어떻게 하면 데이터를 더 빠르게 db에 저장 할 수 없을까 ?

JPA를 통해 데이터를 DB에 보내면

save() 또는 saveAll() 해주면 엔터티마다 트랜젝션을 생성하고 커밋한다.

즉 건당 오버헤드(overhead)가 발생한다.

solution

오버헤드가 일어나는곳을 한번에 묶어서 처리하면 되지 않을까 ? (중복제거)

addBatch로 한번에 묶어서 커밋을 해보자.

addBatch는 쿼리 실행을 하지 않고 쿼리 구문을 메모리에 올려두었다가, 실행 명령(executeBatch)이 있으면 한번에 DB쪽으로 쿼리를 날린다. Array Processing 기능을 활용하면 한 번의 SQL 수행으로 대량의 로우를 동시에 insert/update/delete 할 수 있다.

---

CODE

    Connection con = null;
    PreparedStatement pstmt = null ;

    public void runBatchJob() {
        String sql = "INSERT INTO post (title, content, category_id, member_id, view_count) VALUES (?, ?, ?, ?, ?)";

        try {
            con = dataSource.getConnection();
            con.setAutoCommit(false);
            pstmt = con.prepareStatement(sql);
            for (int i = 0; i < 100000; i++) {

                Post post = postService.createBatchPosts(i);

                pstmt.setString(1, post.getTitle());
                pstmt.setString(2, post.getContent());
                pstmt.setLong(3, 2L);
                pstmt.setLong(4, 2L);
                pstmt.setInt(5, post.getViewCount());

                // addBatch에 담기
                pstmt.addBatch();
                // 파라미터 Clear
                pstmt.clearParameters();

                if ((i % 10000) == 0) {
                    // Batch 실행
                    pstmt.executeBatch();
                    // Batch 초기화
                    pstmt.clearBatch();
                    // 커밋
                    con.commit();
                }
            }
            pstmt.executeBatch();
            con.commit();

        } catch (Exception e) {
            try {
                con.rollback();
            } catch (SQLException ignored) {;
            }
        } finally {
            if (pstmt != null) try {
                pstmt.close();
                pstmt = null;
            } catch (SQLException ignored) {
            }
            if (con != null) try {
                con.close();
                con = null;
            } catch (SQLException ignored) {
            }
        }

    }

1. db Connetion and Read

        String sql = "INSERT INTO post (title, content, category_id, member_id, view_count) VALUES (?, ?, ?, ?, ?)";

        try {
            con = dataSource.getConnection();
            con.setAutoCommit(false);
            pstmt = con.prepareStatement(sql);

db와 커넥션을 열어주고 AutoCommit을 false 시켜준다 (원자성 보장)

setAutoCommit()를 호출하여 자동 커밋을 비활성화하면,

여러 쿼리가 모두 성공하면 수동으로 커밋을 호출하여 하나의 트랜잭션으로 묶어, 하나의 쿼리라도 실패하면 롤백할 수 있다.

pstmt = con.prepareStatement(sql);

각 객체와 바인딩할 SQL 쿼리파라미터 값을 설정한다.

---

2. process

for (int i = 0; i < 100000; i++) {

                Post post = postService.createBatchPosts(i);

                pstmt.setString(1, post.getTitle());
                pstmt.setString(2, post.getContent());
                pstmt.setLong(3, 2L);
                pstmt.setLong(4, 2L);
                pstmt.setInt(5, post.getViewCount());

                // addBatch에 담기
                pstmt.addBatch();
                // 파라미터 Clear
                pstmt.clearParameters();

10만건의 데이터를 넣을 객체를 생성해주고

VALUES (?, ?, ?, ?, ?)

 

쿼리문 위치에 맞는 값들을 각각 바인딩 해준 후 

preparedStatement 객체에 addBatch() 한다. (담기)

pstmt.clearParameters() 사용하여 이전에 설정된 매개변수를 초기화하고, 다음 addBatch() 호출 시에 새로운 매개변수 값을 설정할 수 있도록 설정한다.

---

3. writer

if ((i % 10000) == 0) {
                    // Batch 실행
                    pstmt.executeBatch();
                    // Batch 초기화
                    pstmt.clearBatch();
                    // 커밋
                    con.commit();
                }
            }

한번에 10만건을 한번에 처리하면 OutOfMemory 걸릴 수 있다. 

1만건씩 나눠서 addBatch로 담아두었던 pstmt.executeBatch() 실행시켜준다. 

담은 Batch를 초기화 해준 후 모두 커밋시켜준다.

---

4. exception

catch(Exception e){
            try {
                con.rollback();                
            }catch(Exception e1) {
            }

코드에 실패가 일어날 경우 rollback 을 해준다.

---

5. finally close

/*con = dataSource.getConnection();
*con.setAutoCommit(false);
*pstmt = con.prepareStatement(sql);
  이대로 열었으니 */
finally {
            if (pstmt != null) try {
                pstmt.close();
                pstmt = null;
            } catch (SQLException ignored) {
            }
            if (con != null) try {
                con.close();
                con = null;
            } catch (SQLException ignored) {
            }
        }

모든 작업이 완료된 후에는 차례대로 닫아주어야 한다.

끗 !

---