여러분도 아시다시피 동일본 대지진으로 인해 상황이 좋지 못한 관계로, 한동안 기사를 포스팅하지 못했네요.
그동안 관심을 가지고 기사를 읽어주신 동네 주민들께 너무 죄송합니다.
사태가 어떻게 지속될지 알 수는 없지만, 짬짬이 함께 공유했으면 하는 기사가 있으면 포스팅하도록 하겠습니다.
각설하고 본론으로 들어가도록 하겠습니다.
이해를 돕기 위해 먼저, Runners라는 테이블을 작성해 보자.
Runners 테이블은 각 주자(Runner) 별 기록(Time)과 주자의 연령(Age)을 저장하고 있다.
CREATE TABLE #Runners
(
Runner integer NOT NULL
, Time integer NOT NULL
, Age integer NOT NULL
)
INSERT INTO #Runners
SELECT 1 , 10 , 20 UNION ALL
SELECT 2 , 15 , 20 UNION ALL
SELECT 3 , 11 , 20 UNION ALL
SELECT 4 , 12 , 30 UNION ALL
SELECT 5 , 18 , 30 UNION ALL
SELECT 6 , 9 , 40 UNION ALL
SELECT 7 , 16 , 40 UNION ALL
SELECT 8 , 13 , 30
여러분이라면 어떻게 T-SQL을 작성할 것인가?
가장 보편적으로 알려진 방법은 Ranking 함수(특히 row_number())와 CTE(Common table Expression) 사용하는 방법이다.
ROW_NUMBER() 함수와 CTE(Common table Expression) 를 이용한 방법
먼저, 코스를 완주한 주자를 기록 시간 순으로 정렬시켜 보자. 연령별로 그룹핑을 할 필요가 없다면 문제는 아주 간단하다.
SELECT *
FROM #Runners
ORDER BY Time
Runner Time Age
------ ----------- -----------
6 9 40
1 10 20
3 11 20
4 12 30
8 13 30
2 15 20
7 16 40
5 18 30
SELECT TOP(2) *
FROM #Runners
ORDER BY Time
Runner Time Age
------ ----------- -----------
6 9 40
1 10 20
SELECT *, ROW_NUMBER() OVER(ORDER BY Time) AS RowN
FROM #Runners
Runner Time Age RowN
------ ----------- ----------- --------------------
6 9 40 1
1 10 20 2
3 11 20 3
4 12 30 4
8 13 30 5
2 15 20 6
7 16 40 7
5 18 30 8
이제 OVER 구에 “PARTITION_BY” 를 확장해 보자.
“PARTITION_BY” 구는 “PARTITION_BY” 구에 지정한 컬럼 별로 그룹 함수를 적용하라는 것을 의미한다.
따라서, 아래의 질의는 RowN 컬럼의 행 번호를 Age 별로 카운트한다.
SELECT *, ROW_NUMBER() OVER(PARTITION BY Age ORDER BY Time) AS RowN
FROM #Runners
ORDER BY Age,RowN
Runner Time Age RowN
------ ----------- ----------- --------------------
1 10 20 1
3 11 20 2
2 15 20 3
4 12 30 1
8 13 30 2
5 18 30 3
6 9 40 1
7 16 40 2
이 질의로 연령별로 기록이 좋은 주자 순으로 정렬할 수 있게 되었다.
이제,TOP N을 필터링하는 일만 남았다.
여러분은 TOP N을 필터링하기 위해 아래의 SQL을 생각할 수 있을 것이다.
SELECT *, ROW_NUMBER() OVER(PARTITION BY Age ORDER BY Time) AS RowN
FROM #Runners
WHERE ROW_NUMBER() OVER(PARTITION BY Age ORDER BY Time) <= 2
ORDER BY Age,RowN
ㅜㅜ… 실행이 안된다...
당연히 구문 에러가 발생할 것이다.
어떻게 해야지?
이 문제를 해결하기 위해 일반적으로 CTE(Common Table Expresssion) 를 사용한다.
WITH cteRunners
AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY Age ORDER BY Time) AS RowN
FROM #Runners )
SELECT *
FROM cteRunners
WHERE RowN <=2
ORDER BY Age, Rown
Runner Time Age RowN
------ ----------- ----------- --------------------
1 10 20 1
3 11 20 2
4 12 30 1
8 13 30 2
6 9 40 1
7 16 40 2
이제 우리가 원하는 소기의 목적을 달성하게 되었다.
하지만 이대로 만족하는가?!
그룹 함수는 그 특성상 그룹 함수의 대상이 되는 집합이 커지면 커질수록 CPU, Memory 등의 리소스 사용량이 늘어나고 응답 속도가 늦어진다.
그러면 어떻게 하면 좀 더 최적화된 질의를 할 수 있을까?
CROSS APPLY 연산자를 이용한 집합 축소
문제는 이러하다.
우리가 최종적으로 가지고 와야 할 집합은 그룹별 상위 N개 이지만, 상위 N개를 도출하기 위해서는 전체 집합을 읽어야 한다는 것이다.
그렇다! 최적화의 키는 읽어야 할 집합을 축소 하는 것이다. 모든 질의가 그러하듯이...
집합을 축소해 보자!!!
우리는 집합을 축소하기 위해 CROSS APPLY 연산자를 사용할 것이다.
우선 아래의 스크립트를 실행해서 예제 테이블 #RunnersBig을 만들자.
이 테이블에 100만 건의 데이터을 입력하였고, age + time 순서로 된 인덱스를 작성하였다.
IF OBJECT_ID('tempdb..#RunnersBig') IS NOT NULL
DROP TABLE #RunnersBig
GO
CREATE TABLE #RunnersBig
(
RunnerId INTEGER IDENTITY
, Time INTEGER NOT NULL
, Age INTEGER NOT NULL
)
GO
INSERT INTO #runnersbig ( Time , Age )
SELECT TOP 1000000
ABS (CHECKSUM(NEWID()))% 1000
, ABS (CHECKSUM(NEWID()))% 99
FROM sys.columns a
CROSS JOIN sys . columns b
CROSS JOIN sys . columns c
GO
CREATE INDEX idxrunnersbig
ON #runnersbig ( age , time ) INCLUDE ( runnerid )
자! 이제 획기적인 성능 향상을 경험할 차례이다. 아래의 두 질의를 비교해서 실행해 보자.
위의 SQL은 ROW_NUMBER() 함수와 CTE를 이용하여 그룹별 상위 2개의 레코드를 필터링하고 있고, 아래의 SQL은 CROSS APPLY 연산자를 이용하여 읽어야 할 집합을 축소하였다.
1. ROW_NUMBER() 함수와 CTE를 이용하여 그룹별 상위 2개의 레코드를 필터링한 SQL
WITH cteRunners
AS ( SELECT * , ROW_NUMBER() OVER(PARTITION BY Age ORDER BY Time ) AS RowN
FROM #RunnersBig )
SELECT *
FROM cteRunners
WHERE RowN <= 2
ORDER BY Age , Rown
GO
2. CROSS APPLY를 이용한 SQL
WITH cteN
AS ( SELECT number
FROM master .. spt_values
WHERE type = 'p'
AND number between 0 and 100 )
SELECT *
FROM cteN
CROSS APPLY ( SELECT TOP(2) *
FROM #RunnersBig
WHERE #RunnersBig . Age = cteN . number
ORDER BY Time ) AS runners
ORDER by cteN.number , runners.Time
주인장의 머신에서 테스트한 결과 ROW_NUMBER() 함수와 CTE를 이용하여 그룹별 상위 2개의 레코드를 필터링한 SQL은 평균 463ms, CROSS APPLY를 이용한 SQL은 1ms 안팎을 기록하였다.
어떻게 이렇게 놀라운 성능 향상이 있을 수 있었는지는 실행 계획을 비교해 확인해 보기 바란다.
물론, 이 SQL을 실전에서 적절하게 사용하기 위해서는 CTE내의 master..spt_value에 해당하는 부모 자식 관계를 정확하게 데이터 모델링해야 하며, 적절한 인덱스 설계가 필요하다.
'연구개발 > SQL2005' 카테고리의 다른 글
| SQL2005 저장프로시져 모니터하기 (0) | 2011.07.20 |
|---|---|
| COUNT Vs EXISTS (0) | 2011.07.18 |
| SQL Server 2008 설치시 Visual studio tools for applications 2.0 관련 에러를 해결하는 방법 (0) | 2011.07.18 |
| SSMS에서의 System.Security.Cryptography.CryptographicException 에러 (0) | 2011.07.18 |
| 인덱스 정보를 조회하는 뷰 New (0) | 2011.07.18 |