프로시저와 임시테이블, 그리고 리컴파일
- Version : SQL Server 2005, 2008, 2008R2, 2012
대부분의 자료에서 보면 보안, 성능 측면에서 stored procedure 사용을 권장한다. 보안 적인 측면에서는 ad-hoc 쿼리의 실행 권한을 방지 함으로써 허가 받지 않는 데이터 변경을 방지 하고 성능 적인 측면에서는 실행 계획 재사용 및 컴파일 비용의 감소를 통해 성능상 이점을 지향 한다.
그렇다면 성능 적인 측면에서 프로시저를 사용하면 무조건 compile(recompile)이 발생하지 않을까? 다음 시나리오를 통해 알아 보자.
우리는 프로시저의 리컴파일에 대해서 알아 보기 위해 프로시저 내에서 임시 테이블을 생성하는 쿼리를 작성 하였다. 임시 테이블이라는 것은 TempDB에 생성되는 것으로 영속적인 데이터베이스의 개체가 아니라 세션 동안만 유지 되는 개체이다.
결론부터 정의하면 프로시저 내에서 임시 테이블을 참조하도록 코드가 생성되어 있으면 프로시저의 실행 계획은 영속적이지 않고 프로시저 실행 시 마다 컴파일이 이루어 진다. 다만 동일한 프로시저에서 생성한 임시 테이블에 접근하는 경우는 프로시저가 리컴파일 되지 않는다.
[동일한 SP에서 임시 테이블 사용]
동일한 SP에서 임시 테이블을 사용하였을 때에는 리컴파일이 발생하지 않는다.
create procedure Test_1
as
create table #temp (name nvarchar(50))
insert #temp values ('KANGSUNGWOOK')
select * from #temp |
[SP에서 임시테이블을 사용하는 다른 SP 호출]
SP Test_2를 호출하면 Test_2에서는 임시테이블을 생성하고 그 안에서 다른 프로시저 Test_3을 호출 한다. Test_3은 Test_2에서 생성한 임시테이블을 조회한다. 그리고 반복적인 작업을 위해 다시 Test_2를 호출하도록 설계 되었다.
Test_2에서 Test_3을 호출 하는 것은 동일한 SP에서 실행되는 것처럼 보이지만 Test_2에서 생성한 개체 ID 값은 계속 변하므로 호출 되는 Test_3프로시저는 계 해서 리컴파일 된다.
create procedure Test_2
as
create table #temp (name nvarchar(50))
insert #temp values ('KANGSUNGWOOK')
exec dbo.test_3 |
create procedure Test_3
as
select * from #temp
exec dbo.test_2 |
exec Test_2 |
[배치세션과 임시테이블 호출]
배치 작업 시 임시테이블이 세션 별로 유지 되기 때문에 해당 테이블을 저장 프로시저의 경우 해당 개체ID 가 유지되어 리컴파일이 발생하지 않는다.
아래의 시나리오에서는 동일한 프로시저 내에서 임시테이블을 참조하는 프로시저를 여러번 호출하게 하였다. 처음 한 번은 리컴파일이 발생하고 나머지 실행에서는 발생하지 않았다.
create procedure Test_4
as
begin create table #temp (name nvarchar(50))
insert #temp values ('KANGSUNGWOOK')
exec dbo.test_5 exec dbo.test_5 exec dbo.test_5 exec dbo.test_5 exec dbo.test_5 exec dbo.test_5
end |
create procedure Test_5
as
select * from #temp |
프로시저 사용 시 프로시저 안에서 사용하는 임시테이블의 성격을 잘 고려하여 리컴파일의 비용을 줄 일 수 있도록 생각해 보자.
[참고자료]
http://technet.microsoft.com/ko-kr/library/cc700710.aspx
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
