Hidden RBAR: Counting with Recursive CTE's

  • Jeff Moden

    SSC Guru

    Points: 995150

    Comments posted to this topic are about the item Hidden RBAR: Counting with Recursive CTE's

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Henk van den Berg

    SSC Veteran

    Points: 223

    Very insightful article. Thanks.

    I have one question, how do you discard the output?

    (Anticipating a "slap-to-the-forehead" moment, but asking anyway...)

    Best,

    Henk

  • majorbloodnock

    SSCrazy Eights

    Points: 9267

    Sorry, but there's a glaring inconsistency in this article.

    No-one with any taste buds would take a perfectly acceptable alcoholic drink and cool it so much that it freezes. From this I deduce that either

  • what you've been eating all this time isn't really made from beer or
  • there isn't really a secret formula, and you're stringing us all along.
  • Tut, tut, Mr Moden....

    😉

    Semper in excretia, sumus solum profundum variat

  • George J Verras

    Valued Member

    Points: 57

    Excellent, Thanks Jeff!

  • Jeff Moden

    SSC Guru

    Points: 995150

    Henk van den Berg (8/8/2011)


    Very insightful article. Thanks.

    I have one question, how do you discard the output?

    (Anticipating a "slap-to-the-forehead" moment, but asking anyway...)

    Best,

    Henk

    Heh... no problem, Henk. A lot of people miss it. In SQL Server 2005, start by selecting {Tools}{Options}{Query Results}{Results to Grid}. Then, click the {Discard results after execution} check box. In 2005, you may have to open a new query window in SSMS before the setting takes affect. In 2008, (IIRC) the setting is good for the current window and any new windows you open while the box is checked.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 995150

    majorbloodnock (8/8/2011)


    Sorry, but there's a glaring inconsistency in this article.

    No-one with any taste buds would take a perfectly acceptable alcoholic drink and cool it so much that it freezes. From this I deduce that either

  • what you've been eating all this time isn't really made from beer or
  • there isn't really a secret formula, and you're stringing us all along.
  • Tut, tut, Mr Moden....

    😉

    Heh... because I like American mass-produced beer in pop-top cans, some would say that I, indeed, have no taste buds. 😛

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 995150

    George J Verras (8/8/2011)


    Excellent, Thanks Jeff!

    You bet, George. Thanks for stopping by and for the feedback.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • terrance.steadman

    SSCrazy

    Points: 2097

    Greetings Jeff,

    That was a wonderful and very insightful article about rCTE's and ways to count out of the box. I will have to put some of the pseudo-counters into practice in my scripts.

    Thank you for sharing.

    P.S.

    Beer popsicles are possible if you know the *secret* ingredient to mix to make it become solid. 🙂

  • mcannistra

    SSC Enthusiast

    Points: 135

    Thank you Jeff for your wonderful post.

    It has been extremely helpful to achieve faster speed in a test case I was creating on sql azure.

    I referenced your post in the Microsoft forum where I had already started a thread before finding your precious info:

    http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/c368315a-1bf1-4353-b0af-c4cfbb44b2c1

    Cheers,

    Mario

  • Ken Wymore

    SSCoach

    Points: 16432

    Nice article! Thanks for the performance stats with the examples. I sense that I have some production code to rewrite now.... 🙂

  • drew.georgopulos

    SSCarpal Tunnel

    Points: 4931

    Good God i had no idea reading could make me feel so smart!...now it just has to sink in

    Thank you very much for explaining the intracacies of each path.

    drew

  • SQLRNNR

    SSC Guru

    Points: 281205

    Great Article Jeff.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Sir Slicendice

    Mr or Mrs. 500

    Points: 570

    Nice article - but I will mention: recursive CTE's aren't always slow! In fact, in my study of the capabilities of the optimizer a few years back, I found that the optimizer was definitely able to properly turn many tail-recursive CTE's into loops, making them just as fast - and far more transparent - than hand writing the loop. I don't have time to dig out code now, but it's pretty easy to test on your own: the key is to make the CTE properly tail-recursive, and keep it simple. The optimizer is definitely incapable of tail recursion optimization across functions, though.

    But just because it's recursive doesn't necessarily mean it's slow!

    -frank


    The End.

  • Jeff Moden

    SSC Guru

    Points: 995150

    terrance.steadman (8/8/2011)


    Greetings Jeff,

    That was a wonderful and very insightful article about rCTE's and ways to count out of the box. I will have to put some of the pseudo-counters into practice in my scripts.

    Thank you for sharing.

    P.S.

    Beer popsicles are possible if you know the *secret* ingredient to mix to make it become solid. 🙂

    Heh... yeah... the ol' "hot ice" trick works pretty well but makes the beer taste pretty bad. That's why I use the "Chill'n'Tap" method, instead. 😛

    Thanks for stopping by and for the feedback. Hmmmm. "Pseudo-Counters". You may have just coined your own term for the 3 counting methods in the article that use "Pseudo-Cursors". 🙂

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 995150

    Sir Slicendice (8/8/2011)


    But just because it's recursive doesn't necessarily mean it's slow!

    -frank

    Nor did I say so in the article. 😉 I absolutely agree and that's why I limited the slowness problem to these types of "counting" rCTE's. Even the title of the article reflects that fact.

    It is good, though, to emphasize the fact that I was only talking about Counting rCTE's in the article, so thanks for bringing it up, Frank. I have to admit , though... although While Loops and rCTE's take turns winning, I generally try to avoid both in favor of set-based solutions.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Viewing 15 posts - 1 through 15 (of 72 total)

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