DataBaser.Net: 집합적사고방식의실전응용예

1 집합적 사고방식의 기본
2 실전예제

1 집합적 사고방식의 기본 #

데이터베이스는 하나의 집합이다. SQL은 단지 결과 집합을 정의하는 것이다. 그렇기 때문에 집합적인 처리가 이루어져야 하며, 기존의 3GL 프로그램 방식으로 SQL을 작성한다면, 성능상의 문제로 항상 고민을 하게 된다. SELECT ~ FROM ~ 으로 DBMS에 결과집합을 요청했으면, 그 결과도 하나의 집합이다. 즉, 물리적으로는 하나의 테이블로 취급을 한다는 것이다. 이를 SQL은 초보자에게 매우 힘들다.

이것은 어떠한 절차적인 사고를 가진다면, 상당히 복잡한 로직이 작동해야 한다는 것이다. 여러분이 EMP테이블을 C언어로 구조체를 사용해서 또는 2차원 배열을 사용해서 위의 결과를 출력하는 프로그램을 작성한다면 어떻게 하시겠는가? 생각만해도 머리가 다 아프다. 바로 이러한 점이 절차적인 사고방식에 기인한 처리방식과 집합적인 사고방식에 기인한 처리방식의 차이점이다. 여러분은 지금 이 예가 SQL문에 대해서 설명한 것이 아니라 집합적인 사고 방식을 가지기 위한 예제임을 알아야 한다. 또 다른 예를 보도록 하자.

TEST 테이블이 다음과 같이 구성이 되어 있다. 
Id
--
1
2
3
.
.
.
100
 
1 ~ 100까지의 행이 있고, 해당 행의 값에 모두 1을 더하게 하고 싶다.

절차적 사고방식

USE TEMPDB
GO
 
IF OBJECT_ID('TEST') IS NOT NULL DROP TABLE TEST
IF OBJECT_ID('TEST2') IS NOT NULL DROP TABLE TEST2
GO
 
--1부터 100까지 숫자가 들어간 테이블 생성
SELECT TOP 100 
(SELECT COUNT(*)  
FROM NORTHWIND..ORDERS B WHERE B.ORDERID > A.ORDERID) AS ID
        INTO TEST
FROM NORTHWIND..ORDERS A
ORDER BY 1
GO
 
--여기서부터 커서를 돌리기 위한 시작
--임시 테이블 생성 : TEST2
SELECT * INTO TEST2 FROM TEST
WHERE 1 = 0
 
--커서 정의 
DECLARE cur CURSOR READ_ONLY
FOR SELECT *  FROM TEST
 
DECLARE @id int
 
--커서 오픈
OPEN cur
 
--패치
FETCH NEXT FROM cur INTO @id
WHILE (@@fetch_status <> -1) BEGIN
             IF (@@fetch_status <> -2) BEGIN
                           SET @id = @id + 1
                           INSERT INTO TEST2 VALUES(@id)
             END
FETCH NEXT FROM cur INTO @id
END
 
CLOSE cur
DEALLOCATE cur
 
SELECT * FROM TEST2
GO

소스를 분석해보면 일반적이다. 해당 테이블에서 한 건을 꺼내와 1을 더하고, 임시테이블에 INSERT를 하는 짓을 100번 반복한다. 그런 후 임시테이블을 모두 SELECT하는 결과이다. 즉, 절차적인 사고 방식은 행 단위로 처리하고자 위와 같이 루프를 돌리는 식으로 커서를 사용하였다. 그렇다면 집합적인 사고방식으로 하면 어떨까?

집합적 사고방식

SELECT ID + 1 FROM TEST

끝이다. 같은 결과라도 엄청난 차이를 느낄 수 있음은 분명하다. 절차적인 사고 방식은 행 단위로 처리하려고 했으며, 집합적인 사고 방식은 처리해야 할 집합을 정의한 후 그 집합에 1을 더한 것 뿐이다. 제품의 나름대로 최적화된 알고리즘으로 처리를 한 것이다. 실제 많은 데이터에서는 수행 속도 차이는 엄청나다는 것을 알 수 있을 것이다.

커서를 사용하지 말라고 하는 이유는 행 단위 처리의 비효율성 때문이다. 거의 모든 것은 커서를 사용하지 않아도 된다. 커서를 사용하지 않고도 답이 있기 때문에 커서를 사용하지 말라는 것이다. 어쩔 수 없이 커서를 사용해야 할 경우는 커서를 정의 할 때 최대한 처리해야 할 집합을 줄인다면 수행성능에 대한 걱정은 하지 않아도 된다. 어쩔 수 없을 때는 패턴을 발견 할 수 없는 경우이다.

필자가 말하려는 것은 집합적인 사고 방식이 위대하다라는 것이 아니다. 다만 절차적인 사고와 집합적인(비절차적) 사고를 어떤 곳에 적용해야 하냐는 것이 문제가 되는 것이다. 이러한 것들은 차후 여러분이 담배를 물고, 커피를 마셔가면서 고생했던 경험에 의한 결정이 되는 것이다. 다음 예는 고객의 사이트에서 실제 발생한 예이다.

[edit]

2 실전예제 #

필자는 튜닝을 하기 위해서 프로필러를 이용해서 3초 이상의 Duration을 보이는 SQL문에 대한 모니터링 중이였다. 기다리는 데 갑자기 다음과 같은 쿼리가 떴다.

delete from hrEmpWorkPlan
where SiteCode = N'N100' 
and EmpCode = N'002360' 
and WorkDate between N'20040501' and N'20040510' and wkconfirm='Y'
 
delete from hrEmpWorkPlan
where SiteCode = N'N100' 
and EmpCode = N'004327' 
and WorkDate between N'20040501' and N'20040510'
 
delete from hrEmpWorkPlan
where SiteCode = N'N100' 
and EmpCode = N'005315' 
and WorkDate between N'20040501' and N'20040510'
 
insert into hrEmpWorkPlan 
(SiteCode, EmpCode,  WorkDate, WkScheduleCode, WkConfirm)
select A.SiteCode, N'002360',  A.WorkDate, A.WkScheduleCode, 'N'
from(
 select A.SiteCode, A.EmpCode, C.PaySystemCode, 
A.WorkDate, A.WkScheduleCode, B.TotalWkTime, A.WkConfirm
 from hrEmpWorkPlan A
  left outer join hrWkScheduleHeader B
   on A.SiteCode = B.SiteCode and A.WkScheduleCode = B.WkScheduleCode
  left outer join EmpMaster C
   on A.SiteCode = C.SiteCode and A.EmpCode = C.EmpCode
 where C.ComCode = N'NXN1' 
and A.SiteCode like N'N100%'  
and A.EmpCode like N'013122%' ) A
where A.SiteCode = N'N100' 
and A.EmpCode = N'013122' 
and  A.WorkDate between N'20040501' and N'20040510'
 
insert into hrEmpWorkPlan 
(SiteCode, EmpCode,  WorkDate, WkScheduleCode, WkConfirm)
select A.SiteCode, N'004327',  A.WorkDate, A.WkScheduleCode, 'N'
from(
 select A.SiteCode, A.EmpCode, C.PaySystemCode, 
A.WorkDate, A.WkScheduleCode, B.TotalWkTime, A.WkConfirm
 from hrEmpWorkPlan A
  left outer join hrWkScheduleHeader B
   on A.SiteCode = B.SiteCode and A.WkScheduleCode = B.WkScheduleCode
  left outer join EmpMaster C
   on A.SiteCode = C.SiteCode and A.EmpCode = C.EmpCode
 where C.ComCode = N'NXN1' 
and A.SiteCode like N'N100%'  
and A.EmpCode like N'013122%' ) A
where A.SiteCode = N'N100' 
and A.EmpCode = N'013122' 
and  A.WorkDate between N'20040501' and N'20040510'
 
insert into hrEmpWorkPlan 
(SiteCode, EmpCode,  WorkDate, WkScheduleCode, WkConfirm)
select A.SiteCode, N'005315',  A.WorkDate, A.WkScheduleCode, 'N'
from(
 select A.SiteCode, A.EmpCode, C.PaySystemCode, 
A.WorkDate, A.WkScheduleCode, B.TotalWkTime, A.WkConfirm
 from hrEmpWorkPlan A
  left outer join hrWkScheduleHeader B
   on A.SiteCode = B.SiteCode and A.WkScheduleCode = B.WkScheduleCode
  left outer join EmpMaster C
   on A.SiteCode = C.SiteCode and A.EmpCode = C.EmpCode
 where C.ComCode = N'NXN1' 
and A.SiteCode like N'N100%'  
and A.EmpCode like N'013122%' ) A 
where A.SiteCode = N'N100' 
and A.EmpCode = N'013122' 
and  A.WorkDate between N'20040501' and N'20040510'
go

모니터링을 시작하고 이와 같은 패턴이 자주 눈에 띄었다. 분명히 어플리케이션에서 무슨 일이 벌어지고 있음을 직감하였다. 실제 개발자에게 가서 어떤 것인지 보여달라고 했다. 어플리케이션에서는 SELECT를 한 후 Grid에 뿌려주고, 사용자는 마우스의 클릭 이벤트가 발생하면 체크되어 체크된 것에 대해서 삽입, 삭제, 업데이트 작업들이 이루어지는 패턴이였다. 핵심은 쿼리의 결과집합에서 또 다른 집합을 뽑아내는 것이다. 그러므로 WHERE 조건에 AND로 더 붙여주기만 하면 되는 것이다. 즉, 개발자는 행단위 처리를 하고 있었다. 필자는 다음과 같이 고쳐주었다.

DELETE FROM hrEmpWorkPlan
WHERE SiteCode = N'N100' 
AND WorkDate BETWEEN N'20040501' 
AND N'20040510' AND wkcONfirm='Y'
AND EmpCode in ('005315','004327','002360') --여기 주목
 
INSERT INTO hrEmpWorkPlan (SiteCode, EmpCode,  WorkDate, WkScheduleCode, WkCONfirm)
SELECT 
	A.SiteCode
,	B.EmpCode2
,	A.WorkDate
,	A.WkScheduleCode
,	'N'
FROM (
	SELECT 
		A.SiteCode
	,	A.EmpCode
	,	C.PaySystemCode
	,	A.WorkDate
	,	A.WkScheduleCode
	,	B.TotalWkTime
	,	A.WkCONfirm
    FROM hrEmpWorkPlan A 
		LEFT OUTER JOIN hrWkScheduleHeader B
			ON A.SiteCode = B.SiteCode AND A.WkScheduleCode = B.WkScheduleCode
		LEFT OUTER JOIN EmpMASter C
			ON A.SiteCode = C.SiteCode AND A.EmpCode = C.EmpCode
    WHERE C.ComCode = N'NXN1' 
	AND A.SiteCode LIKE N'N100%'  
	AND A.EmpCode LIKE N'013122%') A INNER JOIN (
				SELECT 
					'013122' AS EmpCode
				,	EmpCode AS EmpCode2 
				FROM EmpMASter 
				WHERE ComCode=N'NXN1' 
				AND EmpCode in ('005315','004327','002360')) B --여기 주목
ON A.EmpCode=B.EmpCode 
WHERE A.SiteCode = N'N100' 
AND A.EmpCode = N'013122' 
AND  A.WorkDate between N'20040501' AND N'20040510'

이것은 1차적인 튜닝이다. 실제로 사용자가 마우스로 체크하는 것에는 무엇인가에 기준에 의해서 체크된 것일 것이다. 그 체크되는 조건만 발견해서 위 SQL문을 고쳐준다면 더 좋아질 것이다. 어플리케이션에서 DB로 연결을 하고, DBMS에 SQL문이나 저장 프로시저를 호출하는 짓은 생각보다 비용이 많이 드는 작업이다. SQL문을 컴파일하고, 실행하는 것만큼 DBMS Call도 비용이 만만치 않다.

Contents

1 집합적 사고방식의 기본 #

2 실전예제 #