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.