Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Performance Test of New Date Manipulation Functions (SQL Spackle) Expand / Collapse
Author
Message
Posted Thursday, May 10, 2012 12:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:09 AM
Points: 6,580, Visits: 8,856
Comments posted to this topic are about the item Performance Test of New Date Manipulation Functions (SQL Spackle)

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1297609
Posted Thursday, May 10, 2012 1:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 5:14 AM
Points: 1,345, Visits: 981
Hi Wayne,

nice comparison, but I cannot find what you compare the DateAddDateDiff method to.
Could you show the code, please?

Best regards,
Henrik Staun Poulsen
www.stovi.com



Post #1297673
Posted Thursday, May 10, 2012 3:10 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 4:55 AM
Points: 323, Visits: 2,198
Can't say I am surprised by the outcome as everything that is converted to a varchar/string and back is bound to be inefficient.

The datetimeform parts function doesn't have this conversion overhead as it works nativly on dates. On top of this, datetimefromparts obviously is more convenient in date construction then dateadd is, but that is not what is tested here. The speed when working on native dates is virtually identical.

BTW, in some other earlier sparkle article I discovered that datediff had far better optimized special usage cases then some other, more straighforward functions for the job, like datepart.

Here is the releveant link to the code:

http://www.sqlservercentral.com/Forums/FindPost1051435.aspx

I bet that if you replace some of the datepart logic you use to test datetimefromparts, you will see some extra performance. I suspect that using datepart is holding back the datetimefromparts function in this test.
Post #1297701
Posted Thursday, May 10, 2012 4:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 4:55 AM
Points: 323, Visits: 2,198
Silly me, you already linked the article I referred to and in one test (the fastest) had the dateparts stored in the table. This removed any date logic overhead that could taint the performance measurement of the function to be tested.

Very nice!
Post #1297767
Posted Thursday, May 10, 2012 5:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:31 AM
Points: 5,014, Visits: 10,514
Thanks for the article, Wayne.
Very informative and well written!

Along the same line, I recently compared the format function with some other T-SQL and CLR methods here.
Turns out that FORMAT is very powerful, but not fast.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1297783
Posted Thursday, May 10, 2012 6:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 3, 2013 8:29 AM
Points: 1, Visits: 12
Excellent article.
Post #1297841
Posted Thursday, May 10, 2012 7:35 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:35 AM
Points: 3,642, Visits: 72,429
I don't get how DATETIMEFROMPARTS can tell if I want the first day of the quarter, or the first day of a month based on the portion of the article that has

SELECT  SomeDate,
DATETIMEFROMPARTS(ca.Yr, 1, 1, 0, 0, 0, 0) AS [FirstDayOfYear],
DATETIMEFROMPARTS(ca.Yr, ca.Mth, 1, 0, 0, 0, 0) AS [FirstDayOfMonth],
DATETIMEFROMPARTS(ca.Yr, ca.Qtr, 1, 0, 0, 0, 0) AS [FirstDayOfQuarter],
DATETIMEFROMPARTS(ca.Yr, ca.Mth, ca.Dy, ca.Hr, 0, 0, 0) AS StartOfHour,
DATETIMEFROMPARTS(ca.Yr, ca.Mth, ca.Dy, ca.Hr, ca.Mn, 0, 0) AS StartOfMinute,
DATETIMEFROMPARTS(ca.Yr, ca.Mth, ca.Dy, ca.Hr, ca.Mn, ca.Sec, 0) AS StartOfSecond

In the 3rd line you pass in an integer from the Quarter, but every other time it's the month.

What did you do with the quarter line that lets DATETIMEFROMPARTS know that it's using a quarter instead of a month?

I mean if I run

SELECT   DATETIMEFROMPARTS(2012, 3,      1,     0,     0,     0,      0) AS [FirstDayOfQuarter]

Is that 2012-03-01 or is it 2012-07-01 ?




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1297871
Posted Thursday, May 10, 2012 7:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:09 AM
Points: 6,580, Visits: 8,856
mtassin (5/10/2012)
I don't get how DATETIMEFROMPARTS can tell if I want the first day of the quarter, or the first day of a month based on the portion of the article that has

SELECT  SomeDate,
DATETIMEFROMPARTS(ca.Yr, 1, 1, 0, 0, 0, 0) AS [FirstDayOfYear],
DATETIMEFROMPARTS(ca.Yr, ca.Mth, 1, 0, 0, 0, 0) AS [FirstDayOfMonth],
DATETIMEFROMPARTS(ca.Yr, ca.Qtr, 1, 0, 0, 0, 0) AS [FirstDayOfQuarter],
DATETIMEFROMPARTS(ca.Yr, ca.Mth, ca.Dy, ca.Hr, 0, 0, 0) AS StartOfHour,
DATETIMEFROMPARTS(ca.Yr, ca.Mth, ca.Dy, ca.Hr, ca.Mn, 0, 0) AS StartOfMinute,
DATETIMEFROMPARTS(ca.Yr, ca.Mth, ca.Dy, ca.Hr, ca.Mn, ca.Sec, 0) AS StartOfSecond

In the 3rd line you pass in an integer from the Quarter, but every other time it's the month.

What did you do with the quarter line that lets DATETIMEFROMPARTS know that it's using a quarter instead of a month?

I mean if I run

SELECT   DATETIMEFROMPARTS(2012, 3,      1,     0,     0,     0,      0) AS [FirstDayOfQuarter]

Is that 2012-03-01 or is it 2012-07-01 ?


Excellent question Mark. Actually, in the Qtr column in the CROSS APPLY, I use this calculation to determine what the month number is at the beginning of the quarter for the date:
((CEILING(MONTH(dt.SomeDate)/3.0)*3)-2) AS Qtr,

In the example that you posted, it is specifying the third month, thus 2012-03-01.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1297896
Posted Thursday, May 10, 2012 8:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 36,711, Visits: 31,160
As usual, awesome article, Wayne.

It is a bit amazing to me that MS isn't, for some reason, able to do performance wise at the base code level what we're able to do at the "hack" level. I was really shocked when you test the previous row stuff against the Quirky Update. They should spend more time on stuff like making Pivot as useful as it is in Access. On second thought, it's already slow enough. Maybe they should leave it alone.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1297920
Posted Thursday, May 10, 2012 8:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:35 AM
Points: 3,642, Visits: 72,429
WayneS (5/10/2012)
Excellent question Mark. Actually, in the Qtr column in the CROSS APPLY, I use this calculation to determine what the month number is at the beginning of the quarter for the date:
((CEILING(MONTH(dt.SomeDate)/3.0)*3)-2) AS Qtr,

In the example that you posted, it is specifying the third month, thus 2012-03-01.



ah I glanced over that, but missed the implications of it. Which left me thinking you were stuffing in the quarter number, instead of the first month number of a given quarter.

Makes sense, thanks!




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1297944
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse