Loop within a Loop

Question

Loop within a Loop

Viewing 15 posts - 16 through 30 (of 30 total)

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

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 1

I used the while loop within while loop worked a treat for what i need :), thanks for all the SET based solutions but id used the loop to complete the task which it did in less than a second so im happy plenty to ponder for the future though :w00t:

***The first step is always the hardest *******

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

SGT_squeequal (7/12/2012)
I used the while loop within while loop worked a treat for what i need 🙂

So with 5 other methods that are faster you still choose to use the slow looping version? :w00t:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 3

i used the loop before any of the set based ooptions appeard plenty to ponder for the future :);-)

***The first step is always the hardest *******

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

SGT_squeequal (7/12/2012)
i used the loop before any of the set based ooptions appeard plenty to ponder for the future :);-)

And that is a reason to not change which method you use?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Sean Lange (7/12/2012)
SGT_squeequal (7/12/2012)
I used the while loop within while loop worked a treat for what i need 🙂
So with 5 other methods that are faster you still choose to use the slow looping version? :w00t:

Not only faster, but more compact as well.

😉

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

SGT_squeequal (7/12/2012)
i used the loop before any of the set based ooptions appeard plenty to ponder for the future :);-)

If you chose a looping method over 5 faster, simpler set-based methods while on my watch, you'd have all the time in the world to ponder the future - at home.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 7

I dont really know how to take your remark, are you being a popmus git, just showing off or trying to help? mmm

will grant, that set based, over loop is more efficient but, at the end of the day it gets dark and the job needs doing. The while loop worked perfect for my task, so it matters not what way i completed the task as long as it was completed within time frame given.

Thanks to all of you who posted constructive and helpfull comments, im not a SQL developer but i do my bit.

***The first step is always the hardest *******

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Not all experienced SQL Server developers appreciate the importance of code performance - as a relative newcomer it may have passed you by, even after lurking around here for a while.

I apologise if the remark has caused personal offence, however I would urge that you take the intent on board. I'm not alone on this thread in expressing surprise that you chose a looping method.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 9

SGT_squeequal (7/12/2012)
I dont really know how to take your remark, are you being a popmus git, just showing off or trying to help? mmm
will grant, that set based, over loop is more efficient but, at the end of the day it gets dark and the job needs doing. The while loop worked perfect for my task, so it matters not what way i completed the task as long as it was completed within time frame given.
Thanks to all of you who posted constructive and helpfull comments, im not a SQL developer but i do my bit.

Whom are you adressing? Actually, it doesn't matter. Folks are just trying to help.

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

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 10

:hehe:

Jeff Moden (7/12/2012)
SGT_squeequal (7/12/2012)
I dont really know how to take your remark, are you being a popmus git, just showing off or trying to help? mmm
will grant, that set based, over loop is more efficient but, at the end of the day it gets dark and the job needs doing. The while loop worked perfect for my task, so it matters not what way i completed the task as long as it was completed within time frame given.
Thanks to all of you who posted constructive and helpfull comments, im not a SQL developer but i do my bit.
Whom are you adressing? Actually, it doesn't matter. Folks are just trying to help.

i was refering to ChrisM@Work comment,

If you chose a looping method over 5 faster, simpler set-based methods while on my watch, you'd have all the time in the world to ponder the future - at home.

@chris-2 i understand the need for perforamce however on this instance there was no real requirement your just going to have to trust me on that, i have looked at the other methods for future use and like the CTE way to complete the task. once again thank you all for your help it was and aways is appreciated.

EDIT PS

apology acceepted thanks to everyone

***The first step is always the hardest *******

Dwain Camps SSC Guru Points: 86978 More actions · Answer 11

Sean Lange (7/12/2012)
SGT_squeequal (7/12/2012)
I used the while loop within while loop worked a treat for what i need 🙂
So with 5 other methods that are faster you still choose to use the slow looping version? :w00t:

Clearly this must be one of the rare applications where high performance is not a non-functional requirement.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 12

dwain.c (7/12/2012)
Sean Lange (7/12/2012)
SGT_squeequal (7/12/2012)
I used the while loop within while loop worked a treat for what i need 🙂
So with 5 other methods that are faster you still choose to use the slow looping version? :w00t:
Clearly this must be one of the rare applications where high performance is not a non-functional requirement.

Agreed but consider that taking every opportunity to do it the right way will make you better when it really does need to be right. It's kind of like practicing the piano. Unless you're in the business of being a comedian, you just don't practice hitting the wrong notes.

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

Dwain Camps SSC Guru Points: 86978 More actions · Answer 13

Jeff Moden (7/12/2012)
dwain.c (7/12/2012)
Sean Lange (7/12/2012)
SGT_squeequal (7/12/2012)
I used the while loop within while loop worked a treat for what i need 🙂
So with 5 other methods that are faster you still choose to use the slow looping version? :w00t:
Clearly this must be one of the rare applications where high performance is not a non-functional requirement.
Agreed but consider that taking every opportunity to do it the right way will make you better when it really does need to be right. It's kind of like practicing the piano. Unless you're in the business of being a comedian, you just don't practice hitting the wrong notes.

I guess, omitting the smiley from my post, also lost the irony of my statement. 😀

C'mon now - that was funny wasn't it?

I can name that tune in 5 off-key notes!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 14

Heh... I've gotten in trouble with some folks by using emoticons. Decided to try not using them for a while. Guess that's not working so well, either. :hehe:

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

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 15

as today is the 13th its a good day my lucky day 🙂 other day was a dark day lol 😀

***The first step is always the hardest *******