SQL Function using WHILE takes a long time to run

Question

I've created a SQL function that takes two dates, then finds the number of days between them, excluding weekends and holidays. It works, but the issue is that it takes 1.5 seconds per row to run. This is a bummer, because we're running this for reports that are potentially thousands of rows long.

For a little more background, the cteTally table is just a generic tally table. The HLD1 table is a table that looks like the following:

Calendar       StrDate    EndDate    
2016Holidays   1/1/16     1/1/16
2016Holidays   5/30/16    5/30/16
2016Holidays   7/4/16     7/4/16
2016Holidays   9/5/16     9/5/16
2016Holidays   11/24/16   11/25/16
2016Holidays   12/26/16   12/26/16

Essentially what it's intent is is that when a customer calls in on a repair request for a washing machine, we want to track the response time, but only counting business days in our count. So, for example, if a customer called in on June 30 at 4:30 PM, and a tech went out to repair on July 5 at 8:30 AM, we would count Friday 7/1 (a non-holiday, non-weekend date), not count Saturday 7/2 or Sunday 7/3 (weekend days), and not count Monday 7/4 (a holiday), and count Tuesday 7/5 to get an accurate count of the overnights.

The answer that it would then return is 2.

In order to do this, it looks at the days in the function and says "when these days meet these criteria, add or subtract a day so the start or end date is not a weekend or holiday."

I'm about 98.36% sure that there has to be a better/easier way to do this, and I'm way overthinking what's going on, but I can't for the life of me determine what I could do differently, and I've been working on it on and off for about 3 days now.

EDIT (before I even posted): I've now gotten it down to 19 seconds for the 14560 rows returning that I'm using the function in. However, before I modified the function to take holidays into account, it was able to return all of the rows in less than a second. The change that I made was to limit the "holidays" that were getting returned to holidays in between the start and end date. However, I think the "WHILE" loop is still causing me some issues.

I've also tried creating a temp table or a cte table to store the holidays in so it only has to calculate them once per line instead of 4 times per line, but it doesn't appear that that is possible within a function.

I'm going to keep working on this for now, but would love any help that y'all can provide.

EDIT 2: I put all of the "SELECT @variables" that were in a row in the same select statement instead of having separate ones, and that cut the time down by a further 9 seconds for 14000 rows. However, I'd still like to get it under the 2 second barrier, if possible, or at least the 5 second barrier.

Here is the text of the function:

CREATE FUNCTION [dbo].[dateDiffHolidays] (
@startdaytime DATETIME,
@enddaytime DATETIME
)
RETURNS INT
AS
BEGIN

DECLARE @answer INT;
DECLARE @START Date;
DECLARE @END Date;
DECLARE @AddDays int;

SET @answer = 0

-- Strip Times
SELECT @START = dateadd(dd,0, datediff(dd,0,@StartDayTime))
SELECT @END = dateadd(dd,0, datediff(dd,0,@EndDayTime))
SELECT @AddDays = count(*) from (SELECT 
dateadd(dd,ctetally.n-1,@START) date1
from
cteTally
where dateadd(dd,ctetally.n-1,@START) <= @END) s1 where s1.date1 in (select cast( DATEADD(day, t.N - 1, StrDate) as date) as ResultDate 
from HLD1 s join cteTally t on t.N <= DATEDIFF(day, StrDate, EndDate) + 1)
or datepart(dw,s1.date1) in (1,7) 

-- handle end conditions
DECLARE @firstWeekDayInRange datetime, @lastWeekDayInRange datetime;
SET @firstWeekDayInRange = @START
set @lastWeekDayInRange = @END


WHILE @firstWeekDayInRange in (select cast( DATEADD(day, t.N - 1, StrDate) as date) as ResultDate 
from HLD1 s join cteTally t on t.N <= DATEDIFF(day, StrDate, EndDate) + 1)
or datepart(dw,@firstWeekDayInRange) in (1,7) 

BEGIN

SELECT @firstWeekDayInRange =
CASE
WHEN @firstWeekDayInRange in (select cast( DATEADD(day, t.N - 1, StrDate) as date) from HLD1 s join cteTally t on t.N <= DATEDIFF(day, StrDate, EndDate) + 1)
or datepart(dw,@firstWeekDayInRange) in (1,7) 
THEN dateadd(DAY,1,@firstWeekDayInRange)
ELSE @firstWeekDayInRange
END

END 


WHILE @lastWeekDayInRange in (select cast( DATEADD(day, t.N - 1, StrDate) as date) as ResultDate 
from HLD1 s join cteTally t on t.N <= DATEDIFF(day, StrDate, EndDate) + 1)
or datepart(dw,@lastWeekDayInRange) in (1,7) 

BEGIN

SELECT @lastWeekDayInRange =
CASE
WHEN @lastWeekDayInRange in (select cast( DATEADD(day, t.N - 1, StrDate) as date) from HLD1 s join cteTally t on t.N <= DATEDIFF(day, StrDate, EndDate) + 1)
or datepart(dw,@lastWeekDayInRange) in (1,7) 
THEN dateadd(DAY,-1,@lastWeekDayInRange)
ELSE @lastWeekDayInRange
END
END 




-- add one day to answer (to count Friday) if enddate was on a weekend

SELECT @answer = @answer +
CASE
-- triggered if start and end date are on same weekend
WHEN dateDiff(DAY,@firstWeekDayInRange,@lastWeekDayInRange) < 0 THEN (@answer * -1)
-- otherwise count the days and substract 2 days per weekend in between dates
ELSE (DateDiff(DAY, @firstWeekDayInRange, @lastWeekDayInRange) - @AddDays)
END


RETURN @answer
END 

GO

John Cappelletti · Accepted Answer

Perhaps this can help.

Declare @HLD1 table (Calendar varchar(50),StrDate Date,EndDate Date)
Insert Into @HLD1 values
('2016Holidays','1/1/16','1/1/16'),
('2016Holidays','5/30/16','5/30/16'),
('2016Holidays','7/4/16','7/4/16'),
('2016Holidays','9/5/16','9/5/16'),
('2016Holidays','11/24/16','11/25/16'),
('2016Holidays','12/26/16','12/26/16')


Declare @Date1 Date = '2016-06-30 16:30:00'
Declare @Date2 Date = '2016-07-05 08:30:00'

Select DateDiff(DD,@Date1,@Date2)-sum(Excl)
 From (
        Select RetVal,Excl=max(Excl)
          From (
                Select *,Excl=IIF(DatePart(WEEKDAY,RetVal) in (7,1),1,0)  From [dbo].[udf-Create-Range-Date](@Date1,@Date2,'DD',1)
                Union All
                Select RetVal=StrDate,Excl=1 From @HLD1 Where StrDate Between @Date1 and @Date2
               ) A
         Group By RetVal
      ) A

Returns

You can still use your tally table, but I prefer my UDF to create Dynamic Date Ranges

CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)

Returns 
@ReturnVal Table (RetVal datetime)

As
Begin
    With DateTable As (
        Select DateFrom = @DateFrom
        Union All
        Select Case @DatePart
               When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
               When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
               When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
               When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
               When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
               When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
               When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
               When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
               End
        From DateTable DF
        Where DF.DateFrom < @DateTo
    )

    Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)

    Return
End

-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','YY',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','DD',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-31','MI',15) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-02','SS',1)

SQL Function using WHILE takes a long time to run

Answers (2)

Related Questions