NOT IN 조건으로 사용된 서브쿼리 성능 이슈

1. 사전구성

SELECT c.id
  , CASE WHEN c.id <=5 THEN 1
    WHEN c.id <= 10 THEN 2
    WHEN c.id <= 15 THEN 3
    WHEN c.id <= 20 THEN 4
    ELSE 5 END gid
  , NEWID() code1
  , NEWID() code2
INTO TB_Maind8
FROM
  (SELECT ROW_NUMBER() OVER(ORDER BY a.number) id
  FROM master..spt_values a, master..spt_values b
  WHERE a.type = 'P'
    AND a.number BETWEEN 1 AND 100
    AND b.type = 'P'
    AND b.number BETWEEN 1 AND 100) c
;
 
SELECT *
INTO TB_Subd8
FROM TB_Maind8;
 
CREATE CLUSTERED INDEX CIDX_Maind8 ON TB_Maind8(gid);
CREATE INDEX IDX01_Subd8 ON TB_Subd8(id);

 

table_name	index_name	index_description	Is_Unique	indexed_columns	included_columns
TB_Maind8	CIDX_Maind8	clustered	0	gid	[NULL]
TB_Subd8	IDX01_Subd8	nonclustered	0	id	[NULL]

 

2. AS-IS

SELECT COUNT(id)
FROM TB_Subd8 a
WHERE id NOT IN (SELECT DISTINCT id
  FROM TB_Maind8
  WHERE gid BETWEEN 2 AND 4);

• 데이터를 제외하기 위해 NOT IN 절 사용.
• 두 테이블의 데이터를 비교하는 과정에서 NL Join이 수행.
• 선행 테이블 [TB_Subd8]의 결과 집합 10,000건에 대해 후행 테이블인 [TB_Maind8]에 반복 조인되면서 많은 I/O 비용 발생.

 

3. TO_BE

SELECT COUNT(a.id)
FROM TB_Subd8 a
LEFT JOIN (
    SELECT id
    FROM TB_Maind8
    WHERE gid BETWEEN 2 AND 4
  ) b 
  ON b.id = a.id
WHERE b.id IS NULL
;

• LEFT OUTER JOIN으로 구문 변경하여 다른 조인 방식으로 수행되도록 유도.
• [TB_Subd8] 테이블은 조인 키에 해당되는 [id] 컬럼이 인덱스로 인해 이미 정렬.
• [TB_Maind8] 테이블은 DISTINCT로 정렬을 선행하기 때문에,
  테이블을 한 번씩 읽어내면서 정렬된 데이터로 조인하는 Merge Join으로 성능 개선.