IN 조건절 성능 이슈

1. 사전구성

CREATE TABLE TB_ORFirst (seq INT, date DATETIME);
CREATE TABLE TB_ORSecond (seq INT, gid INT, txt NVARCHAR(36) DEFAULT (NEWID() ) );

INSERT INTO TB_ORFirst
SELECT ROW_NUMBER() OVER (ORDER BY a.number) seq
  , DATEADD(d, a.number, '2020-01-01') date
FROM master..spt_values a (NOLOCK)
  , (SELECT number
    FROM master..spt_values (NOLOCK)
    WHERE type = 'P' AND number <20) b
WHERE type = 'P'
  AND a.number <= 365
;

INSERT INTO TB_ORSecond
SELECT b.seq, a.number, NEWID()
FROM master..spt_values a (NOLOCK), TB_ORFirst b
WHERE type = 'P'
  AND number%2 = 1
  AND number <= 100
;
  
CREATE CLUSTERED INDEX CIDX01_ORFirst ON TB_ORFirst (date);
CREATE INDEX NIDX01_ORSecond ON TB_ORSecond (seq, gid) INCLUDE (txt);

table_name	index_name	index_description	Is_Unique	indexed_columns	included_columns
TB_ORFirst	CIDX01_ORFirst	clustered	0	date	[NULL]
TB_ORSecond	NIDX01_ORSecond	nonclustered	0	seq, gid	txt

 

2. AS-IS

SELECT f.date, s.txt
FROM TB_ORFirst f
INNER JOIN TB_ORSecond s 
  ON f.seq = s.seq
WHERE f.date BETWEEN '2020-01-01' AND '2020-02-28'
  AND s.gid IN (1,2,3,7,8,9,13,14,15,19,20,21,25,26,27,31,32,33)
;

• 후행 테이블의 조인 조건과 IN 조건절 컬럼이 인덱스 키로 생성되어 있다. 인덱스로 탐색하는 횟수가 과다하게 많아 지는 현상이다.
• NL Join으로 인해 선행 테이블의 결과 집합 건수(1,180)만큼 후행 테이블을 참조하며 그 결과 집합은 1건당 IN 절의 조건 개수(18) 만큼 인덱스를 탐색하게 된다.
• 테이블 검색 수 = 선행 테이블 데이터 건수 * IN 조건절 개수 = 1,180 * 18 = 21,240

 

3. TO_BE

// IN 조건절이 인덱스 탐색 조건의 대상이 되지 못 하도록 컬럼을 고의적으로 가공.
SELECT f.date, s.txt
FROM TB_ORFirst f
INNER JOIN TB_ORSecond s 
  ON f.seq = s.seq
WHERE f.date BETWEEN '2020-01-01' AND '2020-02-28'
  AND s.gid + 0 IN (1,2,3,7,8,9,13,14,15,19,20,21,25,26,27,31,32,33)
;

// Hash Join으로 유도하여 테이블 검색 수를 최소화
CREATE INDEX NIDX02_ORSecond ON TB_ORSecond (gid) INCLUDE (seq, txt)
;
SELECT f.date, s.txt
FROM TB_ORFirst f
INNER JOIN TB_ORSecond s 
  ON f.seq = s.seq
WHERE f.date BETWEEN '2020-01-01' AND '2020-02-28'
  AND s.gid IN (1,2,3,7,8,9,13,14,15,19,20,21,25,26,27,31,32,33)
OPTION (HASH JOIN )
;

1. IN 조건절이 인덱스 탐색 조건의 대상이 되지 못 하도록 컬럼을 고의적으로 가공한다.
2. Hash Join으로 유도하여 테이블 검색 수를 최소화한다.

• 조인 조건을 제외한 조건절 컬럼을 키로 생성하여 Hash Join으로 유도한다.
• IN 절의 조건절 개수 만큼만 인덱스로 탐색을 수행하기 때문에 검색 횟수가 감소된다.
• 조건절을 탐색하는 스캔 범위에 따라 I/O가 결정된다.