Trying to speed up this 'GetWorkingDays' function.

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

Plan.pesession (37.6 kB)
avatar image By Jason A Long 1 asked Oct 18 at 05:35 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

--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
avatar image By Jason A Long 1 answered Oct 18 at 05:49 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.