Hidden RBAR: Counting with Recursive CTE's

  • Jeff Moden

    SSC Guru

    Points: 996619

    John Dempsey (8/8/2011)


    Great article Jeff with great explanations & examples.

    Long time no see, John. Thanks for the feedback. Heh... I'd forgotten about the "Pork Chop Logo". I wonder how T-Shirts with it would sell at PASS. πŸ˜›

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    F

  • majorbloodnock

    SSCrazy Eights

    Points: 9389

    Andy DBA (8/8/2011)


    majorbloodnock (8/8/2011) ... No-one with any taste buds would take a perfectly acceptable alcoholic drink and cool it so much that it freezes...

    A perfectly acceptable alcoholic drink? Have you ever tasted Canadian "beer"? I'm guessing the whole idea behind the popsicles is to numb the taste buds...

    He, he. Yes I have, and that brings me back to point one of my original post. It isn't really beer, except, perhaps, after someone's processed it. πŸ˜‰

    Sorry, don't mean to pick on Canadian beer. Most American "beer"s aren't palatable either unless they're too cold to taste. That's why we have a brand where graphics on the can change color when it's cold enough for consumption!

    To be honest, it isn't just an issue on the North American continent. Fosters, for instance, may be a different brand, but it's still basically the same gassy chemical brew produced over here.

    Semper in excretia, sumus solum profundum variat

  • Dugi

    SSCoach

    Points: 17998

    Jeff,

    Thanks for the very nice stuff! Simple to say that I need to read it again and doing more and more practicing and practicing ...!

    Have a nice day,

    Dugi

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Mattrick

    Ten Centuries

    Points: 1176

    Jeff,

    Thanks for the article and the performance metrics. After having read it, I will be sending a link to all of the developers in my department as a means to more succinctly explain why I have advocated avoiding recursive CTEs used for this purpose.

    Thanks again,

    Matt

  • Revenant

    SSC-Forever

    Points: 42467

    Jeff

    I have printed this article and put it into my 'keep for frequent reference' folder.

    Much thanks!

  • ChrisM@Work

    SSC Guru

    Points: 186107

    Crackin' good read as always Jeff.

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • John Dempsey

    Hall of Fame

    Points: 3165

    Long time no see, John. Thanks for the feedback. Heh... I'd forgotten about the "Pork Chop Logo". I wonder how T-Shirts with it would sell at PASS. πŸ˜›

    Yeah, it has been tough getting time on the forums :(. Makes me sad, but I was definitely looking forward to when this article was coming out.

    I might have to get a prototype t-shirt going and submit it to Steve Jones for approval. I will be at PASS this year πŸ˜‰ and hopefully meeting you there after you win, so it could work.

  • tfifield

    SSCrazy Eights

    Points: 9655

    Excellent article Jeff!

    It really puts the kabosh on the rCTE method. It also shows something that I found a while back. That the old fashioned tally table in some cases can slightly out perform the Itzik style cross join.

    Todd Fifield

  • lstrashnoy

    Valued Member

    Points: 61

    I believe that if you define E1 in Itzik stile join as:

    E1(N) AS (

    SELECT 0 UNION SELECT 1 UNION SELECT 0 UNION

    SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION

    SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9

    ),

    then you don't need an analytic function.

    That will make your code little cleaner and may be faster.

    I've been using this technique for years...

    Thanks,

    -Leonard

  • lstrashnoy

    Valued Member

    Points: 61

    Of cause your calculations little different:

    WITH --===== Itzik-Style CROSS JOIN counts from 1 to the number of days needed

    E1(N) AS (

    SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL

    SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL

    SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9

    ),

    E2(N) AS (SELECT 1000*thousands.N + 100*hunderds.N + 10*tens.N + ones.N

    FROM E1 thousands, E1 hunderds, E1 tens, E1 ones),

    cteTally(N) AS (SELECT TOP 4000 N FROM E2 order by N)

    SELECT *

    FROM cteTally

    - Leonard

  • Jeff Moden

    SSC Guru

    Points: 996619

    lstrashnoy (8/9/2011)


    Of cause your calculations little different:

    WITH --===== Itzik-Style CROSS JOIN counts from 1 to the number of days needed

    E1(N) AS (

    SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL

    SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL

    SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9

    ),

    E2(N) AS (SELECT 1000*thousands.N + 100*hunderds.N + 10*tens.N + ones.N

    FROM E1 thousands, E1 hunderds, E1 tens, E1 ones),

    cteTally(N) AS (SELECT TOP 4000 N FROM E2 order by N)

    SELECT *

    FROM cteTally

    - Leonard

    Hi Leonard,

    Thanks for the feedback and the code.

    You might want to reconsider and use something else, though. On my machine, your code does pretty well at a count of 8k... between 17 and 23 milliseconds (heh... yeah... old machine but I still love it) which is still slower than all the other methods except the rCTE but still pretty fast. The problem is that it also takes almost that long to generate a count of 1. This is because the code generates all 10000 numbers no matter what the requested number is. Take a look at the Execution Plan and then run it in the test code that I attached at the bottom of the article to see what I mean.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    F

  • Jeff Moden

    SSC Guru

    Points: 996619

    tfifield (8/9/2011)


    Excellent article Jeff!

    It really puts the kabosh on the rCTE method. It also shows something that I found a while back. That the old fashioned tally table in some cases can slightly out perform the Itzik style cross join.

    Todd Fifield

    Hi Todd,

    Thanks for stopping by.

    Gosh... I can only hope that the article puts the kabosh on counting with rCTE's in people's minds because, like I said in the article, it's becoming an almost viral problem on posts on many forums. I suspect the "draw" to using the rCTE is that it looks setbased, is easy to write (although not nearly as easy as the two Cross-Joined table methods), and it shows that the person at least knows about a relatively advanced feature. It just doesn't work well for counting problems and writing this article is just my first step to trying to get the word out. I'll also take any help I can get with folks spreading the word.

    And, I absolutely agree... the Tally Table is right up there with the big boys and it's a whole lot easier to write code with than a lot of the other methods.

    Again, thanks for stopping by, Todd. Always a pleasure.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    F

  • Jeff Moden

    SSC Guru

    Points: 996619

    John Dempsey (8/9/2011)


    Long time no see, John. Thanks for the feedback. Heh... I'd forgotten about the "Pork Chop Logo". I wonder how T-Shirts with it would sell at PASS. πŸ˜›

    Yeah, it has been tough getting time on the forums :(. Makes me sad, but I was definitely looking forward to when this article was coming out.

    I might have to get a prototype t-shirt going and submit it to Steve Jones for approval. I will be at PASS this year πŸ˜‰ and hopefully meeting you there after you win, so it could work.

    I can only hope to win. There's 4 other good folks I'm competing against. If you'll allow me a self indulgent minute, help spread the word... I can use all the votes I can get. πŸ™‚

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    F

  • Jeff Moden

    SSC Guru

    Points: 996619

    ChrisM@Work (8/9/2011)


    Crackin' good read as always Jeff.

    Thanks, Chris. Always good to hear from you.

    I never noticed it before. Your signature line says you're a defender of moggies. We have 7 that we rescued from the streets. At one time, we had 15 of them, 4 dogs, and a rabbit and that was a little much. Even the dust bunnies got uncomfortable.

    Nah... didn't get rid of any of them. They all died of old age. I think we've given good lives to something like 40 or 50 of them.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    F

  • Jeff Moden

    SSC Guru

    Points: 996619

    Revenant (8/9/2011)


    Jeff

    I have printed this article and put it into my 'keep for frequent reference' folder.

    Much thanks!

    You bet, Revenant. Thank you for the read and the feedback. πŸ™‚ A person just can't ask for a better compliment than to make it to someone's FAQ folder. Thanks.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    F

Viewing 15 posts - 31 through 45 (of 72 total)

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