Get Particular Date from a Month
- Jeffrey Williams wrote:Jeff Moden wrote:
@Jeffrey Williams,
I tested your performance improvements for Peter's code and they're an order of magnitude faster. Nicely done. I've also not been able to find a fault in the output with the bit of testing I've done. This is really cool because of the functionality to work backwards in a month to find the "last" DoW occurrence in a month. Of course, that can also be done by finding the first occurrence in the next month and then simply subtracting 7 days but it's still handy. I don't know if it causes and performance loss in having that functionality because I've not tested for that.
Thank you - the functionality is all Peter's work though, and when it comes to date math in SQL Server I trust his work. I doubt there is any performance hit with using a negative value as @theNthDay - since it all comes down to simple integer math at that point.
I really didn't expect to see any performance improvement though...just restructured and removed the string dates. I can see the string dates improving performance a little...but didn't think it would be that much faster.
You did good. Peter's takes over 4 seconds on a million rows. Yours takes about 380ms. Like I said, an order of magnitude
--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 wrote:
You did good. Peter's takes over 4 seconds on a million rows. Yours takes about 380ms. Like I said, an order of magnitude
Curious - I am wondering where the improvements are coming from...is it just the change from string dates to integer? Or is the change from a derived table to CROSS APPLY?
I guess I have some testing to do 🙂
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Jeffrey Williams wrote:Jeff Moden wrote:
You did good. Peter's takes over 4 seconds on a million rows. Yours takes about 380ms. Like I said, an order of magnitude
Curious - I am wondering where the improvements are coming from...is it just the change from string dates to integer? Or is the change from a derived table to CROSS APPLY?
I guess I have some testing to do 🙂
Heh... and some documentation, please. 😀
--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) -
Are you referring to this function?
https://www.sqltopia.com/algorithms/date-and-time/get-the-nth-weekday-of-any-interval/
I can't see that it should take 4 seconds to run over a million date interval?
N 56°04'39.16"
E 12°55'05.25" - SwePeso wrote:
Are you referring to this function? https://www.sqltopia.com/algorithms/date-and-time/get-the-nth-weekday-of-any-interval/
I can't see that it should take 4 seconds to run over a million date interval?
That's the one, Peter. Here's the code I used. I didn't use SET STATISTICS because it sometimes changes the nature of things when scalar functions are used.
--===== Create a test table that contains random dates and times from 1900-01-01 up to and not
-- including 2100-01-01.
DROP TABLE IF EXISTS #MyHead
;
SELECT TOP 1000000
SomeDateTime = RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'1900','2100')+CONVERT(DATETIME,'1900') --Inherently DATETIME
-- SomeDateTime = CONVERT(DATETIME2(7),RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'1900','2100')+CONVERT(DATETIME,'1900')) --DATETIME2()
-- SomeDateTime = CONVERT(DATE,ABS(CHECKSUM(NEWID())%DATEDIFF(dd,'1900','2100'))+CONVERT(DATETIME,'1900')) --DATE
INTO #MyHead
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO
--===== Peter Larsson's Fuction
DECLARE @BitBucket DATETIME
,@StartDT DATETIME = GETDATE()
SELECT @BitBucket = dbo.fnGetNthWeekdayOfMonth(SomeDateTime,1,1)
FROM #MyHead
;
--===== Display the time
SELECT DurationMS = DATEDIFF(ms,@StartDT,GETDATE())
;--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) -
I get 450 milliseconds when using the algorithm as a scalar function.
I get 1000 milliseconds when using the algorithm as an inline table-valued function.
Using the same 1 000 000 rows table as above.
I used the original function found here
https://weblogs.sqlteam.com/peterl/2009/06/17/how-to-get-the-nth-weekday-of-a-month/
N 56°04'39.16"
E 12°55'05.25" - SwePeso wrote:
I get 450 milliseconds when using the algorithm as a scalar function. I get 1000 milliseconds when using the algorithm as an inline table-valued function.
Using the same 1 000 000 rows table as above.
I used the original function found here https://weblogs.sqlteam.com/peterl/2009/06/17/how-to-get-the-nth-weekday-of-a-month/
Just to confirm - that is the original function I used. The differences are minor - modifying from a derived table to cross apply, changing the string dates to integers and removing one of the sign's (sign of the sign?) and a couple variable changes.
The only real difference would be the string dates to integer dates. And that was done to remove the warnings I was getting for the implicit converts in the execution plans (cardinality estimates).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - SwePeso wrote:
I get 450 milliseconds when using the algorithm as a scalar function. I get 1000 milliseconds when using the algorithm as an inline table-valued function.
Using the same 1 000 000 rows table as above.
I used the original function found here https://weblogs.sqlteam.com/peterl/2009/06/17/how-to-get-the-nth-weekday-of-a-month/
Interesting. I wonder what the differences are in machines/version of SQL Server? I'm getting 4040ms for yours and about 380 for Jeffrey's. I'm running SQL Server 2017 Dev Edition at the latest CU, 12 hyperthreaded processors at 4Mhz, with 32GB of ram (24 allocated to SQL Server) with 2TB of NVME SSDs. This also won't be the first time I've seen something like this... and it confuses the hell out of me every time.
--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) -
I am using an 8 core laptop with 8 GB of ram and 512 GB SSD disk, SQL Server 2019.
I get consistent 1100 ms with Jeffreys cte.
N 56°04'39.16"
E 12°55'05.25" -
No wonder mine is faster. SQL Server managed to parallelize my function. With MAXDOP 1 my function runs in 1600 ms and Jeffreys in 1100 ms.
N 56°04'39.16"
E 12°55'05.25" - SwePeso wrote:
No wonder mine is faster. SQL Server managed to parallelize my function. With MAXDOP 1 my function runs in 1600 ms and Jeffreys in 1100 ms.
I don't have SQL Server 2019 - so I cannot test. I wonder if modifying my version to a scalar function would make any difference as an inline-scalar function on 2019.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Mine is still old-fashioned scalar function.
N 56°04'39.16"
E 12°55'05.25" -
Interesting - earlier you showed that your scalar function ran significantly faster than your inline...I was just curious as to why that would be the case and thought maybe it was because of 2019.
Both versions are very similar...it is very interesting that one was able to use a parallel plan and the other was not. So which one was able to use a parallel plan?
I am thinking the difference is how SQL Server is generating the plan when using the scalar version vs inline version.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - SwePeso wrote:
I am using an 8 core laptop with 8 GB of ram and 512 GB SSD disk, SQL Server 2019.
I get consistent 1100 ms with Jeffreys cte.
I'm thinking that SQL Server 2019 is the difference in that case. The scalar may have automatically gone inline. I don't have 2019 to test that little theory on.
--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) -
You don't need to iterate/loop thru values looking for 'Tuesday', and personally I wouldn't. I find all such code much harder to adjust later, and generally less efficient.
I guess you could put this code in a function, but, for performance reasons, I wouldn't, unless on SQL 2019.
Edit: To adjust to a different week number of month and/or days to advance, just change the cte values, nothing else needs changed. For example, to go from 3nd Fri to following Mon, change cte values to '19000105', 3 and 3.
Finally, note that the code below works under any/all date settings, i.e., no matter what @@DATEFIRST is set to.
;WITH cte_controlling_dates AS (
SELECT '19000102' AS base_date, /*any previous date known to be a Tue (or whatever day) will work*/
2 AS week_number_in_month, /* 2 = 2nd Tue (or whatever day) of month, 3 = 3rd, etc. */
2 AS number_of_days_to_advance /*2 to go from Tue to Thu*/
)
SELECT
date, final_date
FROM ( VALUES /* just sample values, you can instead use GETDATE() or date(s) from any table*/
(CAST('20201130' AS date)), /*your CASE 1 date*/
('20201201'), /*your CASE 2 date*/
('20210901') /*current month*/ ) AS dates(date)
CROSS JOIN cte_controlling_dates
CROSS APPLY (
SELECT DATEADD(DAY, -DATEDIFF(DAY, base_date, DATEFROMPARTS(YEAR(date), MONTH(date), 7 * week_number_in_month)) % 7 +
number_of_days_to_advance, DATEFROMPARTS(YEAR(date), MONTH(date), 7 * week_number_in_month)) AS current_month_date
) AS ca1
CROSS APPLY (
SELECT CASE WHEN current_month_date <= date THEN current_month_date
ELSE DATEADD(DAY, -DATEDIFF(DAY, base_date, DATEFROMPARTS(YEAR(date), MONTH(date) - 1, 7 * week_number_in_month)) % 7 +
number_of_days_to_advance, DATEFROMPARTS(YEAR(date), MONTH(date) - 1, 7 * week_number_in_month)) END AS final_date
) AS ca2SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply