데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

SQL Server 에러 핸들링 비용 비교

  • TRY / CATCH, 제약조건 선행 검사

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server 2005부터 TRY / CATCH 기능이 제공되어 에러 핸들링에 많이 사용 되고 있다. 이는 확실히 프로그래밍을 더 쉽게 하고 에러를 처리하는데 많은 도움이 되었다.

 

데이터 입력 시 TRY / CATCH를 사용하여 에러를 핸들링하는 경우와 체크 조건을 미리 확인하여 수동으로 에러를 핸들링 하는 것에 대한 비용 차이를 확인해 보자.

 

다음 실습을 통하여 TRY / CATCH 예외 처리에 발생하는 비용에 대해서 알아 보자. 실습을 위해 기본 키가 테이블에 고유한 값의 100,000행을 입력 한다.

CREATE TABLE dbo.Numbers (

n INT PRIMARY KEY

)

 

INSERT dbo.Numbers(n)

SELECT TOP (100000) rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])

FROM sys.all_objects AS s1

    CROSS JOIN sys.objects AS s2;

 

 

 

다음 스크립트는 데이터를 입력하는 프로시저이다. 두 프로시저의 차이점은 데이터 입력 전에 제약조건을 검사하는 부분의 유무이다.

CREATE PROCEDURE dbo.InsertNumber_JustInsert

@Number INT

 

AS

 

SET NOCOUNT ON;

 

BEGIN

    BEGIN TRY

        BEGIN TRANSACTION;

        INSERT dbo.Numbers(n) SELECT @Number;

        COMMIT TRANSACTION;

    END TRY

    BEGIN CATCH

        -- error handling goes here

        ROLLBACK TRANSACTION;

    END CATCH

END

GO

 

CREATE PROCEDURE dbo.InsertNumber_CheckFirst

@Number INT

AS

 

SET NOCOUNT ON;

 

BEGIN

    IF NOT EXISTS (SELECT 1 FROM dbo.Numbers WHERE n = @Number)

    BEGIN

        BEGIN TRY

            BEGIN TRANSACTION;

                INSERT dbo.Numbers(n) SELECT @Number;

            COMMIT TRANSACTION;

        END TRY

        BEGIN CATCH

            -- error handling goes here

            ROLLBACK TRANSACTION;

        END CATCH

    END

END

GO

 

 

테스트 시나리오는 다음과 같다.

  1. 모든 삽입 성공
  2. 삽입의 66% 성공
  3. 삽입의 33% 성공
  4. 모든 삽입 실패

 

이 테스트를 수행 하기 위해 추적 로그 테이블을 생성한다. 로그 테이블 생성 스크립트는 다음과 같다.

CREATE TABLE dbo.InsertLog (

Test VARCHAR(32),

Step TINYINT,

UniqueRows INT,

dt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

);

GO

 

다음 프로시저 생성 스크립트는 커서를 사용하여 데이터를 입력하는 프로시저이다. 이때 제약 조건의 유무를 선행하는 프로시저를 구분하기 위해 @Test 변수를 사용하여 JustInsert 와 CheckFirst를 구분하여 호출 할 수 있도록 하였다.

CREATE PROCEDURE dbo.InsertNumber_Wrapper

@Test VARCHAR(32),

@UniqueRows INT

 

AS

 

SET NOCOUNT ON;

 

BEGIN

    -- always

    DELETE dbo.Numbers WHERE n > 100000;

    

    -- record a log entry for step 1 (start)

    INSERT dbo.InsertLog(Test, Step, UniqueRows)

    SELECT @Test, 1, @UniqueRows;

    

    DECLARE @n INT;

    

    DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR

    SELECT n FROM

    (

        -- to generate successes; unique values come from > 100000

        SELECT TOP (@UniqueRows) n = n + 100000 FROM dbo.Numbers

        ORDER BY n

    ) AS x(n)

      

     UNION ALL

      

     SELECT n FROM

     (

        -- to generate failures; duplicate values come from <= 100000

        SELECT TOP (100000 - @UniqueRows) n FROM dbo.Numbers

        ORDER BY n

    ) AS y(n)

    ORDER BY n;

    

    OPEN c;

    

    FETCH NEXT FROM c INTO @n;

    

    -- trudge through cursor

    WHILE (@@FETCH_STATUS <> -1)

    BEGIN

        IF @Test = 'JustInsert'

            EXEC dbo.InsertNumber_JustInsert @Number = @n;

        

        IF @Test = 'CheckFirst'

            EXEC dbo.InsertNumber_CheckFirst @Number = @n;

            

        FETCH NEXT FROM c INTO @n;

END

 

CLOSE c;

DEALLOCATE c;

 

-- record a log entry for step 2 (end)

 

INSERT dbo.InsertLog(Test, Step, UniqueRows)

SELECT @Test, 2, @UniqueRows;

END

GO

 

 

다음 스크립트는 위에서 설명한 시나리오의 삽입 테스트 이다. 안정적인 평균값을 구하기 위해 3번씩 실행 하였다.

EXEC dbo.InsertNumber_Wrapper @Test = 'CheckFirst', @UniqueRows = 100000;

