Home Forums SQL Server 2014 Development - SQL Server 2014 Trying to speed up this 'GetWorkingDays' function. RE: Trying to speed up this 'GetWorkingDays' function.

  • October 19, 2017 at 10:34 pm

    #1964450

    Alan.B - Thursday, October 19, 2017 8:36 PM

    Here's another one for your tests. I think it's a winner 😉

    IF OBJECT_ID('dbo.workdayCalendar') IS NOT NULL
    DROP TABLE dbo.workdayCalendar;

    -- Wide enough range to cover a posible dates; limiting to the years where you have holiday data
    DECLARE @startdate datetime = '20000101', @enddate datetime = '20300101';

    SELECT caldate = ISNULL(caldate, @startdate)
    INTO dbo.workdayCalendar
    FROM
    ( SELECT dateadd(day, n, @startdate)
    FROM dbo.tfn_tally(datediff(day, @startdate, @enddate),0)
    ) c(caldate)
    LEFT JOIN dbo.holiday h ON c.caldate = h.hdate
    WHERE datepart(weekday,c.caldate) BETWEEN 2 AND 6 AND h.hdate IS NULL;
    GO
    CREATE UNIQUE NONCLUSTERED INDEX uq_nc_workdayCalendar ON dbo.workdayCalendar(caldate);
    GO

    -- Logic for your iTVF:
    DECLARE @startdate datetime = '20170101', @enddate datetime = '20170506';

    SELECT count(*)
    FROM dbo.workdayCalendar
    WHERE caldate >= @startdate AND caldate <= @enddate;

    That's a dead ringer for the one that got the whole ball rolling in the first place (even your calendar table... same date range as the one we use). The cost of the count aggregation snowballs and becomes really expensive, really fast, when it's calculating multiple status changes on multiple reports.
    At the end of the day, even simple little, millisecond count aggs add up no matter how much tuning you do, the only way to get past the cost is to get rid of the counts. That's how we ended up with a 60+M row table in the first place...
    It dawned on us that it was stupid to keep paying for the same set of calculations over and over again, when we could do it once and be done with it... so, we decided to pre-calculate every possible date combination in our calendar table. Now all it has to do is a seek on one of two indexes (one on BegDate and one on EndDate, a pick up the value. It still has to do some calculation, because an adjustment has to be done if the BegDate is a workday or not and if the parameters were passed dates only of date & time, but I was never able to see any impact from those calculations. 
    The reason for wanting to dump it... Holiday edits, that are minor on a normal calendar table, turn into a major ordeal when that date affects 10's of thousands of separate calculations. More than a few and it's easier to simply recalculate the whole thing. 
    Then there's the spark that really lit a fire under me... We have a client that has determined that we need to use THEIR holidays when we co the calculations on their stuff... So, now I have two of them! Which, of course, means it's just a matter of time until there are more...

    I'll post what I have tomorrow... Whether I'm done testing & tweaking or not... Hopefully Jeff will put a horse in the race too... That will bring out all the speed freaks.