Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Jeff Moden Calculating Duration Using DATETIME Start and End Dates (SQL Spackle) RE: Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

  • January 16, 2014 at 8:53 pm

    #1680947

    dwain.c (1/16/2014)

    I must say, I love the technique Jeff! Very simple, straightforward and clean with good performance. Nothing more straightforward comes immediately to mind (issues noted above aside).

    Normally I take your word on performance but I in this case I played a bit and found something interesting. Running your 1M row test harness, followed by the below query:

    SET STATISTICS TIME ON;

    SELECT @Bitbucket = STUFF(CONVERT(VARCHAR(20),s,114),1,2,DATEDIFF(hh,0,s))

    FROM #JBMTest

    CROSS APPLY (SELECT EndDT-StartDT) a (s);

    SET STATISTICS TIME OFF;

    Generated different speed results on SQL 2008 R2 vs. SQL 2012.

    SQL 2008 R2 (expected - second result uses CA for intermediate calculation):

    (1000000 row(s) affected)

    SQL Server Execution Times:

    CPU time = 702 ms, elapsed time = 699 ms.

    SQL Server Execution Times:

    CPU time = 717 ms, elapsed time = 707 ms.

    SQL 2012 (unexpected):

    (1000000 row(s) affected)

    SQL Server Execution Times:

    CPU time = 749 ms, elapsed time = 738 ms.

    SQL Server Execution Times:

    CPU time = 671 ms, elapsed time = 674 ms.

    Improvements in the optimizer I reckon.

    Thanks for the feedback, Dwain. Like I said in the article, most of the time was spent on display and the actual time expended for the calculation was right around 717ms using the "@BitBucket" method for taking the display out of the picture. Your code certainly backs that claim up.

    As for the 2012 code, I'm actually disappointed to see those results and wouldn't call it an "improvement" to the optimizer at all. It seems to indicate that you need more complex code to get the same performance out of what used to be simpler code.

    --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)