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

-
Published 16 May 2006 08:15 by simonsabin
Filed under: ,

Comments

No Comments