배경
현재 사내 ERP에서는 생산공정과 물류 처리를 위한 다양한 인터페이스 배치가 정해진 시간에 자동 실행되고 있습니다. 이 중에서도 특히 자재 이동에 필요한 수불 데이터 인터페이스 작업은 매일 오전 4시에 자동 실행되며, 오전 6시 이전에는 반드시 완료되어야 하는 필수 작업입니다.
수불 데이터가 인터페이스되어야만 실제 생산 공장에서 자재 이동 및 생산계획을 수행할 수 있기 때문입니다.
그런데, 6월 1일 새벽 예상치 못한 배치 지연 이슈가 발생했습니다. 😢
문제의 시작: 마감 작업과 수불 작업의 충돌
6월 1일 오전 3시 30분에 시작된 전월 미입금 마감 배치 작업(up_STR_Agency_MthClosing_U01)이 예상보다 훨씬 오래 걸리며 약 3시간 이상 소요되었습니다.
그런데 문제는 이 마감 작업이 데이터를 점유한 상태로 장시간 실행되면서, 4시에 실행된 수불 인터페이스 작업(up_STR_BATCH_ShopOrderToRecvSupp_C01)도 해당 테이블에 접근하려다 서로 자원을 점유하면서 병목 현상이 발생하게 됩니다.
두 작업이 동시에 완료되지 못하고 서로 얽히면서 수불 데이터 인터페이스 작업이 오전 6시를 넘겨서야 종료되었습니다. 이로 인해 생산공장에서 자재 확인 및 이동이 지연되었고, 결국 생산 계획 전반에 차질이 발생함에 따라 단순한 지연을 넘어 현장 운영 전체에 치명적인 영향을 미치는 문제로 이어졌습니다.
원인 분석
문제의 핵심 원인은 BankSummary 테이블의 비효율적인 조회 방식에 있었습니다. 해당 테이블은 TransDate를 복합 키로 가지며, 클러스터드 인덱스가 설정된 상태였습니다.
그러나 아래와 같이 TransDate에 CONVERT() 함수를 적용해 YYYYMM 형식으로 가공하는 방식으로 조회가 이루어졌습니다.
WHERE CONVERT(DATE, TRANDATE) BETWEEN @pStartDate AND @pEndDate이로 인해 인덱스를 사용할 수 없게 되고, 결국 테이블 전체를 스캔하는 full scan이 발생합니다. 실행계획에서도 인덱스가 아닌 전체 테이블 접근이 확인되었으며, 수백만 건이 누적된 테이블에서 심각한 병목과 배치 지연을 유발하는 직접적인 원인으로 작용했습니다.
해결 방법
쿼리 성능을 개선하기 위해 필드(TransDate)에 함수를 적용하는 대신, 파라미터 쪽을 가공하였습니다.
즉, YYYYMM을 비교하는 방식 대신, 날짜 범위를 이용해 직접 비교하도록 쿼리를 변경하였습니다
-- 개선 전 (인덱스 미사용)
WHERE CONVERT(VARCHAR(6), TransDate, 112) = @psYYYYMM
-- 개선 후 (인덱스 사용 가능)
WHERE TransDate BETWEEN @pStartDate AND @pEndDate필드에 함수를 적용하지 않고, 파라미터를 날짜 타입 범위로 명확하게 전달하여 인덱스가 정상적으로 작동할 수 있도록 쿼리를 수정하였습니다.
결과
해당 개선 이후 실행계획을 통해 Index Seek가 발생하는 것을 확인하였고, 기존에 수 분 이상 소요되던 쿼리의 실행 시간이 수 초 이내로 감소했습니다. 이는 곧 배치 전체 소요 시간 단축으로 이어졌고, 병목 현상 없이 안정적으로 인터페이스가 완료되는 구조를 마련할 수 있었습니다.
마무리
이번 배치 작업 개선은 단순한 쿼리 수정이었지만, 인덱스 미사용으로 인한 병목을 해소하고 배치 성능을 가시적으로 향상시켰다는 점에서 의미 있는 개선을 이루었다고 생각합니다.
특히, ERP 시스템 내에서 정해진 시간 안에 반드시 완료되어야 하는 작업의 안정성 확보는 운영 측면에서 매우 중요하게 다뤄야할 포인트라는 것을 알게 되었습니다.
물론, 성능 저하의 원인이 쿼리뿐만 아니라 데이터 증가, 테이블 구조, 병렬 처리 방식 등 다양한 요소와 얽혀 있는 만큼, 앞으로도 지속적인 모니터링과 쿼리 리팩토링이 필요할 수 있습니다.
그러나 이번 개선을 통해 작은 쿼리 한 줄이 시스템 전체에 미치는 영향을 다시금 체감할 수 있었고, 성능 개선은 결국 꾸준한 원인 분석과 반복적인 개선 노력에서 비롯된다는 교훈을 얻을 수 있었습니다.
긴 글 읽어져서서 감사합니다. 저의 경험이 다른 개발자분들에게 작은 도움이라도 되길 바라면서 글 마치도록 하겠습니다. 😁
