CORSS APPLY 부분 범위 처리

1. 사전 구성

SELECT number id
	, CASE WHEN number <= 50 THEN 1 ELSE 2 END gid
	, DATEADD(dd, number, '2020-01-01') date
INTO TB_CAFirst
FROM master..spt_values
WHERE type = 'P'
	AND number BETWEEN 1 AND 100
;
ALTER TABLE TB_CAFirst ADD CONSTRAINT PK_CAFirst PRIMARY KEY (id)
;
CREATE INDEX NIDX01_CAFirst ON TB_CAFirst (gid) INCLUDE (id)
;

WITH w_values AS
	(SELECT number
	FROM master..spt_values
	WHERE type = 'P'
		AND number BETWEEN 1 AND 100)
SELECT a.number id
	, DATEADD(dd, b.number, '2020-01-01') date
	, NEWID() code
INTO TB_CASecond
FROM w_values a, w_values b, w_values c
;
ALTER TABLE TB_CASecond ADD CONSTRAINT FK_CASecond FOREIGN KEY (id)
	REFERENCES TB_CAFirst (id)
;
CREATE INDEX NIDX01_CASecond ON TB_CASecond (id, date) INCLUDE (code);

2. AS-IS

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 TB_CAFirst f
	INNER JOIN TB_CASecond s
		ON f.id = s.id
	WHERE f.gid = 1
) a
WHERE a.rn <= 5
;

• [TB_CAFirst] 테이블의 [gid] = 1 인 데이터와 [TB_CASecond] 테이블을 조인한 결과 중 [id] 별로 가장 최근 데이터 5건씩만 출력시키는 구문.
• 조인 조건에 사용된 [id] 컬럼은 [TB_CAFirst] 테이블과 [TB_CASecond] 테이블 간에 1:N 관계를 가지며 [TB_CASecond] 테이블은 [id] 값 마다 10,000건의 행이 존재.
• 이 구문의 선행되는 테이블에서 [gid] = 1 인 조건에 해당하는 데이터는 50건이므로 최종적으로 출력되는 데이터는 최대 250건(50건*5)이 된다.
• [id] 컬럼의 값마다 5건의 데이터만 요구되지만 두 테이블의 조인이 완료된 뒤 Filter 연산자를 통해 5개의 값을 추출하기 때문에 499,750(50*1000-250)건만큼 불필요한 데이터까지 읽어내는 문제가 발생.

3. TO-BE

SELECT f.id, s.code, s.date
FROM TB_CAFirst f
CROSS APPLY (
        SELECT TOP 5 code, date
	FROM TB_CASecond s
	WHERE f.id = s.id
	ORDER BY date DESC) s
WHERE f.gid = 1
;

• CROSS APPLY : 인라인뷰(FROM 절의 서브 쿼리)에서 조인 조건으로 전달받을 값을 사용할 수 있도록 지원하는 조인 방식.
• CROSS APPLY를 사용하면 [TB_CAFirst] 테이블의 [id] 값을 인라인 뷰 내부에 전달하며 [id] 값마다 TOP 5건의 데이터를 출력할 수 있다.