Introducing the Set-based Loop

Question

Post reply

Introducing the Set-based Loop

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

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

Brymen SSC-Addicted Points: 495 More actions · Answer 1

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.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

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)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

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

marcceloselv Grasshopper Points: 19 More actions · Answer 5

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

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

John N Hick Ten Centuries Points: 1320 More actions · Answer 7

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.

pradeepcse.t SSC Journeyman Points: 75 More actions · Answer 8

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

Asavko Newbie Points: 3 More actions · Answer 9

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.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 11

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 13

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 14

akljfhnlaflkj

SSC Guru

Points: 76202

October 10, 2016 at 8:58 am

#1905678

Good article, thanks.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 15

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