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
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
RETURNWITH
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;
GOThe SET STATISTICS IO,TIME ON;
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
>>>>>>>>>>>>>>>>>>>>>>> test 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#TestData___________________________________________________________________________________________________________000000002BA2'. Scan count 5, logical reads 3600, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times:
CPU time = 6514 ms, elapsed time = 1733 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
>>>>>>>>>>>>> end test 1 <<<>>> test2 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'WorkingDaysPreCalc'. Scan count 0, logical reads 3055039, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TestData___________________________________________________________________________________________________________000000002BA2'. Scan count 1, logical reads 3600, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times:
CPU time = 4766 ms, elapsed time = 4764 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
>>>>>>>>>>>>>>>>>>>>>>> end test 2 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms. -
I have to believe that someone here has the magic go-fast this thing is missing... :crying:
- Jason A. Long - Wednesday, October 18, 2017 6:00 PM
Heh... I just got home from work (9PM). I'm looking. 🙂 Do you have a test table generator?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Wednesday, October 18, 2017 7:08 PM
Yes sir... 1st, Thank you for jumping in! and 2nd...My apologies...I meant to post my table & harness too... It was close to 2:00 am on a worknight when I decided to throw in the towel for the evening...
Here is what I was using to generate dates. It'll come up short of 1M rows because of the IGNORE_DUP_KEY = ON, but it gets pretty close.
IF OBJECT_ID('tempdb..#TestData', 'U') IS NULL
BEGIN -- DROP TABLE #TestData;
CREATE TABLE #TestData (
RN INT NOT NULL,
beg_dt DATETIME NOT NULL,
end_dt DATETIME NOT NULL,
PRIMARY KEY CLUSTERED (beg_dt, end_dt)
WITH (IGNORE_DUP_KEY = ON)
);INSERT #TestData(RN, beg_dt, end_dt)
SELECT
t.n,
MIN(x.DT),
MAX(x.DT)
FROM
dbo.tfn_Tally(1000000, 1) t
CROSS APPLY ( VALUES (CHECKSUM(NEWID()) % 9999) ) r1 (rand_1)
CROSS APPLY ( VALUES (CHECKSUM(NEWID()) % 9999) ) r2 (rand_2)
CROSS APPLY ( VALUES (DATEADD(mi, r1.rand_1, '2015-06-15 12:30:30')),
(DATEADD(mi, r2.rand_2, '2015-06-15 12:30:30')) ) x (DT)
GROUP BY
t.n
END;The test harness... I know your's is better but I couldn't find a copy last night.
The other function in the test is supported by a 60M row table of pre-calculated date ranges, making it impracticable to share. It's wicked fast but but not exactly a joy to maintain./*
SET STATISTICS IO,TIME ON;
SET STATISTICS IO,TIME OFF;
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
*/GO
PRINT('>>>>>>>>>>>>>>>>>>>>>>> test 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<');
GODECLARE @v-2 INT;
SELECT
--td.rn,
--td.beg_dt, DATEPART(dw, td.beg_dt),
--td.end_dt, DATEPART(dw, td.end_dt),
@v = gx.WorkingDays
FROM
#TestData td
CROSS APPLY dbo.tfn_GetWorkingDays_X(td.beg_dt, td.end_dt) gxGO
PRINT('>>>>>>>>>>>>> end test 1 <<<>>> test2 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<');
GODECLARE @v-2 INT;
SELECT
--td.rn,
--td.beg_dt, DATEPART(dw, td.beg_dt),
--td.end_dt, DATEPART(dw, td.end_dt),
@v = g.WorkingDays
FROM
#TestData td
CROSS APPLY dbo.tfn_GetWorkingDays(td.beg_dt, td.end_dt) gGO
PRINT('>>>>>>>>>>>>>>>>>>>>>>> end test 2 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<');
GO -
I don't know what dbo.tfn_Tally(1000000, 1) does. Does it start at 1 and count to a million with an increment of 1?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Wednesday, October 18, 2017 9:13 PM
Oh.. Sorry, I thought you had seen my version of tally number function before... Not too dissimilar to one I've seen you use before... mine just doesn't have option to change the increments (yet).
And... Yes, you're absolutely correct... starts at the number in the 2nd parameter position and increments by 1 until it reaches the number in the 1st parameter.SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.tfn_Tally
/* ============================================================================
07/20/2017 JL, Created. Capable of creating a sequense of rows
ranging from -10,000,000,000,000,000 to 10,000,000,000,000,000
============================================================================ */
(
@NumOfRows BIGINT,
@StartWith BIGINT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)), -- 10 rows
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b), -- 100 rows
cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b), -- 10,000 rows
cte_n4 (n) AS (SELECT 1 FROM cte_n3 a CROSS JOIN cte_n3 b), -- 100,000,000 rows
cte_Tally (n) AS (
SELECT TOP (@NumOfRows)
(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) + @StartWith
FROM
cte_n4 a CROSS JOIN cte_n4 b -- 10,000,000,000,000,000 rows
)
SELECT
t.n
FROM
cte_Tally t;
GO -
I think I may be on to something here... I need test further before opening my big mouth, but so far...
Single threaded...
With a "MakeParallel" function added to latest "X2" version...
Not sure why, but the numbers are slightly better when run from SSMS than Sentry Plan Explorer...
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 53 ms.SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 5000 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
>>>>>>>>>>>>>>>>>>>>>>> test 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 16 ms, elapsed time = 25 ms.
Table '#TestData___________________________________________________________________________________________________________000000002C55'. Scan count 5, logical reads 3609, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times:
CPU time = 6562 ms, elapsed time = 2803 ms. <-- the first "X" version posted in the op... goes parallel on it's own...
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
>>>>>>>>>>>>> end test 1 <<<>>> test 2 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 47 ms, elapsed time = 64 ms.
Table '#TestData___________________________________________________________________________________________________________000000002C55'. Scan count 5, logical reads 3609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times:
CPU time = 5313 ms, elapsed time = 2081 ms. <-- the latest "X2" that's making me smile... w/ MakeParrallel
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
>>>>>>>>>>>>>> end test 2 <<<>>> test 3<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 31 ms, elapsed time = 92 ms.
Table 'WorkingDaysPreCalc'. Scan count 0, logical reads 3126482, physical reads 1254, read-ahead reads 1488, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TestData___________________________________________________________________________________________________________000000002C55'. Scan count 5, logical reads 3609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times:
CPU time = 5218 ms, elapsed time = 5094 ms. <-- my old war horse... also w/ the same MakeParallel
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
>>>>>>>>>>>>>>>>>>>>>>> end test 3 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.--===============================================================================================================================================
same test but with everyone off the steroids...SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 75 ms.SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 5000 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
>>>>>>>>>>>>>>>>>>>>>>> test 1 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 16 ms, elapsed time = 26 ms.
Table '#TestData___________________________________________________________________________________________________________000000002C55'. Scan count 5, logical reads 3609, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times:
CPU time = 6125 ms, elapsed time = 2728 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
>>>>>>>>>>>>> end test 1 <<<>>> test 2 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 16 ms, elapsed time = 20 ms.
Table '#TestData___________________________________________________________________________________________________________000000002C55'. Scan count 1, logical reads 3609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times:
CPU time = 4890 ms, elapsed time = 4922 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
>>>>>>>>>>>>>> end test 2 <<<>>> test 3<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 228 ms.
Table 'WorkingDaysPreCalc'. Scan count 0, logical reads 3131776, physical reads 1279, read-ahead reads 1288, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TestData___________________________________________________________________________________________________________000000002C55'. Scan count 1, logical reads 3609, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times:
CPU time = 4750 ms, elapsed time = 9824 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
>>>>>>>>>>>>>>>>>>>>>>> end test 3 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms. -
I was able to get parallelism out of it by simplifying the function instead of the "Make Parallel" thing but now I'm dealing with an implicit cast that's making mine 2 seconds slower. I'll work on it but it's 2:15... I'm going to bed.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jason A. Long - Wednesday, October 18, 2017 6:00 PM
Jason, I don't think you'll get around what looks like a Cartesian join, because for each date pair, you have to count the rows between them in your holiday table.
You can however simplify the weekdays calculation, like this:SELECT
WorkingDays = (DATEDIFF(dd, @BegDate, @EndDate) + 1)
- (DATEDIFF(wk, @BegDate, @EndDate) * 2)
- (CASE WHEN DATENAME(dw, @BegDate) = 'Sunday' THEN 1 ELSE 0 END)
- (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
My gut says a calendar table in your database with a bit column for holiday or not and indexed on that column then by the date, could have rows between @BegDate and @EndDate counted rather quickly. Jeff? Jason? Chris?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - ChrisM@Work - Thursday, October 19, 2017 9:31 AM
I already did... I just haven't had the chance to finish testing. Plus, I'm really curious to see what Jeff comes up with.
- sgmunson - Thursday, October 19, 2017 11:38 AM
Actually, that's what got replaced by what I'm try to replace now...
To give an idea... Here is sys.dm_db_index_usage_stats for that database, sorted by use (ORDER BY ius.user_seeks + ius.user_scans DESC;
The top two, by wide margins, are calendar tables...
They're fast but not fast enough...
- sgmunson - Thursday, October 19, 2017 11:38 AM
I tried that and was convinced that it would be faster. I'm out of time for now but my initial testing was rather interesting...
Holiday table with a unique nonclustered index:
CREATE TABLE dbo.holiday(hdate DATETIME NOT NULL UNIQUE CLUSTERED);
GO
CREATE UNIQUE NONCLUSTERED INDEX uq_nc_holiday ON dbo.holiday(hdate);
GOINSERT dbo.holiday
SELECT *
FROM
(VALUES (CAST('20000529' AS DATE)),
('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);The function
CREATE FUNCTION dbo.tfn_GetWorkingDays_X_ajb -- changing to date
(
@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)
)
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
CROSS JOIN
(
SELECT COUNT(1)
FROM dbo.holiday h
WHERE h.hdate >= @BegDate AND h.hdate <= @EndDate
) h(holidays)
GOPerformance test
Couple things to note:
1. I'm only comparing what I threw together to tfn_GetWorkingDays_X.
2. My test compares the two functions when running in serial (maxdop 1) and parallel (using traceflag 8649). In Production I would use make_parallel; I prefer the traceflag when testing as it makes for a more readable execution plan IMO.SET STATISTICS IO,TIME ON;
GO
PRINT('>>>>>>>>>>>>>>>>>> tfn_GetWorkingDays_X (serial) <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<');
GO
DECLARE @v-2 INT;
SELECT
--td.rn,
--td.beg_dt, DATEPART(dw, td.beg_dt),
--td.end_dt, DATEPART(dw, td.end_dt),
@v-2 = gx.WorkingDays
FROM #TestData td
CROSS APPLY dbo.tfn_GetWorkingDays_X(td.beg_dt, td.end_dt) gx
OPTION (MAXDOP 1);
GO
PRINT('>>>>>>>>>>>>>>>>>> tfn_GetWorkingDays_X (parallel) <<<<<<<<<<<<<<<<<<<<<<<<<<<<<');
GODECLARE @v-2 INT;
SELECT
--td.rn,
--td.beg_dt, DATEPART(dw, td.beg_dt),
--td.end_dt, DATEPART(dw, td.end_dt),
@v-2 = gx.WorkingDays
FROM #TestData td
CROSS APPLY dbo.tfn_GetWorkingDays_X(td.beg_dt, td.end_dt) gx
OPTION (querytraceon 8649);
GOSET STATISTICS IO,TIME ON;
GO
PRINT('>>>>>>>>>>>>>>>>>> tfn_GetWorkingDays_X_ajb (serial) <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<');
GODECLARE @v-2 INT;
SELECT
--td.rn,
--td.beg_dt, DATEPART(dw, td.beg_dt),
--td.end_dt, DATEPART(dw, td.end_dt),
@v-2 = gx.WorkingDays
FROM #TestData td
CROSS APPLY dbo.tfn_GetWorkingDays_X_ajb(td.beg_dt, td.end_dt) gx
OPTION (MAXDOP 1);
GO
PRINT('>>>>>>>>>>>>>>>>>> tfn_GetWorkingDays_X (parallel) <<<<<<<<<<<<<<<<<<<<<<<<<<<<<');
GODECLARE @v-2 INT;
SELECT
--td.rn,
--td.beg_dt, DATEPART(dw, td.beg_dt),
--td.end_dt, DATEPART(dw, td.end_dt),
@v-2 = gx.WorkingDays
FROM #TestData td
CROSS APPLY dbo.tfn_GetWorkingDays_X_ajb(td.beg_dt, td.end_dt) gx
OPTION (querytraceon 8649);
GO
SET STATISTICS IO,TIME OFF;Results
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
>>>>>>>>>>>>>>>>>> tfn_GetWorkingDays_X (serial) <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 15 ms, elapsed time = 19 ms.
Table '#TestData___________________________________________________________________________________________________________000000000062'. Scan count 1, logical reads 5163, physical reads 0, read-ahead reads 2689, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times:
CPU time = 4750 ms, elapsed time = 4801 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
>>>>>>>>>>>>>>>>>> tfn_GetWorkingDays_X (parallel) <<<<<<<<<<<<<<<<<<<<<<<<<<<<<SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 6 ms.
Table '#TestData___________________________________________________________________________________________________________000000000062'. Scan count 9, logical reads 5163, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times:
CPU time = 8409 ms, elapsed time = 1121 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
>>>>>>>>>>>>>>>>>> tfn_GetWorkingDays_X_ajb (serial) <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 11 ms.
Table 'holiday'. Scan count 997512, logical reads 1995024, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TestData___________________________________________________________________________________________________________000000000062'. Scan count 1, logical reads 5163, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times:
CPU time = 1984 ms, elapsed time = 1982 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
>>>>>>>>>>>>>>>>>> tfn_GetWorkingDays_X (parallel) <<<<<<<<<<<<<<<<<<<<<<<<<<<<<SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'holiday'. Scan count 997512, logical reads 1995024, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TestData___________________________________________________________________________________________________________000000000062'. Scan count 9, logical reads 5163, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times:
CPU time = 10265 ms, elapsed time = 1527 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.The one that uses the persisted calendar table (I'll call ajb for short) is blows the doors off the original when running in serial but is roughly 40% slower when they both run in parallel. I'm running this on my laptop which has 8 logical CPUs. In that sense the ajb version is a little better as it gets the job done in ~2 seconds using 1 cpu vs ~1 second using 8 cpus. The ajb version, however, does produce a boat load of reads.
No clear winner here - just some food for thought.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
- Alan.B - Thursday, October 19, 2017 12:43 PM
Thank you Alen. I'll definitely add your code to my tests. I would like to get away from using base tables if possible but I will go with the solution that moves the fastest while consuming the least cpu / memory / io.
I've been slammed all day today and haven't time to play at all. I will say that the solution I found last night actually yielded a trivial plan on the date list, before combining it with the rest of the function code and the only thing that's beaten it (so far) the the origin "X" version, but even then, only when it's parallel vs single thread.
I'll try to post the updated code this evening if tonight tests continue to go well... I won't say what it is right now, but you'll crack up when you see it.. -
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;"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
Viewing 15 posts - 1 through 15 (of 92 total)
You must be logged in to reply to this topic. Login to reply