COUNT 함수의 OVER 절 성능 이슈 - 1

1. 사전구성

SELECT NEWID() AS code
  , CAST(rownum%12 AS INT) AS value
INTO TB_OVER
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
;
CREATE CLUSTERED INDEX CIDX_OVER ON TB_OVER (code);
CREATE INDEX NIDX_OVER ON TB_OVER (value);

 

table_name	index_name	index_description	Is_Unique	indexed_columns	included_columns
TB_OVER	CIDX_OVER	clustered	0	code	[NULL]
TB_OVER	NIDX_OVER	nonclustered	0	value	[NULL]

 

2. AS-IS

SELECT *, COUNT(*) OVER() AS 'totalcount'
FROM TB_OVER A
WHERE A.value = 11;

• 결과 집합 모든 행에 전체 행 수를 조인하기 위해 Spool이 사용된다.
• 이는 조건절에 해당하는 결과 집합을 임시 데이터로 저장하여 집합에 대한 전체 행수를 집계할 때 임시 데이터를 재사용하기 위함이다.
• Spool은 내부적으로 임시 테이블을 만들어 저장한다.
  따라서 Spool을 생성하고 재사용하는 과정에서 임시 테이블에 대한 I/O 비용이 높게 발생된다.

 

3. TO_BE

SELECT A.*, totalcount
FROM TB_OVER A
CROSS JOIN (
  SELECT COUNT(1) AS 'totalcount'
  FROM TB_OVER
  WHERE value = 11) B
WHERE A.value = 11;

• 임시 테이블에 중간 결과 집합을 저장하고 재사용하는 과정에서 I/O가 높게 사용되었다.
• 내부적으로 Spool을 생성하지 않도록 전체 행 수를 구하는 구문을 별도로 작성하고,
  CROSS JOIN을 통해 결과 집합 모든 행에 전체 행수가 조인되도록 구문을 변경한다.