2021 02 19 - 서브쿼리

서브쿼리 단점

코드 가독성이 떨어진다

서브쿼리는 대부분 일시적인 영역(메모리 또는 디스크)에 확보되므로 오버헤드가 생긴다.

서브쿼리는 인덱스 또는 제약 정보를 가지지 않기 때문에 최적하되지 못한다.

이 쿼리는 결합을 필요로 하기 때문에 비용이 높고 실행 계획 변동 리스크가 발생한다.

 

-- 서브쿼리를 이용하거나 상관 서브쿼리를 사용하면 테이블스캔이 두번 일어난다.
-- 윈도우 함수를 사용하면 테이블 접근이 1회만 일어난다.

SELECT cust_id, seq, price
　FROM (SELECT cust_id, seq, price,
               ROW_NUMBER()
                 OVER (PARTITION BY cust_id
                           ORDER BY seq) AS row_seq
          FROM Receipts ) WORK
 WHERE WORK.row_seq = 1;

결합을 사용한 쿼리는 두개의 불안정 요소가 있다.

- 결합 알고리즘의 변동 리스크

- 환경 요인에 의한 지연 리스크(인덱스, 메모리, 매개변수 등)

 

상황에 따라서 결합 알고리즘은 변동된다.

때문에 어느순간 역치를 넘어 실행알고리즘에 변동이 생기면 성능에 큰 변화가 일어난다. 결합을 사용하면 이러한 변동리스크를 안을수 밖에 없다

 

레코드 수가 적은 테이블

- Nested Loop

큰테이블을 결합하는 경우

- Sort Merge 

- Hash

 

환경요인에 의한 지연리스크

Nested Loops의 내부 테이블 결합키에 인덱스가 존재하면 성능이 크게 개선된다.

- Sort Merge / Hash가 선택되어 TEMP 탈락이 발생하는 경우 작업메모리를 늘려주면 성능을 개선할 수 있다.

 

서브쿼리 의존증

최댓값을 가지는 레코드와 함께 양쪽 price 필ㄷ의 차이도 구해보자.

SELECT cust_id,
       SUM(CASE WHEN min_seq = 1 THEN price ELSE 0 END)
         - SUM(CASE WHEN max_seq = 1 THEN price ELSE 0 END) AS diff
　FROM (SELECT cust_id, price,
               ROW_NUMBER() OVER (PARTITION BY cust_id
                                      ORDER BY seq) AS min_seq,
               ROW_NUMBER() OVER (PARTITION BY cust_id
                                      ORDER BY seq DESC) AS max_seq
          FROM Receipts ) WORK
 WHERE WORK.min_seq = 1
    OR WORK.max_seq = 1
 GROUP BY cust_id;

 

서브쿼리가 유용한경우

 

결합 대상 레코드  수

1) 결합 을 먼저 수행

SELECT C.co_cd, C.district,
       SUM(emp_nbr) AS sum_emp
　FROM Companies C
         INNER JOIN
           Shops S
    ON C.co_cd = S.co_cd
 WHERE main_flg = 'Y'
 GROUP BY C.co_cd;

결합을 먼저 수행하고, 결과에 GROUP BY를 적용해서 집약한다.

2) 사업소 테이블을 집약해서 직원수를 구하고 회사 테이블과 결합했다.

SELECT C.co_cd, C.district, sum_emp
　FROM Companies C
         INNER JOIN
          (SELECT co_cd,
                  SUM(emp_nbr) AS sum_emp
             FROM Shops
            WHERE main_flg = 'Y'
            GROUP BY co_cd) CSUM
    ON C.co_cd = CSUM.co_cd;

 

두가지 방법은 성능적으로 차이를 보인다.

바로바로바로 결합 대상 레코드 수가 다르다는것이다. 예를들어,

 

첫번째 쿼리를 결합할때 레코드의 개수는,

회사테이블: 레코드 4개

사업소 테이블: 레코드 10개

 

대충 결합할 레코드의 수라고 생각하자

사업소 테이블을 걸러내지 않고 그대로 결합을 시도하고 있다.

 

반면 두번째 쿼리는 main_flag ='Y'조건으로 한번 필터링하고 나머지 레코드들을 결합하고 있으니

결합비용을 낮출수있다.

 

테이블의 규모가 매우 크다면 결합 대상 레코드를 집약하는것이 I/O 비용을 더 줄잀 ㅜ있다

 

하지만 절대적인것은 아니며, 환경에 따라 다르므로 실제 개발을 할때는 성능을 테스트하고 판단을 내리는것이 좋다.