'한방쿼리'라는 것을 들어 봤는가? 아마도 DB밥 좀 먹었다는 사람은 다 들어봤을 것이다. 아마도 이 말은 '엔xx 컨설팅'에서 나온 말일 테다. 그 분들은 의도는 분명 집합 기반 솔루션이 행 기반 솔루션(커서)보다 대부분 유리한데서 기인한 것일테다. 중요한 것은 '대부분 유리하지 모든 경우에 유리한 것은 아니다'라는 말을 그 분들은 하지 않았다는 것이다. 또한 DBMS마다 같은 결과라도 성능부분에서는 다를 수 있다. 서버의 가용 자원의 상황도 있으므로 어떤 솔루션이 유리한지는 100% 정확하게 말 할 수는 없다.
Oracle Server는 워낙 함수가 좋아서 정말 거의 대부분은 집합 기반의 솔루션이 유리하다. 하지만 MS-SQL Server의 경우 아직 Oracle Server 보다 함수의 지원이 미흡하다. 그렇기 때문에 MS-SQL Server에서는 생각보다 많은 경우 행 기반 솔루션이 유리할 경우가 많이 있다. 또한 서버의 하드웨어 자원이 허접할 경우 대용량 처리를 위해서는 행 기반 솔루션과 비슷하게 나누어 처리를 하는 것이 더 유리한 경우가 있다.
행 기반 처리를 했다하여 다짜고짜 허접하다 욕하지 마라. 따져보고나서 확연한 차이를 드러내면 그때서야 '이거 이렇게 바꾸는 것에 대해 어떻게 생각하세요?'라고 물어라.
2 Oracle vs MS-SQL #
필자의 기억으로는 Oracle 8.1.6부터 분석용 함수를 쓸 수 있다. 안 그래도 강력한 함수를 많이 지원해서 Query 짜기가 수월했는데 분석용 함수의 출현으로 개발이 너무나도 쉬워졌다. 그래서
![[http]](/moniwiki/imgs/http.png)
Oracle에서 누적(http://databaser.net/moniwiki/wiki.php/%EB%B6%84%EC%84%9D%EC%9A%A9%ED%95%A8%EC%88%98%EC%9D%98%EC%9D%B4%EC%9A%A9#s-3)을 구하는 경우는 쉽고 빠르게 구할 수 있게 되었다. 하지만 MS-SQL에는 그러한 함수가 존재하지 않아서 SELF JOIN을 해야 한다. 조인 연산자는 <, >, <=, <= 중에 하나일 것이다. 그러므로 Merge Join 또는 Hash Join을 할 수 없다. 오로지 Loop Join만(SQL Server 2005부터는 꼭 '=' 이 아니더라도 Hash Join을 한 것을 많이 보았다. 아마도 내부적으로 '='로 바꿀 수 있는 것은 바꾸어 Hash Join을 하나보다. 확인된 바는 없다.) 것은 을 해야 하는데, 누적을 구하는 범위가 늘어날수록 쿼리 처리비용이 X
2 그래프로 늘어나는 것을 볼 수 있다. 예를 들어 다음과 같은 누적을 구하는 쿼리는 데이터가 많으면 많을수록 성능이 급격히 나빠진다.
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
4 한방쿼리 vs 커서 #
한방쿼리
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
![[-]](/moniwiki/imgs/plugin/arrup.png "[-]")
![[+]](/moniwiki/imgs/plugin/arrdown.png "[+]")