Post reply

Performance Test of New Date Manipulation Functions (SQL Spackle)

  • May 10, 2012 at 12:04 am

    #255630

    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

  • May 10, 2012 at 1:54 am

    #1485523

    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

  • May 10, 2012 at 3:10 am

    #1485548

    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.

  • May 10, 2012 at 4:56 am

    #1485602

    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!

  • May 10, 2012 at 5:32 am

    #1485616

    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

  • May 10, 2012 at 6:59 am

    #1485669

    Excellent article.

  • May 10, 2012 at 7:35 am

    #1485694

    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]

  • May 10, 2012 at 7:54 am

    #1485717

    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

  • May 10, 2012 at 8:31 am

    #1485736

    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)

  • May 10, 2012 at 8:55 am

    #1485756

    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]

  • May 10, 2012 at 9:04 am

    #1485762

    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

  • May 11, 2012 at 1:28 pm

    #1486599

    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

  • May 15, 2012 at 7:37 pm

    #1488126

    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.

  • July 1, 2012 at 6:22 pm

    #1507577

    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