SQL Query–커서 대체하기

본 글은 2006년 12월 12일 본인의 네이버 블로그 (http://blog.naver.com/ezmis)에 포스팅 한 글을 본 블로그로 가져와 일부 수정한 것임을 밝힌다. 원문 주소는 다음과 같다.

http://blog.naver.com/ezmis/130012110941

 

레코드를 한 행씩 처리하는 방법으로 커서를 사용하는 경우가 많다. 그러나 커서를 이용할 경우 다량의 데이터 처리에 문제가 있다.

가령 데이터 수가 일정 이하 일 때, 초당 100건씩 처리했다고 해서 1만 건을 처리하는데 10초가 걸리는 것이 아니다.

커서의 실행 예상 시간은 아래 그래프와 같다 할 수 있다.

 

 

커서 사용은 손쉬운 선택이나 동시에 고통(?)의 길로 밀어 넣는 작업이기도 하다.

 

아래는 Microsoft에서 제공한 MS SQL Server 2000 개발자를 위한 튜닝 가이드에서 발췌한 내용이다.

"커서는 내부적으로 임시 테이블을 사용하기 때문에 임시 테이블을 쓴다고 부하가 더 발생하진 않습니다. 오히려 커서의 부가적 기능 때문에 서버 자원을 더 낭비하게 됩니다. (커서로 할 수 있는 건, 임시 테이블이나 테이블 변수로도 모두 처리가 가능합니다.)"

(임시 테이블과 테이블 변수 간의 수행 속도에 관해 논란의 여지가 있다. 2003년도 12월자 Windows & .NET Magazine에 의하면, 데이터의 수가 커질 수록 임시 테이블의 수행 속도가 테이블 변수 보다 더 빨라진다고 한다. 이는 기존의 MS사의 입장과는 상반된 것이다.)

위 가이드에 따라서 실제 업무에서 커서 대신 테이블 변수를 이용한 배치 작업의 예이다.

-- ORACLE로부터 직위 정보를 읽어들여 local server의 직위 정보에 반영한다.

-- ACTION_TYPE 필드 플래그가 R이면 등록, M이면 수정, D이면 삭제

SET NOCOUNT ON

DECLARE @tmpPosition TABLE
(
     nid  int identity(1, 1) not null,
     idx  int,
     csName  varchar(50),
     idx_ClassSystem int,
     iSeq  int,
     iState  int,
     act_type  varchar(1)
)

-- 커서문에서 for select 에 해당

INSERT INTO @tmpPosition(idx, csName, idx_ClassSystem, iSeq, iState, act_type)
(
          SELECT
                    CAST(POSITION_ID AS INT),
                    RTRIM(SUBSTRING(POSITION_NAME, 1, 50)),
                    (SELECT idx FROM mc_ClassSystem
                              WHERE idx_Uplevel = 0),
                    CAST(POSITION_ORDER AS INT),
                    1, ACTION_TYPE
          FROM OPENQUERY
          (ORA10G,
          'SELECT
                    POSITION_ID, POSITION_NAME, POSITION_ORDER,
                    ACTION_TYPE  
          FROM
                    ORGDBADM.TKMID_POSITIONINFORMATION
          WHERE IS_CHECK_CON = ''N''
          ORDER BY MODIFY_DATE ASC'
     )
)

DECLARE @i int, @maxno int, @idx int, @csName varchar(50),
     @idx_ClassSystem int, @iSeq int, @iState int, @act varchar(1)

-- fetch into에 해당

SELECT @i = 1, @maxno = max(nid) FROM @tmpPosition

WHILE @i <= @maxno
BEGIN
          SELECT @idx = idx, @csName = csName,
                    @idx_ClassSystem = idx_ClassSystem, @iSeq = iSeq,
                    @iState = iState, @act = act_type
          FROM 
                    @tmpPosition
          WHERE 
                    nid = @i

          IF @act = 'R'
          BEGIN
                    SET IDENTITY_INSERT mc_ClassPosition ON

                    INSERT INTO mc_ClassPosition(idx, csName,
                              idx_ClassSystem, iSeq, iState)
                    VALUES(@idx, @csName,
                              @idx_ClassSystem, @iSeq, @iState)

                    SET IDENTITY_INSERT mc_ClassPosition OFF
          END
          ELSE IF @act = 'M'
          BEGIN
                    UPDATE mc_ClassPosition
                    SET csName = @csName,
                         idx_ClassSystem = @idx_ClassSystem,
                         iSeq = @iSeq,
                         iState = @iState
                    WHERE 
                         idx = @idx
          END
          ELSE IF @act = 'D'
          BEGIN
                    DELETE mc_ClassPosition WHERE idx = @idx
          END
         
          SET @i = @i + 1
END

-- 기타 후 처리

Microsoft 사에서 나온 MS-SQL Server와 관련된 참고 자료들을 보면 하드디스크 I/O가 일어나는 임시 테이블보다는 테이블 변수가 메모리 상에서 형성되고 처리되기 때문에 더욱 높은 성능을 가질 수 있다라고 명시가 되어있다.

그리고 위 언급한 MS SQL Server 2000 개발자를 위한 튜닝 가이드에서도 아래와 같이 언급하고 있다.

"결론부터 말씀 드리자면, 커서보다는 임시 테이블이 임시 테이블보다는 테이블 변수를 사용하는 것이 성능에 보탬이 됩니다."

그런데 2003년도 12월자 Windows & .NET Magazine에 의하면 테이블 변수 또한 하드디스크 I/O가 일어나는데다가 SQL 서버 엔진 내에서 병렬처리 쿼리 계획을 생성하지 못 한다는, SQL 서버 개발팀의 고위 관계자의 발언이 인용되어있다.

현재 이 부분에 대해서는 MS사로부터 어떠한 공식적인 입장표명이 없다. (거기다가 이미 MS사는 MS SQL 2005 서버로 넘어가버렸다...)

SQL 서버 테이블 변수는 가급적 소규모 쿼리나 데이터 집합을 위한 용도로 사용하고, 대용량 데이터 집합의 처리는 임시 테이블을 사용하는 것이 바람직하다고 SQL 서버 개발팀에서 권고한다고 하니 각자의 판단에 맞겨야 하겠다.

MS SQL Server 2000 개발자를 위한 튜닝 가이드는 MSDN이나 아래 사이트에서 그 내용을 확인 할 수 있다.

http://www.dbguide.net/tuning_guide/tuning_guide_11.jsp