Just a quick post of some benchmarking code for our good friend Guayo at
TheDailyWTF, in regards to
this discussion.
The debate is, essentially: Assuming everything is properly indexed, which is faster/more efficient?
A) select * from ...
WHERE DATEPART( ... ) = @X and DATEPART( ...) = @Y
B) select * from ...
WHERE SomeDate BETWEEN @A and @B
C) select * from ... inner join SomeLookupTable on ....
WHERE SomeLookUptable.X = @X and SomeLookupTable.Y = @Y
see for yourself ..... and enjoy the thread on the other site as well, for those who are curious.
Basically, below, we have:
Days -- A lookup table of all possible dates our database needs to consider (1 row per date), along with a indexed Year and WeekNo columns
Test -- A table of data covering many days within that date range, from which we will be querying for a specific week number and year.
create table Test
(ID int primary key, TestDate datetime)
create index dt_index on Test (TestDate)
create table Days
(DateVal datetime primary key, [WeekNo] int, [year] int)
create index days_wd on Days ([WeekNo])
create index days_yr on Days ([Year])
go
declare @i int;
declare @d datetime;
set @i=0
set nocount on
while @i < 1000
begin
insert into Test (ID,TestDate) values (@i, convert(datetime, '1/1/2000') + (@i/4))
set @d = convert(datetime,'1/1/2000') + @i
insert into Days (DateVal, [WeekNo], [year]) values (@d, datepart(ww,@d), Year(@d))
set @i=@i+1
end
set nocount off
-- Here's the slowest:
select * from Test
where Datepart(ww,TestDate) = 14 and DatePart(yy,testdate) = 2000
-- Here's the fastest
select * from Test where TestDate between '3/26/2000' and '4/1/2000'
-- Here's the second fastest (twice as fast as the first):
select Test.* from test
inner join Days on Test.TestDate = Days.dateval
where Days.WeekNo = 14 and Days.Year = 2000
go
drop table Days
drop table Test