SQL 쿼리 최적화 - CTE로 복잡한 조건 정리하기

 

복잡한 SQL 쿼리에서 서브쿼리를 중첩하거나 동일한 조건을 반복적으로 쓰게 되면 수행시간이 늘어나고 가독성도 떨어질 수 있습니다.

이럴 때 CTE(Common Table Expression)을 사용하면 쿼리 구조를 더 명확하게 많들고 수행시간도 단축 시킬 수 있습니다. 

 

 

오늘은  제가 실무에서 직접 경험한 CTE 최적화 사례를 소개드리겠습니다.

 

 

1. CTE ?

 

CTE는 SQL에서 복잡한 쿼리를 읽기 쉽고, 재사용 가능하게 만들어주는 기능입니다.

WITH 절을 사용하여, 마치 쿼리 안에 이름 붙인 임시 테이블을 만드는 것과 비슷한 개념입니다.

 

 

2. 기존 쿼리

기존 쿼리는 다음과 같은 구조로 되어 있었습니다. 

SELECT 
    ...
    (SELECT COUNT(*) FROM child_product cp WHERE cp.product_seq = pp.product_seq AND cp.product_status = '001') AS productCnt,
    (SELECT COUNT(*) FROM child_product cp WHERE cp.product_seq = pp.product_seq AND cp.product_status = '002') AS completeCnt,
    (SELECT COUNT(*) FROM child_product cp WHERE cp.product_seq = pp.product_seq AND cp.product_status = '003') AS cancelCnt,
    COALESCE(ps.product_status, '000') AS prdStatus,
    pp.product_date 
FROM parent_product pp
LEFT JOIN (
    SELECT
        product_seq,
        CASE
            WHEN SUM(CASE WHEN product_status = '001' THEN 1 ELSE 0 END) > 0 THEN '001'
            WHEN COUNT(product_status) = SUM(CASE WHEN product_status = '002' THEN 1 ELSE 0 END) THEN '002'
            ELSE '003'
        END AS product_status
    FROM child_product
    GROUP BY product_seq
) ps ON pp.product_seq = ps.product_seq
...

2-1. 테이블 관계 및 기본 구조

parent_product : 부모 테이블 (1)

 : 하나의 product 작업에 대해 한 건만 생성됩니다.

child_product : 자식 테이블 (N)

 : 하나의 parent_product 에 대해 여러 건이 생성되고 각각 상태 코드 (product_status)를 가집니다.

 

2-2. 조회 목적

이번 조회는 parent_product 기준으로 목록을 출력하지만 각 부모 항목에 대해 다음 정보를 함께 제공해야 합니다.

 

첫번째, 해당 parent에 속한 child의 상태별 개수 

• '001' : Product 생산 중
• '002' : Product 생산 완료
• '003' : Product 생산 취소
• '000' : Product 생산 준비

두번째, Child들의 상태값을 기준으로 parent의 종합 상태값을 판단

 

2-3. 상태 판단 로직 

parent_product의 상태는 다음 기준으로 결정됩니다. 

조건	상태
child에 '001' 상태가 하나라도 있다면	001 : Product 생산 중
전체 child의 상태가 모두 '002'일 경우	002 : Product 생산 완료
위 두 조건에 모두 해당하지 않을 경우	003 : Product 생산 취소
child가 하나도 존재하지 않아 LEFT JOIN 결과가 NULL일 경우	000 : Product 생산 준비

 

 

기존 쿼리로 실행했을 때 parent와 child의 데이터양이 작을 땐 문제가 발생하지 않지만 데이터양이 늘어나면서 중복 서브쿼리로 인해 child_product의 접근 횟수가 N * 3번이 되므로 쿼리 실행 속도가 엄청나게 느려집니다. 

 

실제 운영 환경에서 조회가 느리다는 VOC를 받았지만 사용했던 쿼리입니다.

 

 

3. 쿼리 최적화 

WITH절을 사용한 서브쿼리 집계로 상태별 COUNT + 상태 판정을 한 번에 처리하였습니다. 

WITH status_product AS (
    SELECT
        product_seq,
        SUM(CASE WHEN product_status = '001' THEN 1 ELSE 0 END) AS productCnt,
        SUM(CASE WHEN product_status = '002' THEN 1 ELSE 0 END) AS completeCnt,
        SUM(CASE WHEN product_status = '003' THEN 1 ELSE 0 END) AS cancelCnt,
        CASE
            WHEN SUM(CASE WHEN product_status = '001' THEN 1 ELSE 0 END) > 0 THEN '001'
            WHEN COUNT(product_status) = SUM(CASE WHEN product_status = '002' THEN 1 ELSE 0 END) THEN '002'
            ELSE '003'
        END AS product_status
    FROM child_product
    GROUP BY product_seq
)

SELECT 
    pp.product_seq,
    sp.productCnt,
    sp.completeCnt,
    sp.cancelCnt,
    COALESCE(sp.product_status, '000') AS prdStatus,
    pp.product_date
FROM parent_product pp
LEFT JOIN status_product sp ON pp.product_seq = sp.product_seq

 

 

 

4. 성능 비교 

 

 

Execution Time (실행시간)이 약 1.4초(1408ms) 정도 크게 단축되었다는 걸 확인하여 병목 지점 개선과 불필요한 연산이 줄어들었다는 걸 확인할 수 있었습니다.