CTE 쿼리에 컬럼 별칭으로 지정된 슈퍼 키 성능 개선

회계 시스템에서 입력된 전표 데이터를 특정 기준에 따라 집계해 보고서로 보여주는 기능에 속도 이슈가 발생했다.

이 문제를 어떻게 해결했는지 경험을 공유하고자 한다.

상황

회계 시스템에서 입력된 전표 데이터를 바탕으로 재무제표 등 여러 보고서를 조회할 수 있는데, 조회 속도가 느릴 때는 10초, 심할 때는 2분 이상 걸리는 문제가 있었다.

긴 쿼리 중 문제가 되었던 예시 쿼리는 다음과 같다.

WITH ACC_SOURCE AS (SELECT
                        -- 슈퍼 키
                        ID_ORG || ' ' || SQ_ACCSES || ' ' || ID_ACCCD AS SUPER_KEY,
                        A.*
                    FROM ACC_SOURCE_OG A),
     X1_TBL AS (SELECT *
                FROM ACC_SOURCE
                WHERE SUPER_KEY = 'A00'),
     X2_TBL AS (SELECT *
                FROM X1_TBL
                WHERE SUPER_KEY = 'A10'),
    -- 원래 이후 더 많은 쿼리 존재...
SELECT AMT_DR, AMT_CR
FROM X2_TBL;

ACC_SOURCE_OG에서 여러 컬럼을 조합해 SUPER_KEY를 만들고, 이를 기준으로 X1_TBL, X2_TBL을 생성해 최종적으로 AMT_DR, AMT_CR을 조회하는 쿼리다.

위에서 아래로 내려가면서 ACC_SOURCE_OG → ACC_SOURCE → X1_TBL → X2_TBL 순으로 CTE(Common Table Expression) 쿼리가 체이닝되어 있는 것을 볼 수 있다.

느린 원인 분석

하위 쿼리에서는 Source 테이블에 계속 접근해야 하는데 KEY가 컬럼 별칭으로 존재하고 있었다.

CTE이기 때문에 하위 쿼리에서 조회할 때마다 재평가되어 큰 오버헤드가 발생했고, SUPER_KEY 역시 컬럼 별칭이라 Index가 없어 Full-Scan이 반복되고 있었다.

개선 방법

CTE로 정의된 테이블은 서브 쿼리처럼 사용할 때마다 계산된다. 그래서 CTE를 물리 테이블로 분리하는 것만으로도 어느 정도 속도가 개선된다. (10분이 넘어도 조회가 안 되던 쿼리가 4초 만에 조회될 정도로 빨라졌다. 하지만 Index를 사용하면 500ms 이하로 훨씬 더 빨라진다.)

KEY를 컬럼 별칭으로 사용하다 보니 Full-Scan이 발생했는데, Index를 지정하면 훨씬 더 빨라질 것이다.

이에 따라 Source 물리 테이블에 SUPER_KEY를 물리 컬럼으로 추가하고, Index를 생성하여 해결했다.

WITH X1_TBL AS (SELECT *
                FROM ACC_SOURCE_OG
                WHERE SUPER_KEY = 'A00'),
     X2_TBL AS (SELECT *
                FROM X1_TBL
                WHERE SUPER_KEY = 'A10'),
    -- 원래 이후 더 많은 쿼리 존재...
SELECT AMT_DR, AMT_CR
FROM X2_TBL;

속도 개선 결과

정말 느린 경우는 14분(840초) 걸리던 것이 1.2초로 줄어 700배 빨라졌다.

적어도 전체적으로 3배 이상은 빨라졌다.

마무리

결론을 보면 당연히 KEY 값은 물리 컬럼으로 PK를 지정해서 Index가 되어야 하는 것 아니야? 싶지만, 그렇지 않은 쿼리도 존재했고... 복잡한 시스템 사이에서 이걸 찾기란 쉽지 않았다. 이런 결론에 도달하는 데 많은 시간이 걸렸다.

느린 부분을 찾고 해결을 하며 속도 개선이 눈에 보이니 재미있었다.

이번 경험을 통하여 CTE 쿼리의 성능 이슈를 알게 되었고, Index를 활용해 성능 개선을 직접 경험할 수 있었다.

누군가 이 개선 사례를 바탕으로 도움이 되길 바라며 글을 마친다.