Trying to speed up this 'GetWorkingDays' function.

Jason A Long 2017-10-18 05:35:02

I'm trying to redesign one of our working days functions. The idea is to calculate weekends (easy enough) but holidays are trickier. The current idea is to simply embed the hard coded date values in a table expression and filter the dates with the input parameters.

The problem is that the table expression (with 209 values) foms a full cartesian product with the outer query. So, against a 1 million row test table, it's blowing up to 209 million rows before hitting the filter.

I'm also considering the possibility that I'm thinking about it all wrong… Considering the fact that it gets a parrallel plan sooner than the reffernce function and goes from being ~1/2 as fast as the other to being nearly twice as fast.

At this point I'm hopeing that someone with a fresh set of eyes get me pointed in the right direction.

Thank you in advance,
Jason

Jason A Long 2017-10-18 05:49:07
–Here is the tsql for the function…

SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO
CREATE FUNCTION dbo.tfn_GetWorkingDays_X
/* =============================================================================
10/17/2017 JL, Created: Completly in memory does not need the WorkingDaysPreCalc    
============================================================================= */
(
    @BegDate DATETIME,
    @EndDate DATETIME
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN 
 
    WITH
        cte_weekend_count AS (
            SELECT 
                weekend_days = CASE 
                                WHEN dp.beg_daywk = 1 AND dp.end_daywk = 7 THEN (dp.weeks_diff * 2) + 1
                                WHEN dp.beg_daywk = 7 AND dp.end_daywk = 7 THEN (dp.weeks_diff * 2)
                                WHEN dp.beg_daywk = 7 THEN (dp.weeks_diff * 2) - 1
                                WHEN dp.end_daywk = 7 THEN (dp.weeks_diff * 2) + 1
                                ELSE dp.weeks_diff * 2
                            END
            FROM 
                ( VALUES (DATEDIFF(WEEK, DATEADD(DAY, DATEDIFF(DAY, 0, @BegDate), 0), DATEADD(DAY, DATEDIFF(DAY, 0, @EndDate), 0)), DATEPART(dw, @BegDate), DATEPART(dw, @EndDate) ) 
                ) dp ( weeks_diff, beg_daywk, end_daywk )
            ),
        cte_holiday_count AS (
            SELECT
                holidays = COUNT(1) 
            FROM 
                (VALUES (CAST('20000529' AS DATETIME)), 
                    ('20000704'), ('20000904'), ('20001123'), ('20001124'), ('20001225'), ('20010101'), ('20010528'), ('20010704'), ('20010903'), ('20011122'),
                    ('20011123'), ('20011225'), ('20020101'), ('20020527'), ('20020704'), ('20020902'), ('20021128'), ('20021129'), ('20021225'), ('20030101'),
                    ('20030526'), ('20030704'), ('20030901'), ('20031127'), ('20031128'), ('20031225'), ('20040101'), ('20040531'), ('20040705'), ('20040906'),
                    ('20041125'), ('20041126'), ('20041224'), ('20041231'), ('20050530'), ('20050704'), ('20050905'), ('20051124'), ('20051125'), ('20051226'),
                    ('20060102'), ('20060529'), ('20060704'), ('20060904'), ('20061123'), ('20061124'), ('20061225'), ('20070101'), ('20070528'), ('20070704'),
                    ('20070903'), ('20071122'), ('20071123'), ('20071225'), ('20080101'), ('20080526'), ('20080704'), ('20080901'), ('20081127'), ('20081128'),
                    ('20081225'), ('20090101'), ('20090525'), ('20090703'), ('20090907'), ('20091126'), ('20091127'), ('20091225'), ('20100101'), ('20100531'),
                    ('20100705'), ('20100906'), ('20101125'), ('20101126'), ('20101224'), ('20101231'), ('20110530'), ('20110704'), ('20110905'), ('20111124'),
                    ('20111125'), ('20111226'), ('20120102'), ('20120528'), ('20120704'), ('20120903'), ('20121122'), ('20121123'), ('20121225'), ('20130101'),
                    ('20130527'), ('20130704'), ('20130902'), ('20131128'), ('20131129'), ('20131225'), ('20140101'), ('20140526'), ('20140704'), ('20140901'),
                    ('20141127'), ('20141128'), ('20141225'), ('20150101'), ('20150525'), ('20150703'), ('20150907'), ('20151126'), ('20151127'), ('20151225'),
                    ('20160101'), ('20160530'), ('20160704'), ('20160905'), ('20161124'), ('20161125'), ('20161226'), ('20170102'), ('20170529'), ('20170704'),
                    ('20170904'), ('20171123'), ('20171124'), ('20171225'), ('20180101'), ('20180528'), ('20180704'), ('20180903'), ('20181122'), ('20181123'),
                    ('20181225'), ('20190101'), ('20190527'), ('20190704'), ('20190902'), ('20191128'), ('20191129'), ('20191225'), ('20200101'), ('20200525'),
                    ('20200703'), ('20200907'), ('20201126'), ('20201127'), ('20201225'), ('20210101'), ('20210531'), ('20210705'), ('20210906'), ('20211125'),
                    ('20211126'), ('20211224'), ('20211231'), ('20220530'), ('20220704'), ('20220905'), ('20221124'), ('20221125'), ('20221226'), ('20230102'),
                    ('20230529'), ('20230704'), ('20230904'), ('20231123'), ('20231124'), ('20231225'), ('20240101'), ('20240527'), ('20240704'), ('20240902'),
                    ('20241128'), ('20241129'), ('20241225'), ('20250101'), ('20250526'), ('20250704'), ('20250901'), ('20251127'), ('20251128'), ('20251225'),
                    ('20260101'), ('20260525'), ('20260703'), ('20260907'), ('20261126'), ('20261127'), ('20261225'), ('20270101'), ('20270531'), ('20270705'),
                    ('20270906'), ('20271125'), ('20271126'), ('20271224'), ('20271231'), ('20280529'), ('20280704'), ('20280904'), ('20281123'), ('20281124'),
                    ('20281225'), ('20290101'), ('20290528'), ('20290704'), ('20290903'), ('20291122'), ('20291123'), ('20291225')
                ) h (holiday)
            WHERE
                    h.holiday >= @BegDate 
                    AND h.holiday <= @EndDate
            )
SELECT 
    WorkingDays = DATEDIFF(DAY, DATEADD(DAY, DATEDIFF(DAY, 0, @BegDate), 0), DATEADD(DAY, DATEDIFF(DAY, 0, @EndDate), 0)) - (w.weekend_days + h.holidays)
FROM
    cte_weekend_count w
    JOIN cte_holiday_count h
        ON 1 = 1;
GO