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
Notice [IT재직자] 개강임박!! 올해 마지막 무료 및 국비지원 교육 (JAVA, 리눅스 기초/고급 , CCNA, CCIE 등) 코난(김대우) 2022.12.02 129
Notice 2022년 11월 SQLER의 강좌 업데이트 리스트 코난(김대우) 2022.12.01 25
Notice 2022 공개SW 페스티벌 - Open Up 코난(김대우) 2022.12.01 15
1923 트랜잭션 로그 및 LSN을 이용한 삭제된 데이터 복구 jevida(강성욱) 2016.10.13 5369
1922 PFX 형식의 인증서를 SQL Server에서 사용하기 jevida(강성욱) 2016.10.13 1172
1921 SQL Server NUMA 메모리 노드와 Operating System 접근 jevida(강성욱) 2016.10.13 1320
» SQL Server 에러 핸들링 비용 비교 jevida(강성욱) 2016.10.13 1330
1919 압축 백업 시 Checksum 옵션으로 손상 확인하기 jevida(강성욱) 2016.10.13 1671
1918 SQL Server 2012 Memory Manager 구성 jevida(강성욱) 2016.10.13 1281
1917 SQL Server Memory Manager 변화 jevida(강성욱) 2016.10.13 1516
1916 Ring_Buffer_Resource_Monitor jevida(강성욱) 2016.10.13 1010
1915 SQL Server 프로파일러 템플릿 만들기 jevida(강성욱) 2016.10.13 1373
1914 SQL Server 메모리 병목 현상 식별 jevida(강성욱) 2016.10.13 1855
1913 SQL Server 시작 옵션 사용 jevida(강성욱) 2016.10.13 1866
1912 Ring buffer를 활용한 External Memory Pressure 확인 jevida(강성욱) 2016.10.12 1326
1911 Memory Pressure jevida(강성욱) 2016.10.12 1147
1910 64비트 버전의 SQL Server 버퍼 풀 메모리 페이지 수 줄이는 방법 jevida(강성욱) 2016.10.12 2580
1909 누락된 공유 잠금 (Missing Shared Locks) jevida(강성욱) 2016.10.12 1582
1908 Ad Hoc Distributed Queries 옵션 jevida(강성욱) 2016.10.12 3216
1907 높은 MAXDOP은 쿼리를 느리게 만들 수 있는가? jevida(강성욱) 2016.10.12 1347
1906 변경된 테이블 이름 복구하기 jevida(강성욱) 2016.10.12 1344
1905 Sys,dm_exec_connections jevida(강성욱) 2016.10.12 1995
1904 SQL Server 시작 매개 변수 설정 jevida(강성욱) 2016.10.12 2535





XE Login

테스트 팝업
Close