안녕하세요.
매일 좋은 자료만 보다가 처음으로 직접 질문을 올리게 되네요.
다름 아니라 변수값 형태에 따라 인덱스를 타는 방식에 대한 질문을 드리고자 합니다.
예1) 변수로 받아서 조건에 입력
declare @in_date1 varchar(10),
@in_date2 varchar(10)
set @in_date1 = '2014-08-01'
set @in_date2 = '2014-08-10'
select * from test where indate > @in_date1 and indate < @in_date2
예2) 조건에 직접 입력
select * from test where indate >'2014-08-01' and indate <'2014-08-10'
SQL 은 MSSQL 2008 R2 버전이며,
test 테이블의 indate 컬럼은 datetime 형식이며, 인덱스가 달려있습니다.
그런데 위와 같이 두 쿼리를 실행하면 예1)은 index scan 이 일어나여, 예2) 는 index seek 가 일어납니다.
이 때문에 속도에서 상당한 차이를 보이고 있는데요.
indate 컬럼이 datetime 형식이어서 varchar 로 받은 변수값을 convert 하면서 index scan이 발생하는 느낌이기는 한데,
정확한 이유를 모르겠어요.
예1)의 쿼리문을 index seek 가 일어나도록 하는 방법은 없을까요? 테이블 구조를 건드리지는 않으면서 쿼리만 수정할 수 있는 방법이 없을까요?
이러저리 형변환을 해도 동일하며, 동적쿼리로 작성을 해봐도 알 수가 없네요.
제가 워낙 초보라서 질문조차 조금 서툴르네요.
도움 부탁드릴께요.
감사합니다.
Comment 10
-
항해자™
2014.08.16 18:15
-
별난사람
2014.08.18 13:08
답변 감사드립니다.
그럼 결국 옵티마이저 맘대로 어느쪽을 참조하느냐에 따라 scan 이냐 seek 를 결정하는 건가요? 이걸 seek 로 탈수 있도록 할 방법은 없을까요?
-
자리비움
2014.08.17 23:30
인덱스 힌트를 주시면 됩니다.
인덱스 힌트 사용 시 주의하셔야 할 점은 검색해보시구요.
select * from test with(index=인덱스명) where indate > @in_date1 and indate < @in_date2
-
별난사람
2014.08.18 13:11
답변 감사드립니다.
인덱스 힌트는 생각해봤는데, 해당 테이블은 다른 테이블과 union 되어 별도의 뷰로 사용하는 테이블입니다.
연산자가 있는 뷰테이블에는 인덱스를 달수가 없더라구요. 해서 조회시 뷰 내용을 풀어서 인덱스 힌트를 주는 방법도 생각해보고 있는데, 좀더 깔끔하게 해결할 방법이 없을까 해서요. 답변 감사드립니다.
-
자리비움
2014.08.18 15:00
그러시다면,
로컬 변수값을 계산하는 sp1를 하나 만드신 후에
위 sp1에서 계산한 로컬 변수를 파라미터로 받는 sp2로 처리하시면 됩니다.
추가로,
로컬 변수 사용 시(밀도 때문에 그런지는 모르겠지만) 스캔이 발생한다고 하셨으니,
파라미터 스니핑만 주의하셔서 작업하시면 될 듯 합니다.
-
자리비움
2014.08.18 15:05
추가2,로컬 파라미터 사용 시 "밀도" 기준에서 스캔이라면, 특정 파라미터 기준에서는 scan이 유리할 수도 있습니다.통계정보를 확인해보세요. -
별난사람
2014.08.18 15:36
답변 정말 감사드려요
우선은 밑에 맨즈밤 님이 알려주신 recompile 옵션 을 이용하여 테스트 중인데, 님이 말씀하신 방법도 한번 테스트 해봐야 할 것 같아요.
그리고 공부도 더...
도와주셔서 정말 정말 감사드립니다. ^^
-
자리비움
2014.08.18 15:51
recompile 옵션 사용 시 실행계획 재사용을 못하고, 옵션줘도 어차피 밀도 계산이라 scan입니다.맨즈밤님이 말씀하신 recompile은 파라미터 스니핑을 예방하는 차원에서 말씀주신 듯 하구요.꼼수가 하나 있는데...검색될리가 없는 가짜 데이터 만들어서 밀도를 낮추세요.(물론 seek 가 항상 유리하다라는 기준입니다.) -
맨즈밤
2014.08.18 10:28
항해자님 답변에서 좀더 추가해볼께요.
프로시저 중간에 변수를 선언하고 값을 할당받으면 컴파일시엔 그 값을 모르기때문에 ( @in_date1 , @in_date2 ) 잘못된 실행계획이
나올 가능성이 커집니다. 되도록 프로시저의 파라메터값을 그대로 사용해야 하는게 좋습니다. 어떤 이유에 의해서 그렇지
못할경우,,또한 그게 성능상에도 문제를 일으키면 option(recompile) 옵션이나 인덱스힌트를 고려해보면 됩니다.
-
별난사람
2014.08.18 13:21
답변 감사드립니다.
recompile 옵션은 첨 들어보는건데... (아, 배울게 넘 많네요) 한번 찾아보고 다시 질문 드릴께요.
감사합니다.
local 변수의 값은 컴파일 타임에 unknown이라서 통계의 덴시티를 참조하게 됩니다,, 반면 상수값을 직접 넣으면 히스토그램을 참조하지요,,
이에 따라 scan할 수도 seek할 수도 있습니다,,,