Function vs Case efficiency

Question

Before your brain explodes yes i have seen this.

My question is tailored to my specific situation. Im trying to optimize a very large database and im trying to rewrite some calls because it was converted from an Orical DB to a MS DB.

i have a function that is being called from a stored proc that i want to replace and i think it will be fine but im not sure in all cases.

this is the current function that is being called.

ALTER function [dbo].[GREATEST_DATETIME]
        (
         @i1_p  datetime,
         @i2_p  datetime
        )
returns datetime as

begin

    declare @r_l     datetime

    if @i1_p is null or @i2_p is null
        return null

    set @r_l = @i1_p
    if @i2_p > @r_l
        set @r_l = @i2_p

    return @r_l
end

I want to replace the call to the function entirely with this.

select CASE WHEN @date1 >= @date2 THEN @date1 ELSE @date2 END

They both determine the largest date out of 2 dates im just not sure if im covering all my bases.

After testing

declare @date1 datetime
declare @date2 datetime

set @date1 = '2015-05-01'
set @date2 = null

select CASE WHEN @date1 >= @date2 THEN @date1 ELSE @date2 END

This returns null which is the desired result

Here is my final result that is going to work

declare @date1 datetime
declare @date2 datetime

set @date1 = null
set @date2 = '2015-05-01'

select CASE WHEN @date1 is null or @date2 is null then null WHEN @date1 >= @date2 THEN @date1 ELSE @date2 END

"My question is tailored to...."... But you ask no question. What is your question? — Adam, Jun 01 '15 at 19:07
Other then the case where either is `null` yes it is equivalent. — D Stanley, Jun 01 '15 at 19:08
RE: Your test try `set @date1 = null set @date2 = '2015-05-01'` — Martin Smith, Jun 01 '15 at 19:16
Scalar functions in t-sql are horribly inefficient. The case expression will perform much better on larger datasets. — Sean Lange, Jun 01 '15 at 19:21
For more complex logic, you should also compare performance of inline table functions, as far as I have seen, those perform quite good. — James Z, Jun 01 '15 at 19:23
If you use inline table valued functions make certain they are one and only one statement. Otherwise they become multi-statement table valued functions and the performance tanks massively. — Sean Lange, Jun 01 '15 at 19:28

score 2 · Answer 1 · answered Jun 01 '15 at 19:08

2

The new case statement here does not handle NULL in the same way as the original. The original would result in NULL if either source data was null.

answered Jun 01 '15 at 19:08

Joel Coehoorn

399,467
113
570
794

score 1 · Accepted Answer · answered Jun 01 '15 at 19:10

If one of both dates are null then the first comparision will fail and return false. So you need the extra null check

select CASE WHEN @date1 is null or @date2 is null then null
            WHEN @date1 >= @date2 THEN @date1 
            ELSE @date2 
       END

Anon · Answer 3 · 2015-06-01T21:08:13.643

0

If your business logic defaults to NULL, your code should too. The maintainers will thank you.

SELECT CASE
  WHEN @date1 >= @date2 THEN @date1
  WHEN @date2 >  @date1 THEN @date2
  ELSE NULL
END

edited Jun 01 '15 at 21:08

answered Jun 01 '15 at 20:56

Anon

10,660
1
29
31

Function vs Case efficiency

3 Answers3