1. 들어가기전에
실무에서 간단하지만 대용량의 데이터를 처리해야하는 batch 프로그램을 하나 만들어야 했다.
매일 정해진 일정 시간에 1회 동작하는 방식이었다.
요구사항을 살짝 바꿔서 예제를 만들어보자.
2. 요구사항
A 데이터베이스의 Member 테이블로부터 name, mail_address, position 항목만 취득한다.
B 데이터베이스의 Team 테이블의 데이터를 전체 삭제한다.
B 데이터베이스의 Team 테이블에 취득한 데이터를 모두 등록한다.
✔ A 데이터베이스는 Sybase 이며, B 데이터베이스는 Oracle DB 이다.
3. 코드 작성 중 문제 확인
처음엔 데이터가 얼마 되지 않을 것이라 생각해서 한줄씩 INSERT문을 날렸다.
등록해야하는 B 데이터베이스가 Oracle DB 였기 때문에 다음과 같은 SQL문이 지원이 되지 않아서였기도 했고, 자칫 나쁜 버릇이 될 수도 있지만 솔직히 말하면 기존에 작성되어있던 SQL을 참고(라고 말하고 copy & paste) 해서 빠르게 작성했는데, 기존의 코드가 INSERT 문을 1건씩 날리고 있었다. 새로 작성하는것도 귀찮았다.
-- 다음의 다중 INSERT문은 MySQL, PostgreSQL 등에서 가능
INSERT INRO 테이블명 (column1, column2, column3, ...)
VALUES
('value1', 'value2', 'value3', ...),
('value1', 'value2', 'value3', ...),
...
('value1', 'value2', 'value3', ...);
-- Oracle DB 의 다중 INSERT
-- 1. INSERT ALL 방식
INSERT ALL
INTO 테이블명 (column1, column2, ...) VALUES ('value1', 'value2', ...)
INTO 테이블명 (column1, column2, ...) VALUES ('value1', 'value2', ...)
...
SELECT * FROM DUAL;
-- 2. UNION ALL 방식 : 이 방식이 더 빠르다
INSERT INTO 테이블명 ( column1, column2, column3, ...)
SELECT ('value1','value2','value3', ...) FROM DUAL UNION ALL
SELECT ('value1','value2','value3', ...) FROM DUAL UNION ALL
...
SELECT ('value1','value2','value3', ...) FROM DUAL;→ Oracle DB 의 다중 INSERT 쿼리를 작성하는 방법은 MySQL, PostgreSQL 보다 복잡한 편이다.
(1) 문제 확인 (수정전)
(잘못된 예) 1건씩 INSERT 문을 실행
// 대량의 데이터를 등록하는 시스템에서 1건씩 INSERT 문을 날리는 잘못된 예
StringBuffer sql = new StringBuffer();
sql.append("INSERT INTO TARGET_B (");
sql.append("id, name, code");
sql.append(") VALUES (");
sql.append("?, ?, ?");
sql.append(" )";
PreparedStatement pstmt = conn.prepareStatement(sql.toString());
int resultCount = 0;
for (FromTable from : fromList) {
int cnt = 0;
pstmt.setObject(1, from.getId());
pstmt.setObject(2, from.getName());
pstmt.setObject(3, from.getCode());
// SQL 실행 : List 데이터를 for문으로 돌려 1건씩 INSERT 문을 날리고 있었다.
try {
resultCount += pstmt.executeUpdate();
} catch (SQLException e) {
if (pstmt != null) {
pstmt.close();
}
throw e;
}
}
if (pstmt != null) {
pstmt.close();
}
System.out.printLn("resultCount = " + resultCount);
데이터 1건씩 INSERT 하는 코드를 작성한 후, 테스트 데이터로 주어진 약10,000건 정도로 성능테스트를 해보니 15초 이상 걸리고 말았다. 너무 느리다. 1건씩 INSERT 문을 날리니 당연히 느릴수 밖에 없다. 10,000건에 15초는 말도안되는 속도다.
그리고 테스트 데이터가 약10,000건이지 실제 데이터는 더 많다.
이럴때 더 빠르게 대량의 데이터를 등록할 수 있는 방법은 없을까?
(2) 문제 해결 방법
PrepareStatement 의 addBatch() 메소드를 이용하는 방법이 있다.
addBatch()는 바로 쿼리 실행을 하지 않고 작성된 쿼리를 메모리에 올려두었다가 excuteBatch()로 실행하면 메모리에 올려두었던 쿼리를 한번에 날려 보낸다.
1건씩 쿼리를 날려보냈던 방식과 비교하면 당연히 속도가 빨라진다. 쿼리를 실행할때 DB와의 통신이 필요한데, 1건마다 통신하는것과 여러건을 한번에 통신하는것과 비교하면 통신과정이 단축되니 당연히 빨라진다.
먼저 사용법을 확인해보자
(3) 사용법
// 예) SQL
String sql = "INSERT INTO MY_ITEM (id, name, price) VALUES (?, ?, ?)";
// sql 을 전달해 PreparedStatement 를 생성한다.
pstmt = conn.prepareStatement(sql);
// 파라미터를 설정해준다.
pstmt.setString(1, item.getId());
pstmt.setString(2, item.getName());
pstmt.setInt(3, item.getPrice());
// addBatch() 로 메모리에 넣어준다.
pstmt.addBatch();
// batch 메모리에 넣은 후 파라미터 클리어
pstmt.clearParameters();
// 다음 데이터 파라미터 설정 후 addBatch()...반복
// Batch 실행
pstmt.executeBatch();
// Batch 초기화
pstmt.clearBatch();
// Batch 정상 실행되면 커밋
conn.commit()
이제 사용법을 토대로 요구사항 예제를 코드로 확인해보자
4. 문제 해결된 수정후 java 코드
public void execute() {
// Member 데이터 취득
List<Member> memberList = getMemberList();
try {
// 트랜젝션 시작 (테이블 초기화 및 등록은 같은 트랜젝션으로 관리)
Connection conn = startTrasaction(); // connection 생성 코드 생략
conn.setAutoCommit(false); // 하나의 트랜젝션으로 관리하기위해 오토 커밋은 false 설정
// Connection 으로 TeamRepository 생성
TeamRepository teamRepository = new TeamRepository(conn)
// 테이블 초기화
teamRepository.deleteTeam();
// 팀 등록
teamRepository.insertTeam(memberList);
// 트랜젝션 종료
commitTransaction(); //..생략..
} catch (Exception e) {
// 롤백
rollbackTransaction(); //..생략..
} finally {
// 리소스 정리
close(); //..생략..
}
}
// TeamRepository
public class TeamRepository() {
private final Connection conn;
public TeamRepository(Connection conn) {
this.conn = conn;
}
/* 테이블 초기화 */
public void deleteTeam() {
String sql = "DELETE FROM TEAM";// TRUNCATE TABLE 을 고려해야 함
PreparedStatement pstmt = null;
try {
pstmt = conn.prepareStatement(sql);
pstmt.esecuteUpdate();
} catch (SQLException e) {
throw e;
} finally {
if (pstmt != null) {
pstmt.close();
}
}
}
/* 등록 */
public void insertTeam(List<Member> memberList) {
StringBuilder sql = new StringBuilder();
sql.append("INSERT INTO TEAM (");
sql.append("name, mail_address, position, code, insert_timestamp, update_timestamp")
sql.append(") VALUES (");
sql.append("?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP");
sql.append(")");
PreparedStatement pstmt = null;
int cnt = 0;
try {
pstmt = conn.prepareStatement(sql.toString());
for (Member m : memberList) {
pstmt.setObject(1, m.getName());
pstmt.setObject(2, m.getMailAddress());
pstmt.setObject(3, m.getPosition());
pstmt.setObject(4, "0"); // code 는 0 고정
// addBatch
pstmt.addBatch();
cnt++; // 배치 갯수
// 파라미터 클리어
pstmt.clearParameters();
// 메모리 때문에 5,000건씩 실행
if ((cnt % 5000) == 0) {
pstmt.executeBatch();
// SQL문 실행후 배치 클리어
pstmt.clearBatch();
}
}
// 5000건 미만의 나머지 건수 실행
pstmt.executeBatch();
// 배치 클리어
pstmt.clearBatch();
} catch (SQLException e) {
throw e;
} finally {
if (pstmt != null) {
pstmt.close();
}
}
}
}
addBatch() 를 사용할 때 주의해야 할 사항은 너무 많은 쿼리문을 메모리에 올리면 안된다는 것이다.
너무 많은 쿼리문을 메모리에 올리게되면, OutOfMemoryError 가 발생할 가능성이있다.
java.lang.OutOfMemoryError: Java heap space
그래서 예제에서도 5,000건 단위로 메모리에 올려두었다가 쿼리문을 날려주고 있다.
해당 건수는 환경마다 다르니 확인을 통해 적절한 건수를 정해야 한다.
addBatch() 를 사용하는 방법으로 바꾼 후 테스트 데이터 10,000건으로 성능을 확인해보니 1초도 걸리지 않았다.
로그로 실행시간을 측정해보니 결과는 약 80ms정도가 걸렸다.