SQL Server 에러 핸들링 비용 비교 - TRY / CATCH, 제약조건 선행 검사

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 사용이 더 빠른 응답을 나타내었으며 제약 조건 위반의 기대치가 높을 경우에는 제약 조건을 검사하는 것이 응답 속도가 더 빠르게 나타났다.

 

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

 

[참고자료]

• http://www.mssqltips.com/sqlservertip/2632/checking-for-potential-constraint-violations-before-entering-sql-server-try-and-catch-logic/
• http://www.sqlperformance.com/2012/08/t-sql-queries/error-handling

 

 

2014-02-05 / 강성욱 / http://sqlmvp.kr