How to optimize the following SQL Function

Question

I am trying to write a function which can return true or false or 0 or 1 based on some dates. So let's say you want to check if the start & end date for example "Mar 01 2010" & "Mar 02 2010" lies in the date range or not. so if your date range was "Jan 01 2012 - Dec 30 2012' it will return true otherwise if the date range is 'Jan 01 2011' to Dec 30 2011' then it will return false.

Currently i use the following and i feel it is not optimized and taking some time when you are calling this many times.

CREATE FUNCTION dbo.tmpfnIsDateInRange (@rangeStart    DATETIME, 
                                     @rangeEnd      DATETIME, 
                                     @chkStart      DATETIME, 
                                     @chkEnd        DATETIME) 
   RETURNS TINYINT 
AS 
   BEGIN 
      RETURN CASE 
                WHEN @chkStart BETWEEN @rangeStart AND @rangeEnd 
                     OR @chkEnd BETWEEN @rangeStart AND @rangeEnd 
                THEN 
                   1 
                WHEN @chkStart <= @rangeStart AND @chkEnd >= @rangeEnd 
                THEN 
                   1 
                ELSE 
                   0 
             END; 
   END

score 2 · Answer 1 · edited May 23 '17 at 10:09

If you are looking for range overlap, proper test would be:

return case when @chkStart <= @rangeEnd 
             and @chkEnd >= @rangeStart
            then 1
            else 0
            end

This expression returns true if two ranges overlap in any way.

Edit: here is a discussion written much better than i could ever do.

But the biggest gain would be to remove function altogether, and use this expression (save case part) as filter in query directly, as this would enable query optimizer to do as its name says.

score 1 · Answer 2 · answered May 10 '12 at 22:08

1

Never use the function in such scenorios!! do it directly in your sql & see the difference, its because the sql indexes are not applicale on sql functions!!!

answered May 10 '12 at 22:08

Yaqub Ahmad

27,569
23
102
149

I would specifically say avoid using a scalar function. Wrapping all of this in a table function shouldn't hinder performance and allows the logic to be reused similarly to a table/view but with parameters. – JeffO May 11 '12 at 01:36

score 0 · Answer 3 · answered May 10 '12 at 22:16

One thing I would look at is whether you're actually looking at "full" overlaps, or partial overlaps.

For example: Would March 1 - March 3 return true if you were checking the range March 2 - March 4?

If that's the case, you'd have to make more checks for "partial" overlaps.

In either case, you should remove the function.

How to optimize the following SQL Function

3 Answers3