Wednesday, August 27, 2008

T-SQL Performance of the DatePart function

I recently have had some trouble with speed on one of my queries. Suspecting it might be a DatePart function I was applying to every row of retrieved data I wrote a little performance test and was pleasantly surprised at how efficient DatePart was:

[code]

declare @loop int, @temp varchar(10), @datestamp datetime
set @loop=1
print convert(varchar(100),getdate(),113)
set @datestamp = getdate()
while @loop < 1000000
begin
set @loop = @loop + 1
set @temp = DatePart(q, '08/27/2008')
end

print convert(varchar(100),getdate(),113)
print datediff(ms, @datestamp,getdate())

set @datestamp = getdate()
set @loop=1
while @loop < 1000000
begin
set @loop = @loop + 1
set @temp = ''
end
print convert(varchar(100),getdate(),113)
print datediff(ms, @datestamp,getdate())

[/code]

[results]

27 Aug 2008 15:23:12:293
27 Aug 2008 15:23:14:200
1906
27 Aug 2008 15:23:15:983
1783

[/results]

so the DatePart function only added a little more than 100 milliseconds to the processing time.

No comments:

Post a Comment

Please leave your thoughts, I love hearing what you got out of the post. Spam comments will be removed.