Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Wayne Sheffield » Performance Test of New Date Manipulation...
Add to briefcase
14 posts, Page 1 of 2
12»»

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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235
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
henrik staun poulsen
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: 2 days ago @ 11:39 PM
Points: 1,133, Visits: 856
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
peter-757102
Posted Thursday, May 10, 2012 3:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:57 AM
Points: 288, Visits: 1,903
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
peter-757102
Posted Thursday, May 10, 2012 4:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:57 AM
Points: 288, Visits: 1,903
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
spaghettidba
Posted Thursday, May 10, 2012 5:32 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:17 AM
Points: 4,804, Visits: 8,074
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.

Get your two-cent-answer quickly
The Spaghetti DBA
Post #1297783
bstudniarz
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, May 10, 2012 7:36 AM
Points: 1, Visits: 11
Excellent article.
Post #1297841
mtassin
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: Today @ 3:23 PM
Points: 3,231, Visits: 64,370
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
WayneS
Posted Thursday, May 10, 2012 7:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 6,370, Visits: 8,235
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
Jeff Moden
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: Today @ 3:06 PM
Points: 32,930, Visits: 26,816
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1297920
mtassin
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: Today @ 3:23 PM
Points: 3,231, Visits: 64,370
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
14 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.