Performance Test of New Date Manipulation Functions (SQL Spackle)

  • 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
    Author - SQL Server T-SQL Recipes


    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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

    http://www.stovi.com

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

  • 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!

  • 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

  • Excellent article.

  • 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[/url]
    For tips on how to post your problems[/url]

  • 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
    Author - SQL Server T-SQL Recipes


    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

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

    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)

  • 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[/url]
    For tips on how to post your problems[/url]

  • Jeff Moden (5/10/2012)


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

    Thanks Jeff.

    One thing to keep in mind is that these functions are not designed to truncate parts of a date. (Well, maybe Format is.) Everything else going on here is using existing functions to truncate a date down to a specific part. I'm sure that if MS ever builds a "TruncateDate" function, it will be fast (though internally it might just do the dateadd/datediff thing?).

    So, what is being tested is utilizing one or more functions to accomplish a specific task which these functions were not explicitly designed to perform, but they might be used to accomplish the task. DateAdd/DateDiff - neither is designed for truncating a datetime value. DateTimeFromParts is designed to build a datetime from values, not to truncate. Format could be argued that it is designed to do so, but I see it more as a report formatting tool since it doesn't leave the result in the original data type.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Terrific article, Wayne.

    It would be nice to see a comparison using datetime2, datetimeoffset, date (for relevant truncations only) and time (again for relevant truncations only) as well as datetime. Of course there is hordes of legacy data that uses the old datatype, which means including it is essential, but it's a pity to leave the others out. Maybe doing it for smalldatetime too would also be useful.

    Tom

  • Thanks for the post Wayne, I will point all developers of convoluted datetime function hacks to this article for education.

    I always find simplicity in code to be a beautiful thing, can I make a small suggestion on the DateAdd function you use?

    We use

    DATEADD(MONTH, DATEDIFF(MONTH, 0, SomeDate), 0) -- as inspired by Rob Farley

    and make use of the implicit conversion of 0 to a base date. We also use the value to add offsets to the date calculation,

    so I'm thinking the new functionality may well go unused here.

  • craig.watson (5/15/2012)


    Thanks for the post Wayne, I will point all developers of convoluted datetime function hacks to this article for education.

    I always find simplicity in code to be a beautiful thing, can I make a small suggestion on the DateAdd function you use?

    We use

    DATEADD(MONTH, DATEDIFF(MONTH, 0, SomeDate), 0) -- as inspired by Rob Farley

    and make use of the implicit conversion of 0 to a base date. We also use the value to add offsets to the date calculation,

    so I'm thinking the new functionality may well go unused here.

    This does work, but only for the DATETIME and SMALLDATETIME data types. For the newer date data types introduced in SQL 2008, this will fail. In order to be consistent, I use date strings. And lately, I use ISO date strings to avoid date conversion issues. So, I end up with:

    DATEADD(MONTH, DATEDIFF(MONTH, '1900-01-01T00:00:00', SomeDate), '1900-01-01T00:00:00')

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply