1 개요

2 Oracle vs MS-SQL

3 가용 자원과 한방쿼리

4 한방쿼리 vs 커서

5 결론

'한방쿼리'라는 것을 들어 봤는가? 아마도 DB밥 좀 먹었다는 사람은 다 들어봤을 것이다. 아마도 이 말은 '엔xx 컨설팅'에서 나온 말일 테다. 그 분들은 의도는 분명 집합 기반 솔루션이 행 기반 솔루션(커서)보다 대부분 유리한데서 기인한 것일테다. 중요한 것은 '대부분 유리하지 모든 경우에 유리한 것은 아니다'라는 말을 그 분들은 하지 않았다[1]는 것이다. 또한 DBMS마다 같은 결과라도 성능부분에서는 다를 수 있다. 서버의 가용 자원의 상황도 있으므로 어떤 솔루션이 유리한지는 100% 정확하게 말 할 수는 없다.

Oracle Server는 워낙 함수가 좋아서 정말 거의 대부분은 집합 기반의 솔루션이 유리하다. 하지만 MS-SQL Server의 경우 아직 Oracle Server 보다 함수의 지원이 미흡하다. 그렇기 때문에 MS-SQL Server에서는 생각보다 많은 경우 행 기반 솔루션이 유리할 경우가 많이 있다. 또한 서버의 하드웨어 자원이 허접할 경우 대용량 처리를 위해서는 행 기반 솔루션과 비슷하게 나누어 처리를 하는 것이 더 유리한 경우가 있다.

행 기반 처리를 했다하여 다짜고짜 허접하다 욕하지 마라. 따져보고나서 확연한 차이를 드러내면 그때서야 '이거 이렇게 바꾸는 것에 대해 어떻게 생각하세요?'라고 물어라.

필자의 기억으로는 Oracle 8.1.6부터 분석용 함수를 쓸 수 있다. 안 그래도 강력한 함수를 많이 지원해서 Query 짜기가 수월했는데 분석용 함수의 출현으로 개발이 너무나도 쉬워졌다. 그래서 Oracle에서 누적을 구하는 경우는 쉽고 빠르게 구할 수 있게 되었다. 하지만 MS-SQL에는 그러한 함수가 존재하지 않아서 SELF JOIN을 해야 한다. 조인 연산자는 <, >, <=, <= 중에 하나일 것이다. 그러므로 Merge Join 또는 Hash Join을 할 수 없다. 오로지 Loop Join만(SQL Server 2005부터는 꼭 '=' 이 아니더라도 Hash Join을 한 것을 많이 보았다. 아마도 내부적으로 '='로 바꿀 수 있는 것은 바꾸어 Hash Join을 하나보다. 확인된 바는 없다.) 것은 을 해야 하는데, 누적을 구하는 범위가 늘어날수록 쿼리 처리비용이 X² 그래프로 늘어나는 것을 볼 수 있다. 예를 들어 다음과 같은 누적을 구하는 쿼리는 데이터가 많으면 많을수록 성능이 급격히 나빠진다.

with temp
as
(
	select 1 num union all
	select 2 union all
	select 3
)
select 
	b.num
,	sum(a.num) 
from temp a
	inner join temp b
		on a.num <= b.num
group by
	b.num

그래서 이런 경우는 커서를 사용한 솔루션을 추천하고 있다. 자세한 내용은 Inside Microsoft SQL Server 2005 T-SQL PROGARMMING의 3장 커서부분을 참고하기 바란다.

한방쿼리는 충분한 가용 자원이 있을 때나 할 수 있는 소리다. 예를 들어, 2008년 월별 매출액과 신규가입고객수 등과 같은 집계 쿼리를 작성한다고 가정해 보자. 이 쿼리가 필요한 메모리는 1GB다. 하지만 서버에서 내어 줄 수 있는 메모리는 500MB다. 그러면 DBMS는 어떻게 해야 하는가? 그리고 OS는? 메모리가 부족하다고 요청된 쿼리를 내칠 수는 없는 노릇이다. 그래서 페이징이 일어나고 디스크와 메모리를 왔다 갔다하면서 쿼리 성능은 나빠지고 또한 필요한 데이터를 디스크에서 메모리로 퍼 올리기 위해서 디스크를 사용하게 되어 디스크의 병목도 발생할 소지가 있다. (물론 페이징이 일어나는 디스크와 데이터베이스 시스템의 데이터틑 따로 분리되어 있는 것이 기본이다.) 어쨌든 한방쿼리도 충분한 가용자원이 있을 때나 충분한 성능을 낼 수 있다. 꼭 한방쿼리가 좋은 것만은 아니다. 가용한 자원상황에 맞는 단위의 한방쿼리가 진정한 한방쿼리다.

한방쿼리

DECLARE 
    @DateUnit char(1) --D, W, M
,    @CurrDT char(8)
,    @BeginDT char(8)
,    @EndDT char(8);

SET @DateUnit = 'D';
SET @CurrDT = '20091101';

DECLARE 
    @BeginDT char(8)
,    @EndDT char(8);

SET @BeginDT = 
        CASE
            WHEN @DateUnit = 'D' THEN @CurrDT 
            WHEN @DateUnit = 'W' THEN 
                CONVERT(int, CONVERT(char(8), DATEADD(dd, -(DATEPART(weekday, @CurrDT) - 1) , @CurrDT), 112))
            WHEN @DateUnit = 'M' THEN CONVERT(int, CONVERT(char(6), @CurrDT, 112) + '01')
        END    

SET @EndDT = 
        CASE
            WHEN @DateUnit = 'D' THEN @CurrDT 
            WHEN @DateUnit = 'W' THEN 
                CONVERT(int, CONVERT(char(8), DATEADD(dd, 7- DATEPART(weekday, @CurrDT), @CurrDT), 112)) 
            WHEN @DateUnit = 'M' THEN 
                CONVERT(int, CONVERT(char(8), DATEADD(mm, 1, CONVERT(char(6), @CurrDT, 112) + '01') - 1, 112))
        END;
--SELECT @BeginDT, @EndDT;
INSERT Common.Agg_ReiterationUU_ConnectHistory
    (DateKey, BasisServiceKey, ReiterationServiceKey, UU, AggUnit)
SELECT
    @BeginDT DateKey
,    B.BasisServiceKey
,    B.ReiterationServiceKey
,    B.UU
,    @DateUnit AggUnit
FROM Common.Dim_Service OutTable
    CROSS APPLY (
        SELECT
            A.ServiceKey BasisServiceKey
        ,    B.ServiceKey ReiterationServiceKey
        ,    COUNT(DISTINCT B.AccountKey) UU
        FROM 
            (
                SELECT
                    AccountKey
                ,    OutTable.Service_CD ServiceKey
                FROM Common.Fact_ConnectHistory
                WHERE DateKey BETWEEN @BeginDT AND @EndDT
                AND ServiceKey = OutTable.Service_CD
                GROUP BY
                    AccountKey
            ) A
            INNER JOIN Common.Fact_ConnectHistory B
                ON A.AccountKey = B.AccountKey
            WHERE B.DateKey BETWEEN @BeginDT AND @EndDT
        GROUP BY
            A.ServiceKey 
        ,    B.ServiceKey 
    ) B
WHERE Service_CD > 0
AND Site_CD = 1
AND @DateUnit IN ('D', 'W', 'M')

커서

/*
DECLARE 
    @DateUnit char(1) --D, W, M
,    @CurrDT char(8)
,    @BeginDT char(8)
,    @EndDT char(8);

SET @DateUnit = 'D';
SET @CurrDT = '20091101';
*/
DECLARE 
    @BeginDT char(8)
,    @EndDT char(8);

SET @BeginDT = 
        CASE
            WHEN @DateUnit = 'D' THEN @CurrDT 
            WHEN @DateUnit = 'W' THEN 
                CONVERT(int, CONVERT(char(8), DATEADD(dd, -(DATEPART(weekday, @CurrDT) - 1) , @CurrDT), 112))
            WHEN @DateUnit = 'M' THEN CONVERT(int, CONVERT(char(6), @CurrDT, 112) + '01')
        END    

SET @EndDT = 
        CASE
            WHEN @DateUnit = 'D' THEN @CurrDT 
            WHEN @DateUnit = 'W' THEN 
                CONVERT(int, CONVERT(char(8), DATEADD(dd, 7- DATEPART(weekday, @CurrDT), @CurrDT), 112)) 
            WHEN @DateUnit = 'M' THEN 
                CONVERT(int, CONVERT(char(8), DATEADD(mm, 1, CONVERT(char(6), @CurrDT, 112) + '01') - 1, 112))
        END;
--SELECT @BeginDT, @EndDT;

DECLARE 
    @ServiceKey int;

DECLARE cur CURSOR FOR
    SELECT Service_CD ServiceKey
    FROM Common.Dim_Service
    WHERE Service_CD > 0
    AND Site_CD = 1
    AND @DateUnit IN ('D', 'W', 'M');

CREATE TABLE #AccountKey(AccountKey bigint);

OPEN Cur;

FETCH NEXT FROM cur INTO @ServiceKey;
WHILE @@FETCH_STATUS not in (-1, -2)
BEGIN
    INSERT #AccountKey
    SELECT
        AccountKey
    FROM Common.Fact_ConnectHistory
    WHERE DateKey BETWEEN @BeginDT AND @EndDT
    AND ServiceKey = @ServiceKey
    GROUP BY
        AccountKey;

    INSERT Common.Agg_ReiterationUU_ConnectHistory
        (DateKey, BasisServiceKey, ReiterationServiceKey, UU, AggUnit)
    SELECT
        @BeginDT DateKey
    ,    @ServiceKey BasisServiceKey
    ,    A.ServiceKey ReiterationServiceKey
    ,    COUNT(DISTINCT A.AccountKey) UU
    ,    @DateUnit AggUnit
    FROM Common.Fact_ConnectHistory A
        INNER JOIN #AccountKey B
            ON A.AccountKey = B.AccountKey
    WHERE A.DateKey BETWEEN @BeginDT AND @EndDT
    GROUP BY 
        A.ServiceKey
    
    TRUNCATE TABLE #AccountKey
    FETCH NEXT FROM cur INTO @ServiceKey
END

CLOSE cur;
DEALLOCATE cur;

이 경우 커서를 이용하여 분리 처리한 것에 비해 한방쿼리가 훨씬 느렸다. 가용한 메모리가 한정적이라 무진장 페이징이 일어났을 것이다. 다음은 일단위로 처리했을 때와 주단위로 처리했을 때의 차이이다.

처리량	처리방식	처리시간
1일	한방쿼리	120초
1일	커서	40초
7일	한방쿼리	1200초
7일	커서	330초

* 참고: 8cpu, 8GB ram

처리범위가 넓어질수록 한방쿼리의 처리시간은 기하급수적으로 늘어날 것이다. 자원을 병목없게 사용할 수 있는 단위의 한방쿼리를 짜는 것이 성능에 중요하다 하겠다.

마냥 좋은 것만은 없다. 벽돌을 1층에서 5층으로 100장을 나르는 것을 생각해보자. 그리고 강호동과 은지원이 있다치자. 강호동은 100장을 1회에 5층까지 나를 수 있지만, 은지원은 3번에 걸쳐서 나를 수 밖에 없는 하드웨어를 가진 것의 차이다. 한방 쿼리는 다음의 사항을 고려해야 한다.

• 가용자원(하드웨어)
  
• 유지보수(관리비용)
  
  

한방 쿼리는 가용한 자원이 한방 쿼리를 지원할 수 있을만큼 충분해야만 좋은 솔루션이 될 수 있다. 또한 복잡해서 유지보수가 안 된다면 그것 또한 좋은 솔루션이 아니다.

오래된 글이지만 정말 도움이 되었습니다. 마지막 글귀가 와 닿습니다. -- achiara 2017-05-05 18:23:38