데이터베이스 개발자 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
1933 확장 이벤트를 사용한 CPU 고부하 쿼리 추적 [1] jevida(강성욱) 2016.10.15 2140
1932 데이터에 대한 이해와 spill in tempdb jevida(강성욱) 2016.10.13 1771
1931 로그 파일이 많으면 왜 안 좋은가 jevida(강성욱) 2016.10.13 2071
1930 트랜잭션 백업 실패와 전체 백업 성공 그리고 대처 방안 jevida(강성욱) 2016.10.13 1554
1929 Fast recovery 와 로그 잠금 jevida(강성욱) 2016.10.13 2032
1928 고스트 클린업 jevida(강성욱) 2016.10.13 2269
1927 페이지 분할이 발생 하였을 때 롤백을 하면 어떻게 될까? jevida(강성욱) 2016.10.13 1661
1926 DBCC WRITEPAGE - DBCC 명령을 사용한 데이터 파괴하기 jevida(강성욱) 2016.10.13 1650
1925 SQL Server Backup Error 3023 jevida(강성욱) 2016.10.13 2316
1924 Delete 작업과 페이지 offset 변화 jevida(강성욱) 2016.10.13 1487
1923 트랜잭션 로그 및 LSN을 이용한 삭제된 데이터 복구 jevida(강성욱) 2016.10.13 5298
1922 PFX 형식의 인증서를 SQL Server에서 사용하기 jevida(강성욱) 2016.10.13 1163
1921 SQL Server NUMA 메모리 노드와 Operating System 접근 jevida(강성욱) 2016.10.13 1309
» SQL Server 에러 핸들링 비용 비교 jevida(강성욱) 2016.10.13 1320
1919 압축 백업 시 Checksum 옵션으로 손상 확인하기 jevida(강성욱) 2016.10.13 1656
1918 SQL Server 2012 Memory Manager 구성 jevida(강성욱) 2016.10.13 1273
1917 SQL Server Memory Manager 변화 jevida(강성욱) 2016.10.13 1505
1916 Ring_Buffer_Resource_Monitor jevida(강성욱) 2016.10.13 1005
1915 SQL Server 프로파일러 템플릿 만들기 jevida(강성욱) 2016.10.13 1356
1914 SQL Server 메모리 병목 현상 식별 jevida(강성욱) 2016.10.13 1833





XE Login