데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

프로시저와 임시테이블, 그리고 리컴파일

 

  • 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

No. Subject Author Date Views
2193 MSSQL과 Oracle 함수 비교 3탄 - 9가지 함수(마지막) [5] 쓸만한게없네(윤선식) 2010.11.08 82220
2192 DB 오류검사기록 테이블, 백업할때 오류정보 기록테이블 [3] 차주언 2011.07.20 56473
2191 SSMS 줄 번호 표시 jevida(강성욱) 2015.03.26 55138
2190 10_SQL 2008 강좌 - (1) DB관리 - DB 축소(SHRINK) [8] jevida(강성욱) 2011.03.01 51586
2189 Linked Server Open Query 이용 시 USE 명령어 사용하기 [1] 쓸만한게없네(윤선식) 2012.11.07 50806
2188 sa 비밀번호를 잊어먹었을 때 대처방법 [8] 이스트럭(강동운) 2011.06.13 47618
2187 SMO를 이용한 DB백업 [3] 우주인(김재훈) 2011.03.17 45035
2186 [TIP]MSSQL 와일드 카드를 이용한 검색과 주의점 [16] 쓸만한게없네 2010.10.12 44697
2185 프로파일러 사용법. (완전 초보자 기준!) [10] jevida(강성욱) 2011.04.26 43617
2184 SSMS 단축키. [12] 쓸만한게없네(윤선식) 2011.02.07 43331
2183 SQL Server 로긴 유저 및 각 DB별 사용자 권한 조회 [1] 문연군 2010.12.20 42705
2182 MSSQL과 Oracle 함수 비교 2탄 - 9가지 함수 [10] 쓸만한게없네(윤선식) 2010.10.27 42603
2181 Microsoft.ACE.OLEDB.12.0 을 이용한 엑셀 2010 직접쿼리하기 [3] 열이 2013.03.20 42151
2180 DBCC TRACE ... [4] 이스트럭(강동운) 2012.04.13 40956
2179 SQL Server 와 MySQL 문자열 함수 비교 10 가지 [5] 이호엽 2010.12.29 38284
2178 SQL2012는 SSMS의 시작 옵션(Startup option) 설정 위치가 바뀌었어요. [1] 코난(김대우) 2012.01.13 37467
2177 CONVERT 스타일 별 결과 값 [3] Alucard(강산아) 2010.10.14 36617
2176 MSSQL과 Oracle 함수 비교 - 첫번째 10가지 함수. [7] 쓸만한게없네(윤선식) 2010.10.25 35929
2175 트랜잭션 열린넘 있으면 로그백업할때 사이즈가 커진다!! [5] 차주언 2009.12.02 33557
2174 초간단 - 알만한 사람은 다 아는 읽고, 쓰고, 실행하는 권한만 주기 스크립트. [2] 쓸만한게없네(윤선식) 2011.07.21 32941





XE Login