임시 테이블 캐싱 이슈 (Work-Around)
REMARK 2 START
SQL 2005 부터는 allocation contention 발생을 줄이기 위해 저장프로시저 내 임시테이블이 캐시된다. 이런 캐시 영향도가 전체 프로시저의 실행계획에 영향을 끼칠 수 있다. 이 경우 Ramesh Meyyappan 이 여러가지 솔루션을 제안했다. (이사람도 지금은 컨설턴트라서 좋은 솔루션은 절대 안가르쳐 준다. workshop 수업 들어야 알려줄것 같다. ) 좀 더 효율적인 솔루션이 없을까? 가 문제이다.
Ramesh Meyyappan 이 제안한 방법은 3가지 정도가 된다. (별로 영양가가 없으므로, 자세히 하지 않겠다. )
- option recompile 로는 임시테이블 통계가 바뀌지 않기 때문에 문제가 해결되지 않는다.
- 템프 테이블에 명명된 제약조건 생성(동시에 실행하면, 이름충돌 문제있음)
- 임시테이블에 인덱스를 만드는 방법
데이터 생성 스크립트
CREATE DATABASE EPLAN
GO
USE EPlan
go
set nocount on
set statistics time off
set statistics io off
drop table tab7
go
create table tab7 (
c1 int primary key clustered
, c2 int
, c3 char(200)
)
go
create index test on tab7(c2, c1, c3)
go
/*
-- 주의) 실행계획 표시 해제할 것
*/
begin tran
declare @i int
set @i = 1
while @i <= 50000
begin
insert into tab7 values (@i, 1, 'a') -- *) c2는 모두 1
set @i = @i + 1
end
commit tran
go
insert into tab7 values (50001, 1, 'a')
go
checkpoint
go
/*
1) 원본
*/
DROP PROC test_slow
go
CREATE PROC test_slow
@i int
AS
BEGIN
DECLARE @j int
CREATE TABLE #temp1 (c1 int primary key)
INSERT INTO #temp1 (c1) SELECT @i
SELECT @j = t7.c1
FROM tab7 t7
INNER JOIN #temp1 t
ON (t7.c2 = t.c1)
END
go
위 쿼리는 인자를 1로 주면 SCAN, 인자를 2로 줘도 SCAN 이다. 이것을 1이 인자로 들어오면 SCAN 2가 인자로 들어오면 SEEK 가 되도록 고쳐야 하고, 전체 프로시저를 리컴파일(플랜이 캐시되지 않는다.) 하지는 않아야 한다. 아래 스크립트를 실행하면, 캐시하여 둘 다 SCAN 한다.
SCAN
exec test_slow 1
인덱스 SEEK 를 하면 효율적이지만 캐싱된 임시테이블로 인한 SCAN
exec test_slow 2
Ramesh Meyyappan 의 워크샵에 참가하면 알려줄것 같은 답안 1
DROP PROC test_slow go CREATE PROC test_slow @i int AS BEGIN DECLARE @j int CREATE TABLE #temp1 (c1 int primary key) INSERT INTO #temp1 (c1) SELECT @i update statistics #temp1 SELECT @j = t7.c1 FROM tab7 t7 INNER JOIN #temp1 t ON (t7.c2 = t.c1) option (recompile) END go
위 방법은 전체 프로시저를 recompile 하지 않았고 필요한 부분만 정확하게 재컴파일 할 수 있는 방법이다. update statistics 와 option (recompile) 은 같이 쓰여져야 한다.
이 글은 두 사람보다 훨씬 심도깊은 테스트와 의견을 제시한 사람이 있다. 다음 두개의 글을 읽어보길 바란다.
http://sqlblog.com/blogs/paul_white/archive/2012/08/17/temporary-object-caching-explained.aspx
http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx
위 두 글 뿐만 아니라 PAUL 의 글은 매우 수준이 높고 SQL을 이해하는데 도움이 많이 된다. 이글을 소개한 강사는 솔루션을 계속 찾고있다고 한다. 인터넷에 위 글을 참고하면 좋을듯 하다.
REMARK 2 END