대용량 쿼리 ResultRowDataHandler + 대용량 엑셀다운로드 SXSSFWorkbook 를 이용하여 OOM(Out Of Memory), GC overhead limit exceeded 방지

강준한입니다.

스트리밍 버전으로 메모리를 적게 사용 된다고 하는 SXSSF를 이용하여 대용량 엑셀 다운로드 를 사용 했습니다

 

SXSSFWorkbook wb = null;
FileOutputStream fos = null;
SXSSFSheet sheet;
SXSSFRow row;
SXSSFCell cell;
try {
    //크롬 인코딩
    fileName = new String(fileName.getBytes("UTF-8"),"ISO-8859-1");
    wb = new SXSSFWorkbook(100); //메모리 누적 해가면 DISK 에 저장 하는 기준 사이즈
    fos = new FileOutputStream("경로");
    wb.setCompressTempFiles(true);

    sheet = wb.createSheet("Sheet1");

    int position = 0;
    row = sheet.createRow(position);

//          컬럼명
    for (int i = 0; i < columns.length; ++i) {
        cell = row.createCell(i);
        cell.setCellValue(columns[i]);
    }

//          데이터
    for (int i = 0; i < tmpData.size(); i++) {
        position++;
        row = sheet.createRow(position);
        int k = 0;
        for (int column = 0; column < columns.length; ++column) {
            cell = row.createCell(k);
            if (tmpData.get(i).get(columns[k]) != null) {
                cell.setCellValue(tmpData.get(i).get(columns[k]).toString());
            }
            k++;
        }
    }
    res.setContentType("application/vnd.ms-excel");
    // 엑셀 파일명 설정
    res.setHeader("Content-Disposition", "attachment;filename="+파일이름+".xlsx");
    wb.write(res.getOutputStream());

} catch (Exception e) {
    log.error(e.getMessage(), e);

} finally {
    try {
        wb.close();
        wb.dispose();
        if (fos != null) {
            fos.close();
        }
    } catch (IOException e) {
        log.error(e.getMessage(), e);
    }
}

이 방식은 그저 엑셀을 생성 하는 부분이라는 점임니다.

 

주로 엑셀을 다운로드 할 상황은 쿼리를 보내 ROW 를 가져와 엑셀을 만드는 형태입니다.

엑셀의 생성에는 문제 없으나 쿼리가 몇십만 ROW 이다 보니 거기에서 서버가 힘을 내지 못하는 상황이 었습니다.

 

쿼리를 조금씩 가져와 엑셀에 쌓는 방법은 없을까 고민 하다가

org.apache.ibatis 에 괜찮은 기능을 찾았습니다.

 

ResultRowDataHandler

 

주로 mybatis 에서 쿼리를 조회 해오면 리스트에 담아 오는 형태인데 핸들러라는 말이 들어간거 보니 ROW 단위로 DATA를 핸들링 하는 느낌이 들어 심도있게 파봤습니다.

 

public class  ResultRowDataHandler implements ResultHandler {
    private final SXSSFWorkbook workbook;
    private final SXSSFSheet sheet;
    SXSSFRow row;
    SXSSFCell cell;
    FileOutputStream fileOutputStream;
    private final String[] columns;

    public ResultRowDataHandler(Map<String, Object> params, String[] col, Map<String, Object> getConfig) throws FileNotFoundException {
        // 현재 날짜 구하기
        LocalDate now = LocalDate.now();
        // 포맷 정의
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");
        // 포맷 적용
        String formatedNow = now.format(formatter);

        workbook = new SXSSFWorkbook(10000);
        workbook.setCompressTempFiles(true);
        sheet = workbook.createSheet("Sheet1");

        row = sheet.createRow(0);
        columns = col;
        //컬럼명
        for (int i = 0; i < columns.length; ++i) {
            cell = row.createCell(i);
            cell.setCellValue(columns[i]);
           
        }

        String savePath = params.get("path").toString() + "/" + formatedNow+"_"+params.get("SaveFileName").toString() + ".xlsx";
        fileOutputStream = new FileOutputStream(savePath);

    }

    @Override
    public void handleResult(ResultContext resultContext) {
        Map<String, Object> result = (Map<String, Object>) resultContext.getResultObject();
        //데이터
        row = sheet.createRow(resultContext.getResultCount());
        int k = 0;
        for (int column = 0; column < columns.length; ++column) {
            cell = row.createCell(k);
            if (result.get(columns[k]) != null) {
                cell.setCellValue(result.get(columns[k]).toString());
            }
            k++;
        }
    }

    public void close() {
        try{
            workbook.write(fileOutputStream);
            workbook.dispose();
            workbook.close();
            fileOutputStream.close();
        }catch (Exception e){
            log.error("ERROR : ", e);
        }
    }
}

우선 핸드러를 여기저기 구글링 하여 여차 저차 작성 했습니다.

가져온 데이터를 앞서 작성해본 엑셀에 차차 쌓는 형식의 헨들링 파일을 작성하고

 

핸들러를 호출 합니다 

다오에서 매퍼로 넘길때까지 핸들러를 같이 넘겨 쿼리를 실행하며 엑셀을 생성 합니다.

30만 ROW 까지는 끄떡없지만 ... 많이 느리네요