SPOOL 연산자 성능 개선 - 3

1. 사전구성

SELECT a.number id
  , NEWID() txt
  , DATEADD(dd, a.number-1, '2020-01-01') date
INTO TB_FSpool04
FROM master..spt_values a
WHERE a.type = 'P'
  AND a.number BETWEEN 1 AND 100
;
CREATE CLUSTERED INDEX CIDX_FSpool04 ON TB_FSpool04 (date);
 
SELECT ROW_NUMBER () OVER (ORDER BY a.number)  id
  , CHAR(LEFT(CONVERT(NVARCHAR(4), high), 1) +64) code
  , RIGHT(number * high * low, 2) value
INTO TB_SSpool04
FROM master..spt_values a
WHERE a.type = 'P'
  AND a.number BETWEEN 1 AND 1000
;

 

table_name	index_name	index_description	Is_Unique	indexed_columns	included_columns
TB_FSpool04	CIDX_FSpool04	clustered	0	date	[NULL]

 

All density	Average Length	Columns
0.0099999998	8	date

 

2. AS-IS

SELECT f.id, f.txt
  , (SELECT value
    FROM TB_SSpool04 s
    WHERE f.id = s.id
      AND s.code = 'A') value
FROM TB_FSpool04 f
WHERE f.date BETWEEN '2020-01-01' AND '2020-01-31';

• 서브 쿼리인 [TB_SSpool04] 테이블에는 인덱스가 존재하지 않기 때문에 [TB_FSpool04] 테이블의 결과 건수만큼 테이블 전체 스캔을 반복해야 한다.
• 매번 모든 데이터를 읽어내는 대신 테이블 전체를 읽어 Worktable(임시 테이블)에 저장하고 내부적으로 비 클러스터형 인덱스를 생성하여 이후 입력 값에 대해선 Spool에 인덱스로 탐색하는 것을 반복한다.
• Spool은 임시 테이블에 저장되고 사용되며 이로 인해 Worktable에 대한 I/O가 높게 사용된다.

 

3. TO_BE

// 인덱스 생성
CREATE INDEX NIDX01_SSpool04 ON TB_SSpool04 (id, code) INCLUDE (value);
 
// LEFT JOIN  사용
SELECT f.id, f.txt
  , s.value
FROM TB_FSpool04 f
LEFT JOIN TB_SSpool04 s 
  ON s.id = f.id
  AND s.code = 'A'
WHERE f.date BETWEEN '2020-01-01' AND '2020-01-31';

1. 조인 조건에 사용된 [id] 컬럼과 조건절인 [code] 컬럼에 인덱스를 생성하면,
   조인이 수행될 때 필요한 데이터만 인덱스로 탐색하기 때문에 Spool을 통해 저장하고 재사용하는 과정을 제거할 수 있다.
2. LEFT JOIN 을 사용하면 Spool을 생성하지 않고, TABLE SCAN 후 HASH MATCH JOIN 으로 작동한ㄷ.