Performance of MAX trick
Jamie posted a comment to by post about the trick for finding a maximum value in a set of results. Stating that the CASE statement solution was significantly faster. Being a synic I didn't believe him so tried it out for myself
The following are the two sets of code, and below that is a script to populate the tables, the first set of inserts are there to test the results, the commented out section is to load the dates table with lots of values.
On my server the difference is negligible, often with the subquery mechanism being quicker to execute, if slghtly longer to compile.
However the point to note is that the compile time is ~1ms and the execution time for 1024 rows is ~70ms. Thats pretty quick which ever option you choose and more importantly I know which one I would want to be supporting :)
select case when orderdate > canceldate then
case when shipdate > deliverydate then
case when orderdate > shipdate then orderdate else shipdate end
else
case when orderdate > deliverydate then orderdate else deliverydate end
end
else
case when shipdate > deliverydate then
case when canceldate > shipdate then canceldate else shipdate end
else
case when canceldate > deliverydate then canceldate else deliverydate end
end
end
, *
from dates
go
select (select max(datevalue)
from (select orderdate datevalue union all select canceldate union all select shipdate union all select deliverydate )d)
, *
from dates
create table dates (orderdate datetime, canceldate datetime,shipdate datetime, deliverydate datetime)
go
insert into dates values ('1/1/2005', '1/2/2005', '1/3/2005', '1/4/2005')
insert into dates values ('1/1/2005', '1/2/2005', '1/4/2005', '1/3/2005')
insert into dates values ('1/1/2005', '1/3/2005', '1/2/2005', '1/4/2005')
insert into dates values ('1/1/2005', '1/3/2005', '1/4/2005', '1/2/2005')
insert into dates values ('1/1/2005', '1/4/2005', '1/2/2005', '1/3/2005')
insert into dates values ('1/1/2005', '1/4/2005', '1/3/2005', '1/2/2005')
insert into dates values ('1/2/2005', '1/1/2005', '1/3/2005', '1/4/2005')
insert into dates values ('1/2/2005', '1/1/2005', '1/4/2005', '1/3/2005')
insert into dates values ('1/2/2005', '1/3/2005', '1/1/2005', '1/4/2005')
insert into dates values ('1/2/2005', '1/3/2005', '1/4/2005', '1/2/2005')
insert into dates values ('1/2/2005', '1/4/2005', '1/1/2005', '1/3/2005')
insert into dates values ('1/2/2005', '1/4/2005', '1/3/2005', '1/1/2005')
insert into dates values ('1/3/2005', '1/2/2005', '1/1/2005', '1/4/2005')
insert into dates values ('1/3/2005', '1/2/2005', '1/4/2005', '1/1/2005')
insert into dates values ('1/3/2005', '1/1/2005', '1/2/2005', '1/4/2005')
insert into dates values ('1/3/2005', '1/1/2005', '1/4/2005', '1/2/2005')
insert into dates values ('1/3/2005', '1/4/2005', '1/2/2005', '1/1/2005')
insert into dates values ('1/3/2005', '1/4/2005', '1/1/2005', '1/2/2005')
insert into dates values ('1/4/2005', '1/2/2005', '1/3/2005', '1/1/2005')
insert into dates values ('1/4/2005', '1/2/2005', '1/1/2005', '1/3/2005')
insert into dates values ('1/4/2005', '1/3/2005', '1/2/2005', '1/1/2005')
insert into dates values ('1/4/2005', '1/3/2005', '1/1/2005', '1/2/2005')
insert into dates values ('1/4/2005', '1/1/2005', '1/2/2005', '1/3/2005')
insert into dates values ('1/4/2005', '1/1/2005', '1/3/2005', '1/2/2005')
go
--insert into dates select getdate() + 100*rand(),getdate() + 100*rand(),getdate() + 100*rand(),getdate() + 100*rand()
--go 1000
set statistics time on
go
-