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

1. 사전구성

CREATE TABLE TB_Std02 (
  std_no INT NOT NULL
  , class_cd VARCHAR(2) NOT NULL
  , code NVARCHAR(36) NOT NULL
  , CONSTRAINT PK_Std02 PRIMARY KEY NONCLUSTERED (std_no)
);
CREATE TABLE TB_Point02 (
  std_no INT NOT NULL
  , sbjt_cd VARCHAR(2) NOT NULL
  , point INT NOT NULL
);

INSERT INTO TB_Std02 
SELECT std_no 
  , CASE WHEN std_no <= 200 THEN '1'
    WHEN std_no <= 500 THEN '2'
    WHEN std_no  <= 650 THEN '3'
    ELSE '4' END class_cd
  , CONVERT(NVARCHAR(36), NEWID()) code
FROM (
  SELECT ROW_NUMBER() OVER(ORDER BY a.number) std_no
    FROM master..spt_values a
    WHERE a.type = 'P' AND a.number BETWEEN 1 AND 1000
) b;

INSERT INTO TB_Point02
SELECT a.std_no
  , CHAR(b.number+64) sbjt_cd
  , CASE WHEN a.std_no = 1 THEN 100
    ELSE CONVERT(INT, RIGHT(std_no * ASCII(CONVERT(NVARCHAR(36), NEWID() ) ), 2))
    END AS point
FROM TB_Std02 a, master..spt_values b
WHERE b.type = 'P' AND b.number BETWEEN 1 AND 4;

 

table_name	index_name	index_description	Is_Unique	indexed_columns	included_columns
TB_Std02	PK_Std02	nonclustered, unique, primary key	1	std_no	[NULL]

 

2. AS-IS

SELECT s.std_no, s.class_cd 
  , ISNULL(AVG(g.point), 0) avg_point
  , COUNT(1) OVER(PARTITION BY class_cd) total_cnt
FROM TB_Std02 s
LEFT JOIN TB_Point02 g 
  ON s.std_no = g.std_no 
GROUP BY s.std_no, s.class_cd 
ORDER BY s.class_cd, avg_point DESC, s.std_no;

• [std_no], [class_cd] 컬럼을 기준하여 [point] 컬럼의 평균값을 구한다.
• OVER (PARTITION BY class_cd) 절에 COUNT 함수를 적용하여 [class_cd] 그룹별로 집계한 행 수를 동시에 출력하는 구문.
• [class_cd] 컬럼을 그룹별로 집계하는 과정에서 Spool이 사용되는데, 이는 두 테이블을 조인하여 평균값이 구해지는 테이터를 임시 테이블에 저장하고 다시 읽어내면서 그룹별로 행을 집계하기 위해 사용된다.
• 이렇게 Spool은 재사용이 필요한 데이터를 저장하고 읽어내기 위해 사용되는데, 임시 테이블을 이용하기 때문에 Worktable에 대한 I/O 비용이 발생한다.

 

3. TO_BE

SELECT a.std_no, a.class_cd, a.avg_point, b.total_cnt
FROM (
  SELECT s.std_no, s.class_cd 
    , ISNULL(AVG(g.point), 0) avg_point
  FROM TB_Std02 s
  LEFT JOIN TB_Point02 g 
    ON s.std_no = g.std_no 
  GROUP BY s.std_no, s.class_cd 
) a
INNER JOIN (
  SELECT class_cd, COUNT(1) total_cnt
  FROM TB_Std02
  GROUP BY class_cd
) b
ON a.class_cd = b.class_cd
ORDER BY a.class_cd, avg_point DESC, a.std_no

• [TB_Std02] 테이블의 [class_cd] 컬럼을 기준하여 그룹별 행 수를 구하고, 평균 데이터를 구하는 구문에 조인한다.
• 재사용에 필요한 데이터를 테이블로 읽어 내기 때문에 Spool에 의한 수행 과정이 제거된다.