AFTER 트리거를 INSTEAD OF 트리거로 변경 후 효율성 향상

AFTER 트리거를 INSTEAD OF 트리거로 변경 후 효율성 향상

 

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

 

트리거는 SQL Server에서 이벤트가 발생하면 자동으로 실행되는 특수한 종류의 저장 프로시저이다. DML, DDL, LOGON 트리거를 생성 할 수 있다.

 

트리거는 FOR|AFTER 와 INSTEAD OF 인수가 있다.

• FOR|AFTER : AFTER는 DML 트리거를 지정한 모든 작업이 성공적으로 실행되었을 때만 트거가 실행도록 지정한다. 모든 참조 연계 동작 및 제약 조건 검사도 이 트리거가 실행되기 전에 성공해야 한다.
• INSTEAD OF : 트리거를 시작하는 SQL문 대신 DML 트리거가 실행되도록 지정한다. DDL 또는 LOGON 트리거에 대해서는 INSTEAD OF를 지정할 수 없다.

 

이번 포스트는 트리거를 사용 할 때 AFTER 트리거를 INSTEAD OF 트리거로 변경하여 트리거의 효율성을 높인 사례이다. INSTEADOF 트리거는 작업을 수행하기 전에 비즈니스 규칙을 확인 할 수 있어 로그 롤백을 할 필요가 없어 효율적이다. 특히 속도가 느린 디스크에서는 더욱 효율 적이다.

 

실습을 위해 트리거를 기록할 테이블을 생성한다.

CREATE TABLE dbo.UserNames_After ( ID INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(255) NOT NULL UNIQUE );   CREATE TABLE dbo.UserNames_InsteadOf ( ID INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(255) NOT NULL UNIQUE );

 

예외 목록을 저장할 테이블을 생성 후 예외 목록을 입력 한다.

CREATE TABLE dbo.NaughtyUserNames ( Name NVARCHAR(255) PRIMARY KEY ); GO   INSERT dbo.NaughtyUserNames VALUES('admin'); GO

 

 

After 트리거를 생성한다.

CREATE TRIGGER dbo.trUserNames_After ON dbo.UserNames_After AFTER INSERT AS BEGIN IF EXISTS ( SELECT 1 FROM inserted AS i WHERE EXISTS ( SELECT 1 FROM dbo.NaughtyUserNames WHERE Name = i.Name ) ) BEGIN RAISERROR('You used a admin name!', 11, 1); ROLLBACK TRANSACTION; END END GO

 

데이터를 입력 한다. 사용자가 이름을 입력하고 실행하면 예외가 발생하고 트랜잭션이 롤백 한다.

INSERT dbo.UserNames_After(Name) SELECT 'admin';

 

메시지 50000, 수준 11, 상태 1, 프로시저 trUserNames_After, 줄 16 You used a admin name! 메시지 3609, 수준 16, 상태 1, 줄 1 트리거가 발생하여 트랜잭션이 종료되었습니다. 일괄 처리가 중단되었습니다.

 

 

 

INSTEAD OF 트리거를 생성한다.

CREATE TRIGGER dbo.trUserNames_InsteadOf ON dbo.UserNames_InsteadOf INSTEAD OF INSERT AS BEGIN IF NOT EXISTS ( SELECT 1 FROM inserted AS i WHERE EXISTS ( SELECT 1 FROM dbo.NaughtyUserNames WHERE Name = i.Name ) ) BEGIN INSERT dbo.UserNames_InsteadOf(Name) SELECT Name FROM inserted; END ELSE BEGIN RAISERROR('You used a admin name!', 11, 1); END END GO

 

다음 스크립트는 10%의 비율로 admin 이름이 사용된 경우 실패를 반환하지만 테이블에 기록을 하지 않아 롤백을 수행 하지 않는다.

TRUNCATE TABLE dbo.UserNames_After; TRUNCATE TABLE dbo.UserNames_InsteadOf;   CHECKPOINT;   SELECT COUNT(*) FROM sys.fn_dblog(NULL, NULL);   SELECT CurrentSizeMB = size/128.0, FreeSpaceMB = (size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT))/128.0 FROM sys.database_files WHERE name LIKE '%[_]log';   DECLARE @n NVARCHAR(255), @u UNIQUEIDENTIFIER;   DECLARE c CURSOR LOCAL FAST_FORWARD FOR SELECT TOP (900) name = o.name + '/' + c.name, u = NEWID() FROM sys.all_objects AS o INNER JOIN sys.all_columns AS c ON o.[object_id] = c.[object_id] UNION ALL SELECT TOP (100) name = 'admin', u = NEWID() FROM sys.all_objects ORDER BY u;   OPEN c;   FETCH c INTO @n, @u;   SELECT SYSDATETIME();   WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY INSERT dbo.UserNames_InsteadOf(name) SELECT @n; --INSERT dbo.UserNames_After(name) SELECT @n; END TRY BEGIN CATCH PRINT 'Failed'; END CATCH FETCH c INTO @n, @u; END   SELECT SYSDATETIME();   CLOSE c; DEALLOCATE c;   SELECT COUNT(*) FROM sys.fn_dblog(NULL, NULL);   SELECT CurrentSizeMB = size/128.0, FreeSpaceMB = (size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT))/128.0 FROM sys.database_files WHERE name LIKE '%[_]log';

 

 

다음 표는 위의 스크립트를 사용하여 측정한 결과이다. INSTEAD OF 트리거를 사용하여 미리 비즈니스 검사를 수행하여 롤백의 시간을 줄여 빠른 성능을 나타내었다.

 

 

위의 결과를 토대로 무조건 INSTEAD OF 트리거가 빠르다거나 AFTER 트리거가 느리다고 판단하면 위험하다. 비즈니스에 맞게 트리거를 잘 활용하는 것이 중요하다. 위 사례를 바탕으로 다양한 트리거의 활용을 생각해보면 좋을 듯 하다.

 

[참고자료]

• http://www.mssqltips.com/sqlservertip/3139/improve-sql-server-efficiency-by-switching-to-instead-of-triggers/
• CREATE TRIGGER : http://technet.microsoft.com/ko-kr/library/ms189799.aspx

 

 

2013-12-20 / 강성욱 / http://sqlmvp.kr