Introducing the Set-based Loop

  • EXCELLENT Article!

    All I can say is more, more... I want to read more articles like this one!

    You can bet I will be sharing this one with my colleagues.

  • brymen (10/7/2016)


    EXCELLENT Article!

    All I can say is more, more... I want to read more articles like this one!

    You can bet I will be sharing this one with my colleagues.

    Thank you for the nice feedback.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Oh my... with a title like that, I'm not sure how I missed the original publication of this article, Luis. Thanks for taking the time to write it. This will be my "read an interesting article" task for the weekend.

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

  • Jeff Moden (10/7/2016)


    Oh my... with a title like that, I'm not sure how I missed the original publication of this article, Luis. Thanks for taking the time to write it. This will be my "read an interesting article" task for the weekend.

    Thanks again for encouraging me to start writing. It has been a great experience.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Maybe change the CTE recursion for a temporary table (#table), whould change the game.

  • marcceloselv (10/7/2016)


    Maybe change the CTE recursion for a temporary table (#table), whould change the game.

    Can you show me what do you mean with some code? I'm not sure what you have in mind, but it would be nice to know.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Please try this after commenting out this line:

    --WHILE @@ROWCOUNT > 0 --Update until there's no previous group for any Client

    I believe that you will find no difference.

  • This could be slower when you have complex business logic in Join or Where condition

  • Good technical information is provided by there is no need in a technical article to offend programmers with procedural programming background. It is not professional.

  • John Hick-456673 (10/7/2016)


    Please try this after commenting out this line:

    --WHILE @@ROWCOUNT > 0 --Update until there's no previous group for any Client

    I believe that you will find no difference.

    You might not have understood the problem and the solution. If I comment that line, it will update the table just once, but I need to update it multiple times. Every update will assign different values.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • pradeepcse.t (10/7/2016)


    This could be slower when you have complex business logic in Join or Where condition

    Yes, every bit of additional work will make the solutions slower. But if it makes slow one, it might probably make slow every one, because that additional work should be added to all the solutions.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Asavko (10/9/2016)


    Good technical information is provided by there is no need in a technical article to offend programmers with procedural programming background. It is not professional.

    I'm sorry, it's not intended as an offense. I started as a programmer thinking row by row. Once I learned to how to work with sets in SQL Server, I became a good SQL developer. However, now I'm a very bad front-end developer and might not be able to differentiate a class from an interface. I'm not offended by that, we all lack of some knowledge.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (10/10/2016)


    Asavko (10/9/2016)


    Good technical information is provided by there is no need in a technical article to offend programmers with procedural programming background. It is not professional.

    I started as a programmer thinking row by row. Once I learned to how to work with sets in SQL Server, I became a good SQL developer.

    This is a key point that I think Luis illustrated very well. The first step to getting out of the RBAR mindset is to recognize that you have it. Not knowing the difference is a huge roadblock to writing set-based code. When you do break out of it, then you let SQL Server do what it does best and your code can fly.

  • Good article, thanks.

  • Ed Wagner (10/10/2016)


    The first step to getting out of the RBAR mindset is to recognize that you have it.

    Kind of like any other addiction. First step is to acknowledge that you have a problem.

    ATBCharles Kincaid

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

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