스칼라 서브 쿼리 성능 이슈

1. 사전구성

SELECT
  rownum AS seq
  , NEWID() AS sub_code
  , DATEADD(ss, rownum, '2020-01-01 00:00:00.000') AS date
INTO TB_Sub05
FROM
  (SELECT
    ROW_NUMBER() OVER (ORDER BY a.number) AS rownum
    , a.number
  FROM master..spt_values a
  JOIN master..spt_values b
    ON a.type = 'P'
    AND b.type = 'P'
    AND a.number <= 1000
    AND b.number <= 1000
  ) a
;

SELECT
  sub_code AS main_code
  , CASE WHEn seq < 30000 THEN 11 ELSE CAST(seq%10 AS INT) END AS value
INTO TB_Main05
FROM TB_Sub05
;

CREATE CLUSTERED INDEX CIDX_Main05 ON TB_Main05 (main_code);
CREATE INDEX NIDX01_Main05 ON TB_Main05 (value);
CREATE CLUSTERED INDEX CIDX_Sub05 ON TB_Sub05 (date);

 

table_name	index_name	index_description	Is_Unique	indexed_columns	included_columns
TB_Main05	CIDX_Main05	clustered	0	main_code	[NULL]
TB_Main05	NIDX01_Main05	nonclustered	0	value	[NULL]
TB_Sub05	CIDX_Sub05	clustered	0	date	[NULL]

 

2. AS-IS

SELECT *
  , (SELECT sub_code
    FROM TB_Sub05
    WHERE sub_code = a.main_code
      AND date BETWEEN '2020-01-01 00:09:00.000' AND '2020-01-01 00:20:00.000')
FROM TB_Main05 a
WHERE a.value = 11;

• 스칼라 서브 쿼리는 하나의 입력 값에 대해 출력되는 결과로 2개 이상을 가질 수 없다.
• 데이터가 유일한 값이라는 것을 보장할 수 없기 때문에 데이터 검증을 위해 NL Join으로 수행된다.
• 이로 인해서 메인 쿼리의 결과 집합인 약 3만 건의 데이터가 스칼라 서브 쿼리로 반복 호출되기 때문에 많은 I/O가 사용된다.

 

3. TO_BE

// 1. 스칼라 서브 쿼리 구문에 DISTINCT 추가
SELECT *
  , (SELECT DISTINCT sub_code
    FROM TB_Sub05
    WHERE sub_code = a.main_code
      AND date BETWEEN '2020-01-01 00:09:00.000' AND '2020-01-01 00:20:00.000')
FROM TB_Main05 a
WHERE a.value = 11;

// 2. 스칼라 서브 쿼리를 LEFT OUTER JOIN으로 구문 변경
SELECT *
FROM TB_Main05 a
LEFT JOIN 
  (SELECT sub_code
    FROM TB_Sub05
    WHERE date BETWEEN '2020-01-01 00:09:00.000' AND '2020-01-01 00:20:00.000') b
ON a.main_code = b.sub_code
WHERE a.value = 11;

1. 스칼라 서브 쿼리 구문에 DISTINCT 추가
   • DISTINCT를 추가하여 [sub_code] 값이 유일한 값임을 정의하여 NL Join이 아닌 Merge Join 방식으로 수행되도록 유도.
2. 스칼라 서브 쿼리를 LEFT OUTER JOIN으로 구문 변경
   • NL Join이 아닌 다른 방식으로 수행되도록 유도하는 방법.
   • [TB_Sub05] 테이블의 [sub_code] 컬럼 값에 중복 값이 있는 경우라면 전체 건수의 차이가 발생됨으로 주의.