SWITCHOFFSET 내장함수의 잘 못된 예측 - 미리 계산한 값을 쿼리에 연결하여 최적화 하기

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

 

 

 

[참고자료]

• http://blogs.msdn.com/b/psssql/archive/2013/03/02/switchoffset-built-in-function-can-cause-incorrect-cardinality-estimate.aspx
• http://msdn.microsoft.com/ko-kr/library/bb677244.aspx

 

 

 

2013-07-12 / 강성욱 / http://sqlmvp.kr