SWITCHOFFSET 내장함수의 잘 못된 예측
- 미리 계산한 값을 쿼리에 연결하여 최적화 하기
- Version : SQL Server 2005, 2008, 2008R2, 2012
이번 사례는 SQL Server CSS Engineer 공식 팀블로그에 기재된 내용으로 SQL Server의 내장 함수인 SWITCHOFFSET 함수 사용으로 잘못된 예측으로 인한 성능 문제이다.
[SWITCHOFFSET 함수]
저장된 표준 시간대 오프셋에서 지정된 새 표준 시간대 오프셋으로 변경된 datetimeoffset 값을 반환.
CREATE TABLE dbo.test ( ColDatetimeoffset datetimeoffset ); GO
INSERT INTO dbo.test VALUES ('1998-09-20 7:45:50.71345 -5:00'); GO
SELECT SWITCHOFFSET (ColDatetimeoffset, '-08:00') FROM dbo.test; GO --Returns: 1998-09-20 04:45:50.7134500 -08:00
SELECT ColDatetimeoffset FROM dbo.test; GO --Returns: 1998-09-20 07:45:50.7134500 -05:00 |
[잘못된 예측으로 인한 성능 저하]
블로그에 기재된 내용을 요약하면 기간 검색으로 데이터를 조회 하는데 쿼리의 실행 속도가 매우 느리다고 한다. 그래서 쿼리를 확인해 보니 다음과 같은 형식의 쿼리가 실행 되고 있었다,
select * from tbl_x where c1 > switchoffset (Convert(datetimeoffset, GETDATE()), '-04:00') |
SQL Server는 많은 built=in/intrinsic 기능이 있다. 옵티마이저는 쿼리를 컴파일 하는 동안 엿보기(peek)를 사용하여 더 나은 추정치를 제공하는 기능을 실행에 사용 할 수 있다.
예를 들어 쿼리에서 select * from t where c1 > getdate() 같은 값을 사용하였다면 옵티마이저는 getdate() 값을 가져온 다음 히스토그램을 사용하여 최대한 정확한 비용 산출을 하려 할 것이다.
DATEADD 함수 또한 최적화 트릭을 할 수 있는 내장 함수 이다. 그러나 SWITCHOFFSET는 옵티마이저가 엿볼 수 없기 때문에 최적화 트릭을 사용할 수 없다.
이 문제를 해결 하기 위해서 GETDATE()와 SWITCHOFFSET를 사용하는 경우 선언문을 통해서 미리 값을 계산해 놓고 쿼리에 연결하면 좋다.
--before select * from tbl_x where c1 > switchoffset (Convert(datetimeoffset, GETDATE()), '-04:00')
--after declare @dt datetimeoffset = switchoffset (Convert(datetimeoffset, GETDATE()), '-04:00') select * from tbl_x where c1 > @dt option (recompile) |
[AdventureWorks 예제]
set statistics profile on
--before select * from Production.ProductCostHistory where StartDate > switchoffset (Convert(datetimeoffset, GETDATE()), '-04:00')
--after declare @dt datetimeoffset = switchoffset (Convert(datetimeoffset, GETDATE()), '-04:00') select * from Production.ProductCostHistory where StartDate > @dt option (recompile)
set statistics profile off |
[참고자료]
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
