Home Forums SQL Server 2005 T-SQL (SS2K5) Is this a "gaps and islands" problem? Finding gaps in overlapping times. RE: Is this a "gaps and islands" problem? Finding gaps in overlapping times.

  • August 15, 2013 at 6:06 am

    #1641750

    GPO (8/15/2013)

    Awesome to have the heavy hitters on the case! Jeff, where you say:

    You're not actually supposed to use any data from that. You're only supposed to use the presence of rows instead of writing a loop

    I understand that, but if you run that it returns 5 columns of zeros. All called [n]. And I can't work out why that's necessary. Surely one column of zeros would be enough.

    Edit: I think I get it now! The cross joins mean that 10^5 rows are generated, and you're saying it's irrelevant that 5 meaningless columns just happen to be generated. Doing an extra CROSS JOIN would presumably result in 6 meaningless columns and a million rows. Does that sound right?

    Edit 2:

    a readless Tally Table

    So that means a tally table that doesn't have to be read from the disk, and is presumably therefore faster than the permanent tally table I usually use. How am I going so far?

    Thank you for the kind words and I know you meant no slight but, just to be sure, Chris and Dwain are both "heavy hitters" in my book. If they weren't, I wouldn't have learned the things from them that I've learned. 😀

    Your edit #1 is spot on and both Chris and Dwain have each provided good explanations.

    Your edit #2, however, isn't quite right. Allow me to explain. Which is faster? Reading values cached in high speed memory or calculating values? If you take a look at the following chart, you'll find that reading from a cached table ever so slightly edges out calculating the numbers. The "problem" with reading from a cached Tally Table is that it will produce a huge number of reads. While not a problem, in this case, it's still bothersome for many because one of the indicators of other types of performance challenged code is to measure the reads for each query.

    If you're interested, the chart above came from the following article...

    http://www.sqlservercentral.com/articles/T-SQL/74118/

    Also, I've been experimenting more with the performance of the cCTE (Cascading CTE) method that Itzik Ben-Gan originally came up with and I'll post my latest tonight after work (if I can remember that long, lots going on :-P).

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