Can you complete migration during production hours without users noticing? Yes! Watch Program Manager Chris Almida show you how.

Posted by TechNet Announcements for Week of 9/28/2009, filed under Uncategorized. Date: July 24, 2008, 5:16 pm | No Comments »

Check out the latest in the Springboard Series Roundtable discussions, hosted by Mark Russinovich.

Posted by TechNet Announcements for Week of 9/28/2009, filed under Uncategorized. Date: July 24, 2008, 5:16 pm | No Comments »

Multi-task while listening to the latest findings in our current threat landscape.

Posted by TechNet Announcements for Week of 9/28/2009, filed under Uncategorized. Date: July 24, 2008, 5:03 pm | No Comments »

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.

Posted by Denis Gobo, filed under Uncategorized. Date: July 24, 2008, 1:20 pm | No Comments »


Search Engine Optimization and SEO Tools