Hidden RBAR: Counting with Recursive CTE's

  • Jeff Moden

    SSC Guru

    Points: 996619

    Mattrick (8/9/2011)


    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

    You a Man after my own heart, Matt! πŸ™‚ rCTE's that count (and some that do other things) look like some pretty clever code and people just don't understand what goes on under the hood of the blasted things. Glad you're spreading the word and thanks 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.
    "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

    Dugi (8/9/2011)


    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

    Hi Dugi,

    Long time no see. Thanks for stopping by. I've been pretty busy and haven't had a chance to look in at your new users group. How's that going so far?

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

  • Kenney Hill

    SSC Eights!

    Points: 908

    Excellent article, as always, Jeff! And as all of your articles that I mange to see and read, this one has been added to my briefcase. I only wish I had the opportunity to work with you for even a few months, my thought processes and SQL code would be so much better for it!

    Thank you for the time and effort you put into these types of things (articles, forums, etc.).

  • Dugi

    SSCoach

    Points: 17998

    Jeff Moden (8/9/2011)


    Dugi (8/9/2011)


    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

    Hi Dugi,

    Long time no see. Thanks for stopping by. I've been pretty busy and haven't had a chance to look in at your new users group. How's that going so far?

    I'm busy little bit while I'm trying to organize the first meeting in the September, so things are going good, but still more efforts to make things properly! Thanks for asking we will be in touch for the progress...!

    Have a nice day!

    πŸ˜‰

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

  • ChrisM@Work

    SSC Guru

    Points: 186107

    Jeff Moden (8/9/2011)


    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.

    Father Christmas and the petting farm πŸ™‚ I've rescued one or two in my time. Seven places yourself and your missus somewhere between charitable and saintly!

    On first reading your article I was a little concerned that folks might write off rCTE's altogether, despite your very clear wording. However, it does appear that folks have picked up clearly that rCTE's are a profoundly poor choice for counting, not necessarily for other tasks.

    Incidentally, did you notice during your tests how rubbish the stats are for rCTE's?

    [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

  • Ken Wymore

    SSCoach

    Points: 16588

    Jeff,

    Where would I vote for you on the PASS site or is that already gone and past?

    Kenny

  • John Dempsey

    Hall of Fame

    Points: 3165

    Kenneth Wymore (8/10/2011)


    Jeff,

    Where would I vote for you on the PASS site or is that already gone and past?

    Kenny

    For those interested, Jeff is currently nominated for the award of Exceptional DBA and here is the link to vote: RedGate Exceptional DBA voting link[/url]

  • Ken Wymore

    SSCoach

    Points: 16588

    John Dempsey (8/10/2011)


    Kenneth Wymore (8/10/2011)


    Jeff,

    Where would I vote for you on the PASS site or is that already gone and past?

    Kenny

    For those interested, Jeff is currently nominated for the award of Exceptional DBA and here is the link to vote: RedGate Exceptional DBA voting link[/url]

    Thanks John!

  • Jeff Moden

    SSC Guru

    Points: 996619

    Kenney Hill (8/10/2011)


    Excellent article, as always, Jeff! And as all of your articles that I mange to see and read, this one has been added to my briefcase. I only wish I had the opportunity to work with you for even a few months, my thought processes and SQL code would be so much better for it!

    Thank you for the time and effort you put into these types of things (articles, forums, etc.).

    Gosh, Kenney... that's one heck of a nice compliment. Thank you, Sir.

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

  • lstrashnoy

    Valued Member

    Points: 61

    Thanks Jeff.

    I actually agree. I used this code in mySQL and it works pretty efficient. Unfortunately SQL Server not optimizing the way mySQL does. And because it has to do sorting as the last step it kills performance πŸ™

    In my mySQL environment I have numbers in millions and it does everything in memory without reads πŸ™‚

    There is no analytical functions yet in mySQL anyways...

    Thanks,

    -Leonard

  • Jon Russell

    SSCrazy

    Points: 2997

    As usual, excellent! Thanks, Jeff.

  • Paul Herbert

    Valued Member

    Points: 71

    As usual a good article Moden.

    A minor correction if you don't mind. If you are going to quote my 'Really?!", I think it needs both a question mark & an exclamation point. That way it reads like I say it ... a touch of amusement & a ton of WTF. πŸ™‚

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    "Those who can make you believe absurdities,
    can make you commit atrocities." ~Voltaire
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  • Jeff Moden

    SSC Guru

    Points: 996619

    Paul Herbert (8/10/2011)


    As usual a good article Moden.

    A minor correction if you don't mind. If you are going to quote my 'Really?!", I think it needs both a question mark & an exclamation point. That way it reads like I say it ... a touch of amusement & a ton of WTF. πŸ™‚

    Everyone... please meet and greet Mr. Paul Herbert... the Systems DBA where I work at. We've slain many dragons together in the short year or so that we've had the opportunity to work with each other. πŸ™‚ I've been trying to convince him to write an article about some of the things he's done on the Administrative side of the world. Maybe you good folks can help me convince him. πŸ˜‰

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

    lstrashnoy (8/10/2011)


    Thanks Jeff.

    I actually agree. I used this code in mySQL and it works pretty efficient. Unfortunately SQL Server not optimizing the way mySQL does. And because it has to do sorting as the last step it kills performance πŸ™

    In my mySQL environment I have numbers in millions and it does everything in memory without reads πŸ™‚

    There is no analytical functions yet in mySQL anyways...

    Thanks,

    -Leonard

    IIRC, doesn't mySQL have a builtin function that will generate numerical sequences?

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

    Jon Russell (8/10/2011)


    As usual, excellent! Thanks, Jeff.

    You bet, Jon. 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.
    "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 - 46 through 60 (of 72 total)

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