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 |
테스트 시나리오는 다음과 같다.
- 모든 삽입 성공
- 삽입의 66% 성공
- 삽입의 33% 성공
- 모든 삽입 실패
이 테스트를 수행 하기 위해 추적 로그 테이블을 생성한다. 로그 테이블 생성 스크립트는 다음과 같다.
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
