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 ««123»»

HHMMSS int field to human-friendly time? Expand / Collapse
Author
Message
Posted Sunday, January 8, 2012 9:24 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:23 AM
Points: 9,928, Visits: 11,196
Jeff Moden (1/8/2012)
Michael Valentine Jones (1/8/2012)
The overhead of the function call can have a large impact on the results.

Understood and agreed... that's precisely the reason I posted such a test... to show just how bad using a MS provided scalar function can be when compared to simple in-line code. Speaking of which, if you'd like to convert your code to an in-line Table Valued Function, I'd be happy to include that in the testing. Unless I'm terribly mistaken, you won't see much of a difference using such an iTVF.

I think Michael was proposing that the test could be made fairer by:

  • Converting the MS function to in-line; or
  • Converting Michael's code to a scalar function




  • Paul White
    SQL Server MVP
    SQLblog.com
    @SQL_Kiwi
    Post #1232189
    Posted Monday, January 9, 2012 6:17 AM


    SSC-Dedicated

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

    Group: General Forum Members
    Last Login: Yesterday @ 8:51 PM
    Points: 35,606, Visits: 32,190
    I agree that the MS function should have been written as an iTVF instead of a scalar UDF. Heh... that was a part of the point I was trying to make with my last test. You just can't use these things blindly.

    Shifting gears to how it should have been done (as both Michael and Paul have recommended), changing the MS code to an iTVF will certainly solve the major portion of the performance problem but, as Michael alluded to in his original post on this thread, using character-based conversions for date/time manipulation is still a lot slower (twice as slow on my ol' box).

    Here are the two iTVF's... (Michael's code and MS' code)

    CREATE FUNCTION dbo.IntsToDate
    (
    @Date integer,
    @Time integer
    )
    RETURNS TABLE WITH SCHEMABINDING
    AS RETURN
    SELECT FullDateTime =
    -- convert date
    dateadd(dd,((@Date)%100)-1,
    dateadd(mm,((@Date)/100%100)-1,
    dateadd(yy,(nullif(@Date,0)/10000)-1900,0)))+
    -- convert time
    dateadd(ss,@Time%100,
    dateadd(mi,(@Time/100)%100,
    dateadd(hh,nullif(@Time,0)/10000,0)))
    ;
    CREATE FUNCTION dbo.agent_datetime_inline
    (
    @Date integer,
    @Time integer
    )
    RETURNS TABLE WITH SCHEMABINDING
    AS RETURN
    SELECT
    CONVERT(datetime,
    CONVERT(nvarchar(4), @Date/ 10000) + N'-' +
    CONVERT(nvarchar(2),(@Date % 10000)/100) + N'-' +
    CONVERT(nvarchar(2), @Date % 100) + N' ' +
    CONVERT(nvarchar(2), @Time / 10000) + N':' +
    CONVERT(nvarchar(2),(@Time % 10000)/100) + N':' +
    CONVERT(nvarchar(2), @Time % 100),
    120) AS date_time

    GO


    Here's the modified test harness...

    --=====================================================================================================================
    -- Do the test using the solutions found so far for converting Integer-based Dates and Times to DATETIME values.
    -- To take display times out of the picture, all results are dumped to a "bit-bucket" variable.
    -- RUN THIS TEST WITH SQL PROFILER RUNNING TO SEE THE PERFORMANCE DIFFERENCES.
    -- Don't use SET STATISTICS TIME ON for this test because it really makes the MS code suffer.
    --=====================================================================================================================
    GO
    --===== Michael's Solution ============================================================================================
    --===== Declare the "bit-bucket" variable
    DECLARE @BitBucket DATETIME;

    --===== Run the test
    SELECT @BitBucket = dt.FullDateTime
    FROM #TestTable t
    CROSS APPLY dbo.IntsToDate(next_run_date,next_run_time) dt
    ;
    GO
    --===== MS Code "in-line" =============================================================================================
    --===== Declare the "bit-bucket" variable
    DECLARE @BitBucket DATETIME;

    --===== Run the test
    SELECT @BitBucket = dt.date_time
    FROM #TestTable t
    CROSS APPLY dbo.agent_datetime_inline(next_run_date,next_run_time) dt
    ;
    GO


    Here're the results using the previously provided test data...


    --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 Attachments 
    Integer DateTime Race 02.gif (145 views, 5.22 KB)
    Post #1232365
    Posted Monday, January 9, 2012 6:46 AM


    SSCrazy Eights

    SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

    Group: General Forum Members
    Last Login: Yesterday @ 6:23 AM
    Points: 9,928, Visits: 11,196
    Just for interest's sake, here's the in-line function written to use SQL Server 2012:

    CREATE FUNCTION dbo.agent_datetime_inline
    (
    @Date integer,
    @Time integer
    )
    RETURNS TABLE WITH SCHEMABINDING
    AS RETURN
    SELECT
    DATETIMEFROMPARTS
    (
    @Date / 10000,
    @Date / 100 % 100,
    @Date % 100,
    @Time / 10000,
    @Time / 100 % 100,
    @Time % 100,
    0
    ) AS date_time

    Test results using Jeff's rig:

    Michael's code: 1155ms
    New function: 670ms




    Paul White
    SQL Server MVP
    SQLblog.com
    @SQL_Kiwi
    Post #1232382
    Posted Monday, January 9, 2012 7:08 AM


    SSCrazy Eights

    SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

    Group: General Forum Members
    Last Login: Yesterday @ 6:23 AM
    Points: 9,928, Visits: 11,196
    And, just to complete the picture, here's a CLR scalar function (not in-line!):

    CREATE ASSEMBLY [DateTimeExtensions]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030024F30A4F0000000000000000E00002210B010800000A000000060000000000009E280000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000442800005700000000400000C803000000000000000000000000000000000000006000000C0000008C2700001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000A408000000200000000A000000020000000000000000000000000000200000602E72737263000000C80300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C00000000600000000200000010000000000000000000000000000040000042000000000000000000000000000000008028000000000000480000000200050094200000F80600000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133007002F00000001000011000220102700005B021F645B1F645D021F645D0320102700005B031F645B1F645D031F645D730F00000A0A2B00062A1E02281000000A2A0042534A4201000100000000000C00000076322E302E35303732370000000005006C000000E0010000237E00004C0200009402000023537472696E677300000000E00400000800000023555300E8040000100000002347554944000000F80400000002000023426C6F620000000000000002000001471502000900000000FA2533001600000100000011000000020000000200000002000000100000000C00000001000000010000000200000000000A00010000000000060046003F0006004D003F00060091007F000600A8007F000600C5007F000600E4007F000600FD007F00060016017F00060031017F0006004C017F00060084016501060098017F000600C401B1013700D801000006000702E70106002702E7010A007F02640200000000010000000000010001000100100021000000050001000100502000000000960056000A0001008B200000000086186F0011000300000001007500000002007A0019006F00150021006F00150029006F00150031006F00150039006F00150041006F00150049006F00150051006F00150059006F001A0061006F00150069006F001F0079006F00250081006F00110089006F00110011006F00720109006F001100200073002A002E002B0081012E00130099012E001B0099012E0023009F012E000B0081012E003300AE012E003B0099012E004B0099012E005B00CF012E006300D8012E006B00E1017C01048000000100000028113A15000000000000450200000200000000000000000000000100360000000000020000000000000000000000010058020000000000000000003C4D6F64756C653E004461746554696D65457874656E73696F6E732E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A656374004461746554696D65004461746554696D6546726F6D496E74656765725061727473002E63746F7200446174650054696D650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004461746554696D65457874656E73696F6E730053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650000032000000000002D4C33179D0E4D4BAAE00759A4F1A8860008B77A5C561934E0890600021109080803200001042001010E042001010205200101113904200101088146010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054020F497344657465726D696E6973746963015402094973507265636973650154557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D4461746141636365737300000000092006010808080808080407011109170100124461746554696D65457874656E73696F6E7300000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313200000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010000000024F30A4F000000000200000099000000A8270000A80900005253445336FDC6FA984ACC49ACCF9F555F663F9403000000633A5C75736572735C7061756C2077686974655C646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C4461746554696D65457874656E73696F6E735C4461746554696D65457874656E73696F6E735C6F626A5C44656275675C4461746554696D65457874656E73696F6E732E706462000000006C28000000000000000000008E280000002000000000000000000000000000000000000000000000802800000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000006C03000000000000000000006C0334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001003A152811000001003A1528113F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004CC020000010053007400720069006E006700460069006C00650049006E0066006F000000A8020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F00660074000000500013000100460069006C0065004400650073006300720069007000740069006F006E00000000004400610074006500540069006D00650045007800740065006E00730069006F006E007300000000003C000E000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003300390032002E003500340033003400000050001700010049006E007400650072006E0061006C004E0061006D00650000004400610074006500540069006D00650045007800740065006E00730069006F006E0073002E0064006C006C00000000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F006600740020003200300031003200000000005800170001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000004400610074006500540069006D00650045007800740065006E00730069006F006E0073002E0064006C006C0000000000480013000100500072006F0064007500630074004E0061006D006500000000004400610074006500540069006D00650045007800740065006E00730069006F006E0073000000000040000E000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003300390032002E003500340033003400000044000E00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003300390032002E0035003400330034000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000A03800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    WITH PERMISSION_SET = SAFE
    GO
    CREATE FUNCTION dbo.DateTimeFromIntegerParts
    (
    @Date integer,
    @Time integer
    )
    RETURNS datetime
    WITH EXECUTE AS CALLER
    AS EXTERNAL NAME [DateTimeExtensions].[UserDefinedFunctions].[DateTimeFromIntegerParts]
    GO

    Called as so:
    SELECT 
    dbo.DateTimeFromIntegerParts
    (
    tt.next_run_date,
    tt.next_run_time
    )
    FROM #TestTable AS tt

    Test results using Jeff's rig:

    Michael's code: 1155ms
    CLR function: 859ms

    Source code:
    using System;
    using Microsoft.SqlServer.Server;

    public partial class UserDefinedFunctions
    {
    [SqlFunction
    (
    DataAccess = DataAccessKind.None,
    IsDeterministic = true,
    IsPrecise = true,
    SystemDataAccess = SystemDataAccessKind.None
    )
    ]
    public static DateTime DateTimeFromIntegerParts(int Date, int Time)
    {
    return new DateTime
    (
    Date / 10000,
    Date / 100 % 100,
    Date % 100,
    Time / 10000,
    Time / 100 % 100,
    Time % 100
    );
    }
    };





    Paul White
    SQL Server MVP
    SQLblog.com
    @SQL_Kiwi
    Post #1232398
    Posted Monday, January 9, 2012 9:11 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: Yesterday @ 3:34 PM
    Points: 3,109, Visits: 11,516
    I simplified my original code to eliminate three DATEADD function calls and one NULLIF function call, so it might run a bit faster.

    select
    next_run_date ,
    next_run_time ,
    NEXT_RUN_DATETIME =
    dateadd(mm,((next_run_date)/100%100)-1,
    dateadd(yy,(nullif(next_run_date,0)/10000)-1900,
    dateadd(ss,
    -- Seconds
    (next_run_time%100)+
    -- Minutes
    (((next_run_time/100)%100)*60)+
    -- Hours
    ((next_run_time/10000)*3600)+
    -- Days
    (((next_run_date)%100)-1)*86400
    ,0)))
    from
    msdb.dbo.sysjobschedules AS s


    next_run_date next_run_time NEXT_RUN_DATETIME
    ------------- ------------- -----------------------
    20120109 170000 2012-01-09 17:00:00.000
    20120109 170000 2012-01-09 17:00:00.000
    20120204 20000 2012-02-04 02:00:00.000
    20100718 83033 2010-07-18 08:30:33.000
    20120110 100 2012-01-10 00:01:00.000
    20120114 30000 2012-01-14 03:00:00.000
    20120121 23000 2012-01-21 02:30:00.000
    20100821 142000 2010-08-21 14:20:00.000
    0 0 NULL
    20120109 105000 2012-01-09 10:50:00.000

    20091104 100000 2009-11-04 10:00:00.000
    20120115 200 2012-01-15 00:02:00.000
    0 0 NULL
    20120110 90000 2012-01-10 09:00:00.000
    20120115 150000 2012-01-15 15:00:00.000
    20120110 30000 2012-01-10 03:00:00.000
    Post #1232541
    Posted Monday, January 9, 2012 9:22 AM


    SSCrazy Eights

    SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

    Group: General Forum Members
    Last Login: Yesterday @ 6:23 AM
    Points: 9,928, Visits: 11,196
    Michael Valentine Jones (1/9/2012)
    I simplified my original code to eliminate three DATEADD function calls and one NULLIF function call, so it might run a bit faster.

    It seems a little slower on my instances; fastest run out of 10 was 1210ms (versus 1155ms previously).




    Paul White
    SQL Server MVP
    SQLblog.com
    @SQL_Kiwi
    Post #1232555
    Posted Monday, January 9, 2012 2:16 PM
    SSC Eights!

    SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

    Group: General Forum Members
    Last Login: Thursday, May 1, 2014 7:26 AM
    Points: 908, Visits: 2,804
    I'm using one of Michael's versions and I'm getting NULL values when there is a date and the time is 0.

    i.e.:
    last_run_date last_run_time LastRunDateTime
    20081118 0 NULL

    this one:
    CREATE FUNCTION [dbo].[IntsToDate]
    (
    @Date integer,
    @Time integer
    )
    RETURNS TABLE WITH SCHEMABINDING
    AS RETURN
    SELECT FullDateTime =
    -- convert date
    dateadd(dd,((@Date)%100)-1,
    dateadd(mm,((@Date)/100%100)-1,
    dateadd(yy,(nullif(@Date,0)/10000)-1900,0)))+
    -- convert time
    dateadd(ss,@Time%100,
    dateadd(mi,(@Time/100)%100,
    dateadd(hh,nullif(@Time,0)/10000,0)))
    ;

    (I'm reasonably good with T-SQL but when you start getting into this date/time and math stuff I tend to go a bit cross-eyed...)
    Post #1232805
    Posted Monday, January 9, 2012 6:47 PM
    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: Yesterday @ 3:34 PM
    Points: 3,109, Visits: 11,516
    Pam Brisjar (1/9/2012)
    I'm using one of Michael's versions and I'm getting NULL values when there is a date and the time is 0.
    ...


    That is something I fixed in the version that I posted today.

    Or you could fix the version you are using by replacing this:

    dateadd(hh,nullif(@Time,0)/10000,0)))

    with this:
    dateadd(hh,@Time/10000,0)))


    Post #1232884
    Posted Monday, January 9, 2012 6:58 PM


    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Friday, October 24, 2014 12:43 PM
    Points: 4,126, Visits: 3,428
    !Aaron Aardvark! (1/9/2012)
    Michael Valentine Jones (1/9/2012)
    I simplified my original code to eliminate three DATEADD function calls and one NULLIF function call, so it might run a bit faster.

    It seems a little slower on my instances; fastest run out of 10 was 1210ms (versus 1155ms previously).

    Your actual mileage may vary, depending on the processor you are running on.
    Post #1232888
    Posted Monday, January 9, 2012 8:58 PM


    SSC-Dedicated

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

    Group: General Forum Members
    Last Login: Yesterday @ 8:51 PM
    Points: 35,606, Visits: 32,190
    !Aaron Aardvark! (1/9/2012)
    Just for interest's sake, here's the in-line function written to use SQL Server 2012:

    CREATE FUNCTION dbo.agent_datetime_inline
    (
    @Date integer,
    @Time integer
    )
    RETURNS TABLE WITH SCHEMABINDING
    AS RETURN
    SELECT
    DATETIMEFROMPARTS
    (
    @Date / 10000,
    @Date / 100 % 100,
    @Date % 100,
    @Time / 10000,
    @Time / 100 % 100,
    @Time % 100,
    0
    ) AS date_time

    Test results using Jeff's rig:

    Michael's code: 1155ms
    New function: 670ms


    Now THAT would make a cool "spackle" article... you should go for it, Paul. Thanks for the "preview".


    --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 #1232893
    « Prev Topic | Next Topic »

    Add to briefcase ««123»»

    Permissions Expand / Collapse