EXEC dbo.InsertNumber_Wrapper @Test = 'CheckFirst', @UniqueRows = 66000;

EXEC dbo.InsertNumber_Wrapper @Test = 'CheckFirst', @UniqueRows = 33000;

EXEC dbo.InsertNumber_Wrapper @Test = 'CheckFirst', @UniqueRows = 0;

EXEC dbo.InsertNumber_Wrapper @Test = 'JustInsert', @UniqueRows = 100000;

EXEC dbo.InsertNumber_Wrapper @Test = 'JustInsert', @UniqueRows = 66000;

EXEC dbo.InsertNumber_Wrapper @Test = 'JustInsert', @UniqueRows = 33000;

EXEC dbo.InsertNumber_Wrapper @Test = 'JustInsert', @UniqueRows = 0;

 

 

테스트가 완료되면 다음 스크립트를 실행하여 로그 테이블의 평균 시간을 확인해 본다. (평균 시간 결과는 사용자마다 다를 수 있다.)

;WITH s AS

(

    SELECT

        Test, Step, UniqueRows, dt, rn = ROW_NUMBER() OVER (PARTITION BY Test, UniqueRows ORDER BY dt)

    FROM InsertLog

),

 

x AS

(

SELECT

    s.Test, s.UniqueRows, [Duration] = DATEDIFF(MILLISECOND, s.dt, e.dt)

FROM s

    INNER JOIN s AS e

        ON s.Test = e.Test

            AND s.UniqueRows = e.UniqueRows

            AND s.rn = e.rn - 1

            AND s.Step = 1

            AND e.Step = 2

)

 

SELECT

    [Test], UniqueRows, [Avg] = AVG([Duration]*1.0)

FROM x

GROUP BY [Test], UniqueRows

ORDER BY UniqueRows, [Test];

 

 

위의 자료를 그래프로 확인해 보면 (I/O 및 CPU 사용률은 측정하지 않았다.) 모든 입력이 실패한 경우 데이터 입력 전에 체크하는 프로세스가 수행시간이 빠른 것을 확인 할 수 있으며 모든 데이터를 입력하는 쿼리에서는 조금 더 느린 것으로 나타났다.

 

결론적으로 제약 조건 위반의 기대치가 낮을 경우에는 TRY / CATCH 사용이 더 빠른 응답을 나타내었으며 제약 조건 위반의 기대치가 높을 경우에는 제약 조건을 검사하는 것이 응답 속도가 더 빠르게 나타났다.

 

위의 테스트 과정 및 시나리오는 매우 제한적인 테스트이므로 위의 결과가 반드시 정확하다고는 할 수 없다. 다양한 격리 수준과 예외 처리, 사용자 패턴 등을 고려하여 자신만의 예외 처리 방법에 대해 고민 할 수 있도록 하자.

 

[참고자료]

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

 

No. Subject Author Date Views
1930 트랜잭션 백업 실패와 전체 백업 성공 그리고 대처 방안 jevida(강성욱) 2016.10.13 1538
1929 Fast recovery 와 로그 잠금 jevida(강성욱) 2016.10.13 2018
1928 고스트 클린업 jevida(강성욱) 2016.10.13 2242
1927 페이지 분할이 발생 하였을 때 롤백을 하면 어떻게 될까? jevida(강성욱) 2016.10.13 1637
1926 DBCC WRITEPAGE - DBCC 명령을 사용한 데이터 파괴하기 jevida(강성욱) 2016.10.13 1638
1925 SQL Server Backup Error 3023 jevida(강성욱) 2016.10.13 2239
1924 Delete 작업과 페이지 offset 변화 jevida(강성욱) 2016.10.13 1477
1923 트랜잭션 로그 및 LSN을 이용한 삭제된 데이터 복구 jevida(강성욱) 2016.10.13 4950
1922 PFX 형식의 인증서를 SQL Server에서 사용하기 jevida(강성욱) 2016.10.13 1147
1921 SQL Server NUMA 메모리 노드와 Operating System 접근 jevida(강성욱) 2016.10.13 1279
» SQL Server 에러 핸들링 비용 비교 jevida(강성욱) 2016.10.13 1301
1919 압축 백업 시 Checksum 옵션으로 손상 확인하기 jevida(강성욱) 2016.10.13 1626
1918 SQL Server 2012 Memory Manager 구성 jevida(강성욱) 2016.10.13 1262
1917 SQL Server Memory Manager 변화 jevida(강성욱) 2016.10.13 1476
1916 Ring_Buffer_Resource_Monitor jevida(강성욱) 2016.10.13 994
1915 SQL Server 프로파일러 템플릿 만들기 jevida(강성욱) 2016.10.13 1331
1914 SQL Server 메모리 병목 현상 식별 jevida(강성욱) 2016.10.13 1755
1913 SQL Server 시작 옵션 사용 jevida(강성욱) 2016.10.13 1774
1912 Ring buffer를 활용한 External Memory Pressure 확인 jevida(강성욱) 2016.10.12 1256
1911 Memory Pressure jevida(강성욱) 2016.10.12 1120





XE Login