개요
대용량 데이터를 오라클 DB에서 읽어 sam 파일로 내려받는 작업을 jdbc 프로그램으로 구현한다. 예제에서는 오라클 DB를 사용하고 121개의 컬럼을 가진 TB_ITEM_MG 테이블에 1,500,010건의 데이터를 “,”를 구분자로 하는 sam 텍스트 파일로 내려받는다.
JDBC fetch size
JDBC fetch size 매개변수는 Result Set 을 가져오는 시간을 크게 줄일 수 있다. 모든 Statement 객체 또는 PreparedStatement 객체에 설정할 수 있다. Spring이나 Hibernate와 같은 많은 프레임워크에서 이를 수행하는 데 매우 편리한 API를 제공한다.
fetchSize property가 100으로 설정된 경우 어떤 일이 발생하는지 살펴보자. rs.next()가 처음 호출되면 Oracle 드라이버는 데이터베이스에서 처음 100개의 데이터를 가져와 메모리 버퍼에 저장한다. 따라서 다음 99번의 rs.next() 호출에 대해서는 이 버퍼에서 레코드가 검색된다. 버퍼를 완전히 읽은 후 다음 rs.next()는 드라이버가 새로운 데이터 묶음(100)을 버퍼로 가져오도록 강제한다.
fetch size가 100으로 설정된 10,000개 데이터를 읽으려는 경우 드라이버는 연결된 connection을 통해 데이터베이스에 대해 100회 왕복을 수행한다. fetchSize를 500으로 설정하면 드라이버는 데이터베이스에 대해 20번의 왕복만 수행한다.
만일 이 값을 지정하지 않으면 오라클의 경우 기본값은 10이다.
일반적인 JDBC 프로그램에서의 Fetch Size 세팅
PreparedStatement stmt = null;
ResultSet rs = null;
try {
stmt = conn. prepareStatement("select a, b, c from table");
stmt.setFetchSize(200);
rs = stmt.executeQuery();
while (rs.next()) {
...
}
}
구현
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DbToFile {
private static final String JDBC_URL = "jdbc:oracle:thin:@127.0.0.1:1521:test";
private static final String JDBC_USERNAME = "*****";
private static final String JDBC_PASSWORD = "*****";
private static final String FILE_PATH = "c:/batch/data/input/input_data.sam";
public static void main(String[] args) {
BufferedWriter writer = null;
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
int cnt = 0;
int columnCount = 0;
Object columnValue = "";
long startTime = System.currentTimeMillis();
try {
writer = new BufferedWriter(new FileWriter(FILE_PATH));
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD);
String sql = "SELECT * FROM TB_ITEM_MG";
ps = con.prepareStatement(sql);
ps.setFetchSize(1000);
rs = ps.executeQuery();
columnCount = rs.getMetaData().getColumnCount();
while (rs.next()) {
cnt++;
if (cnt % 100000 == 0) {
System.out.println("process cnt : " + cnt);
}
for (int i=0; i<columnCount; i++ ) {
columnValue = rs.getObject(i+1);
if (columnValue == null || rs.wasNull()) {
columnValue = "";
}
if (i==(columnCount - 1)) {
writer.write(columnValue.toString());
writer.newLine();
} else {
writer.write(columnValue.toString());
writer.write(",");
}
}
}
System.out.println("process cnt : " + cnt);
long finishTime = System.currentTimeMillis();
long timeElapsed = (finishTime - startTime) / 1000;
System.out.println("건수 : " + cnt);
System.out.println("작업 시간(sec) : " + timeElapsed);
} catch (ClassNotFoundException | SQLException | IOException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
if (ps != null) ps.close();
if (con != null) con.close();
if (writer != null) writer.close();
} catch (SQLException | IOException e) {
e.printStackTrace();
}
}
}
}
정리
로컬에 설치된 오라클에서 위의 예제를 테스트하였을 때, 다음과 같은 결과를 보였다.
(1) ps.setFetchSize(5000) : 35초
(2) ps.setFetchSize(1000) : 36초
(3) ps.setFetchSize(500) : 36초
(4) ps.setFetchSize(100) : 38초
(5) ps.setFetchSize(50) : 40초
(6) default : 54초
위의 결과를 보면 성능 차이가 크게 느끼지 않을 수 있지만, 실제 상황은 오라클이 원격에 설치되었을 것이기 때문에 유사한 테스트를 한 사례를 구글링하여 찾아보았다.
다음의 사례는 컬럼 3개를 가진 100만개 데이터를 fetch한 결과이다. fetch size를 500으로 했을 때 이를 사용하지 않았을 때(fetch size는 10)보다 12배가 빠르다.
fetch size는 일반적으로 크면 클수록 더 빠를 수는 있지만 메모리와의 가성비를 따져야 한다. fetch size와 성능은 로그 함수의 결과를 보이므로 fetch size를 크게 하면 메모리를 많이 소비하는 것에 비해 기대한 성능을 얻을 수는 없다.