--test table 생성
CREATE TABLE TEST_TABLE
(
TESTID varchar(20),
TRNSDATE datetime
)
--데이터삽입
declare @i int
declare @trnsdate datetime
set @i = 1
set @trnsdate = getdate()
WHILE (@i < 10000)
BEGIN
set @trnsdate = dateadd(minute,5,@trnsdate)
INSERT INTO TEST_TABLE (TESTID, TRNSDATE) VALUES (@i,@trnsdate)
SET @i = @i +1
END
--인덱스 생성
CREATE INDEX IX_TESTID ON TEST_TABLE
(
TRNSDATE
)
--프로시저조회 (매개변수를 직접 쿼리에사용)
CREATE PROCEDURE proGetTestId
@trnsdate datetime
AS
BEGIN
set nocount on
select * from test_table where TRNSDATE >= @trnsdate AND TRNSDATE <= dateadd(hour,1,@trnsdate)
END
--프로시저조회 (매개변수를 변수에 할당후 사용)
CREATE PROCEDURE proGetTestId_1
@trnsdate datetime
AS
BEGIN
set nocount on
declare @stDate datetime
declare @etDate datetime
set @stDate= @trnsdate
set @etDate = dateadd(hour,1,@trnsdate)
select * from test_table where TRNSDATE >= @stDate AND TRNSDATE <= @etDate
END
exec proGetTestId '2013-04-02 01:36:26'
exec proGetTestId_1 '2013-04-02 01:36:26'
위와같이 처리했을경우 proGetTestId 프로시저는 index scan을 하는데
proGetTestId_1의 경우는 table scan을 합니다.
왜 이런결과가 나오는지 궁금하네요?
매개변수를 변수에 할당하는 경우 index처리가 안되는건가요?
Comment 4
-
minsouk
2013.04.02 17:32
-
정선
2013.04.02 21:18
쉽게 이해할 수 있도록 설명을 드려볼게요 :)
인덱스를 쓸지 말지 DB엔진이 결정하기 위해서 조건절을 만족하는 행 수를 대략적으로 예측을 해야해요,
그럴러면 조건절의 값이 무엇인지 미리 알 수 있어야하거든요.
그걸 판단하는 시점이 쿼리를 컴파일하는 시점일텐데요, 우리가 일반적으로 알고 있는 로컬변수라는 것은
실행시점에서야 그 값을 알 수가 있잖아요? 쿼리를 컴파일하는 시점엔 값이 할당되기 전이까요.
그래서 DB엔진에서 알 수 없는 값을 대상으로 행 수를 예측하다가 결과적으로 잘못된 계획을 선택할 수도 있게 됩니다.
반면에 저장 프로시저의 매개변수는 쿼리를 호출하는 시점에 그 값이 무엇인지 알려져 있으니 보다 명확해 지는 것이구요.
어떻게 좀 더 이해가 잘 되셨는지 모르겠네요.
참고로 SQL Server의 열 통계정보(Statistics)라는 것에 대해서 공부를 해 보시면 더 많이 도움이 되실겁니다.
이를 해결하는 방법도 여러가지입니다. 로컬변수 안쓰는 것도 방법이구요^^
좋은 책들이나 자료도 많으니 참고해 보세요.
도움이 되시길.
-
영탁
2013.04.02 21:42
고맙습니다
이제이해가되네요^^ -
쓸만한게없네(윤선식)
2013.04.05 17:18
흠.. 사족.. Index Scan 이 아니라 Index Seek 인 듯..
Index Seek 와 Index Scan 은... 어마어마한 차이입니다. ^^.
아래처럼 사용하면 density 값을 사용하고 위에처럼 사용하면 histogram 을 보기 때문 입니다.
work-around 는 내부에서 sub procedure 를 하나 더 만들거나, 아래 쿼리에서 option(recompile) 을 걸어 보세요
참고로, procedure 레벨의 with recompile 은 동작하지 않습니다.