May 31, 2005 at 9:22 am
"A previous post says that dates make good indexes. I tend to disagree."
Hmm I couldn't believe either so I analyzed Remi's code which came to agree with that.
I started from the fact that INT and SMALLDATETIME they both use 4 bytes so when I have to compare I still have to compare 4 bytes in both case right ? so I see NO difference in PERFORMANCE from this point of view.
For Remi's example I have 1 observation:
select @DateInt, 200505
result
20055 200505
so I don't have to compare all four bytes and from here is the 20% difference ...
so I ll stick with the smalldatetime type : )
Vasc
May 31, 2005 at 9:33 am
Real nice observation Badeau
I reran the code with @DateInt = 200505 and it runs slower (somewhat) than the date version. So looks like I'm back to square one on this case.
Declare @DateStr as smalldatetime
Declare @DateChk as smalldatetime
Declare @DateInt as int
Declare @Year as int
Declare @Month as int
Declare @Date as smalldatetime
Declare @i as int
Declare @Loops as int
Declare @Start as DateTime
set @Year = YEAR(GetDate())
set @Month = Month(GetDate())
set @Loops = 250000
set @i = 0
set @DateStr = CAST(@Year as varchar(4)) + '/' + CAST(@Month as varchar(2)) + '/01'
set @DateChk = @DateStr
set @DateInt = cast(cast(@Year as varchar(4)) + RIGHT('0' + cast(@Month as varchar(2)), 2) as int)
select @DateInt, @DateChk, @DateStr
set @Start = GetDate()
while @i < @Loops
begin
--set @Date = dateadd(YYYY, @Year - 1900, (dateadd(MM, @Month - 1, 0)))
if @DateStr = @DateStr
set @i = @i + 1
else
set @i = @i + 1
end
Select DateDiff(MS,@Start, GetDate()) as "Datetime"
set @i = 0
set @Start = GetDate()
while @i < @Loops
begin
--set @Date = Convert(datetime, CAST(@Year as varchar(4)) + '/' + CAST(@Month as varchar(2)) + '/01')
if @DateInt = 200505
set @i = @i + 1
else
set @i = @i + 1
end
Select DateDiff(MS,@Start, GetDate()) as "int"
datetime = 7940
int = 8576
June 8, 2005 at 7:17 pm
I agree with Vasc that both types (int and smalldatetime) use 4 bytes.
Unfortunately, the code from Remi Gregoire is all done on the CPU rather than disk (usuing tables). I can assure you that I have tables with rowcounts exceeding 20 million rows with primary keys starting with a date (integer format) and when searching and joining I get far better performance. I suggest that you do some more testing using a table on disk (not tempdb).
You can also use several cast and convert functions to easily convert from date to integer and integer to date.
select cast(convert(char(8), getdate(), 112) as integer)
select cast(cast(20050609 as char(8)) as smalldatetime)
Cheers.
June 8, 2005 at 9:15 pm
Thanx for the tip... Will try to test that tomorrow.
June 9, 2005 at 12:57 am
I like to think that converting to string and back is not the best sultion possible. These should be faster:
SELECT CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) + 0x00000000 AS DATETIME)
SELECT CAST(CAST(SUBSTRING(CAST(GETDATE() AS binary(8)),1,4) AS INT) AS DATETIME)
SELECT DATEADD(d,DATEDIFF(d,0,getdate()),0)
SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply