XML Feeds
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′
Here is the plan
- |–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′
Here is the plan
- |–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.
