THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Converting Columns To Date From Datetime Does Not Result In A Scan In SQL Server 2008

I was reading Itzik Ben-Gan's An Introduction to New T-SQL Programmability Features in SQL Server 2008 article yesterday after one of my friends allerted me to the following from that article
For example, the plan for the following query performs an index seek on the index on the CurrencyRateDate DATETIME column:

USE AdventureWorks;

SELECT FromCurrencyCode, ToCurrencyCode, EndOfDayRate

FROM Sales.CurrencyRate

WHERE CAST(CurrencyRateDate AS DATE) = '20040701';

I was surprised by this, as we all know functions/conversions on column names are generaly bad for performance.


Let's see how this works. First create this table in the tempdb database.

 

use tempdb

go

create table TestDatetimePerf (SomeCol datetime,id int identity)

go

This will insert 2048 rows with dates between 2008-01-01 12 AM and 2008-03-26 7 AM

insert TestDatetimePerf(SomeCol)

select dateadd(hh,number,'20080101')

from master..spt_values

where type ='P'

go

create index ix_Date on TestDatetimePerf(SomeCol)

go

 

Turn on the execution plan

set showplan_text on

go

 

Execute the following query

select *

from TestDatetimePerf

where convert(varchar(30),SomeCol,112) = '20080103'

|--Table Scan(OBJECT:([tempdb].[dbo].[TestDatetimePerf]),

--WHERE:(CONVERT(varchar(30),[tempdb].[dbo].[TestDatetimePerf].[SomeCol],112)=[@1]))

As you can see that results in a scan.

 

What happens when you convert to date?

select *

from TestDatetimePerf

where convert(date,SomeCol) = '20080103'

|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))

|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1007], [Expr1008], [Expr1006]))

| |--Compute Scalar(DEFINE:(([Expr1007],[Expr1008],[Expr1006])=GetRangeThroughConvert('2008-01-03','2008-01-03',(62))))

| | |--Constant Scan

| |--Index Seek(OBJECT:([tempdb].[dbo].[TestDatetimePerf].[ix_Date]),

--SEEK:([tempdb].[dbo].[TestDatetimePerf].[SomeCol] > [Expr1007]

--AND [tempdb].[dbo].[TestDatetimePerf].[SomeCol] < [Expr1008]),

--WHERE:(CONVERT(date,[tempdb].[dbo].[TestDatetimePerf].[SomeCol],0)='2008-01-03') ORDERED FORWARD)

|--RID Lookup(OBJECT:([tempdb].[dbo].[TestDatetimePerf]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

 

See that? You get a seek instead, very interesting. It would be nice that when you use convert with the style optional parameter that the optimizer would be smart enough to convert that also to a seek.

Published Thursday, July 24, 2008 3:20 PM by Denis Gobo
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jchang said:

I bugged MS about having Date functions return Date or DateTime as appropriate to the situation for cases such as :

WHERE dateCol = DATEADD(mm, 3, @dateVar)

behaves properly. The date functions already return smalldatetime where for small dt input, so why not Date?

They told me to bug off, but I suppose when 2K8 crashes peoples DW, the change will go in.

July 24, 2008 9:37 PM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement