[MSSQL] CROSS APPLY를 통한 성능개선(부분 범위처리 TOP 5)

CROSS APPLY란?

MSSQL에서는 CROSS APPLY 조인 방식을 지원합니다.

인라인 뷰(FROM 절의 서브쿼리)에서 조인 조건으로 전달받은 값을 뷰 안에서 사용 할 수 있도록 지원합니다.

 

CROSS APPLY 성능비교 예제

이번 예제는 T_OUTER 테이블의 gid=1 데이터와 T_INNER 테이블을 조인한 결과에서 id별로 가장 최근 데이터를 5개씩만 출력하는 구문이다.

 

T_OUTER

테이블은 100개의 행을 가지며 id가 1~100까지, 그 중 gid=1 인 행이 50개 gid=2인 행이 50개씩 있다

인덱스

PK_T_OUTER (id)

T_OUTER (gid) INCLUDE (id)

 

T_INNER

 테이블은 1,000,000 행을 가지며 id가 1~100까지 각 id마다 10,000건씩 존재한다

인덱스

T_INNER (id,date) INCLUDE (code)

 

---

[쿼리문-튜닝전]

SELECT id, code, date
FROM (SELECT f.id
      , s.code
      , s.date
      , ROW_NUMBER() OVER ( PARTITION BY s.id ORDER BY s.date DESC) rn
 FROM T_OUTER f
 INNER  JOIN T_INNER s
 ON f.id = s.id
 WHERE f.gid = 1
) a
WHERE a.rn <= 5
GO

T_OUTER 테이블의 gid=1 인 행이 50개 존재 ROW_NUMBER으로 정리된 행 중 최신행을 5개씩 출력하게 되어있으니 50*5=250행을 출력화면에 보여준다.

하지만 INNER JOIN 은 두 테이블을 모두 조인 한 후에 필터를 진행했기 때문에  50*10000-250=499,750 만큼 불필요한 행을 읽어 성능에 문제가 발생하고 있다.

 

 

[쿼리문-튜닝후]

SELECT f.id, s.code,s.date
FROM T_OUTER f CROSS APPLY (SELECT TOP 5 code,date
                               FROM T_INNER s
                               WHERE f.id = s.id
                               ORDER BY date DESC) s
WHERE f.gid = 1
GO

 테이블  T_INNER를 인라인뷰로 지정하여 date로 내림차순 정렬 후 최신 top5 데이터만 읽도록 변경하였다.

Cross apply를 사용하면 T_OUTER 테이블의 id 값을 인라인뷰 내부로 전달하여 id 값마다 top 5건만 출력하여 불필요한 행을 읽는 행위가 제거되었다.

---

[결과 비교]

2개의 쿼리를 동시에 실행 시 처리비용을 비교하면 상당한 차이를 보이고 있다.

이러한 특징을 알고 쿼리 작성 시 CROSS APPLY 를 고려해 보면 좋다.