August 13, 2008 at 4:56 am
I'm looking into some coding of SP's in a database and see this construction several times:
DECLARE @counter as int
SET @counter = 0
_StartLoop:
IF @counter < 10
DO SOMETHING
ELSE
GOTO _End
SET @counter = @counter + 1
I myself would use a DO WHILE loop for this. I wonder if there would be a performance difference between the GOTO and the DO WHILE construction? We are talking about doing STRING operations on millions of records.
Greetz,
Hans Brouwer
August 13, 2008 at 7:15 am
DECLARE @counter as int
SET @counter = 0
WHILE @counter < 10
BEGIN
--Your code here
PRINT @counter
SET @counter = @counter + 1
END
- Zahran -
August 13, 2008 at 8:31 am
I don't know that there is a performance difference as I have not tested it, but as far as readability and maintainability I believe the WHILE LOOP is better than the GOTO.
There are probably better ways to do string manipulation than a loop though. If you loop you are not really taking advantage of the power of SQL Server and would probably be better off doing the manipulation in another language like .NET.
Search this site for "TALLY table" and "TAME THOSE STRINGS" and you will find some good advice on doing more efficient string manipulation in SQL Server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 13, 2008 at 12:29 pm
Tnx for answering. Also tnx for the advise, I'll have a look.
Hope you all realized I should have ended my first statement with GOTO _Repeat...
Greetz,
Hans Brouwer
August 13, 2008 at 12:37 pm
Hans,
Jack has great advice. I'd avoid GOTO if possible since it tends to lead to code that can be confusing and problematic to maintain.
August 14, 2008 at 2:08 am
I would not use GOTO either. It's just I am reviewing some stoprocs and notice this, obviously build by a programmer. I can advise, but not just change things here.
Tnx
Greetz,
Hans Brouwer
August 17, 2008 at 7:05 pm
Heck... I wouldn't even use the WHILE loop... it's RBAR. Most WHILE loop solutions are because someone doesn't know T-SQL well enough to come up with the proper set based solution. The best thing to do would be to pretend that WHILE doesn't exist.
And, word of warning... just because something doesn't have a WHILE loop in it, doesn't mean it's not RBAR. Take a look at the following articles...
http://www.sqlservercentral.com/articles/T-SQL/61539/
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2008 at 8:52 am
If you intend to loop on something, then write what you mean and mean what you write.
WHILE immediately indicates a loop, whereas a GOTO would have to be checked for its meaning, and this gets to be tedious when there are a lot of line of codes within the IF.
(I could also show you how to use the WHILE clause to implement an IF. I am reminded of the "Great C Code Obfuscation Contest" of a few decades past ...)
In my opinion, code clarity is more important that hoping the squeeze out a minuscule improvement in performance.
For instance, I was told, when joining two tables that this code:
[font="Courier New"]SELECT whatever
FROM T1
INNER JOIN T2 ON T1.something = T2.somethingelse[/font]
was preferable (performance-wise) than this code, which I find easier to read:
[font="Courier New"]SELECT whatever
FROM T1
INNER JOIN T2 ON T2.somethingelse = T1.something[/font]
Especially when there are many tables joined.
And if performance is the issue in using an IF instead of a WHILE to implement a loop, it is far better, as mentioned in other posts to first question if you really are using the best method when you write a loop. This is far more useful than a code "tweak" which probably reduces legibility and results in negligible performance gain.
Regards
August 18, 2008 at 8:59 am
J (8/18/2008)
...In my opinion, code clarity is more important that hoping the squeeze out a minuscule improvement in performance.
For instance, I was told, when joining two tables that this code:
[font="Courier New"]SELECT whatever
FROM T1
INNER JOIN T2 ON T1.something = T2.somethingelse[/font]
was preferable (performance-wise) than this code, which I find easier to read:
[font="Courier New"]SELECT whatever
FROM T1
INNER JOIN T2 ON T2.somethingelse = T1.something[/font]
Especially when there are many tables joined.
...
I don't know who told you that or why, but at least since SQL Server 7 the order of the ON in a JOIN is basically meaningless as the Query Optimizer will return the same plan for either.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2008 at 9:09 am
The advice I received specifically concerned SQL Server 7, by someone who specialized in using PowerBuilder. The theory was that it was more efficient to refer, in the join, to the table called in the previous line.
Glad to hear that the order is meaningless. I the meantime, I occasionally encounter stored procedures written for Crystal Reports that depend on several table (owing to high degree of normalization of the database design) and whose joins were written in the "backwards" way for the sake of efficiency.
Regards
August 18, 2008 at 9:36 am
J,
INNER JOIN T2 ON T1.something = T2.somethingelse
VS
INNER JOIN T2 ON T2.somethingelse = T1.something
This is exactly the same to SQL Server. Take a look at the execution plan. It will be the same.
---------------
Back on Topic...
Avoid using GOTO. "GOTO is best used for breaking out of deeply nested control-of-flow statements." --Books Online.
So the WHILE can get the job done but your going to have to look at the performance and decide if its worth using. There are times when you don't always have the time to come up with the proper set based solution. Now I'm not saying you should take the quick and dirty solution just because it works and you don't have the time now.
August 18, 2008 at 10:19 pm
DB_Andrew (8/18/2008)
There are times when you don't always have the time to come up with the proper set based solution. Now I'm not saying you should take the quick and dirty solution just because it works and you don't have the time now.
... and you'll never be given the time to go back and fix it until it breaks at which point you AND your boss are gonna look like code monkeys. Always do it right the first time. Learn to do it right and quickly the first time. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2008 at 9:04 am
Instead of preaching your “RBAR” cliché and putting people down for doing it the wrong way why don’t you get off your soap box and make yourself helpful for a change and answer their questions and give them the proper set based solution. I see this countless times on here where you come in calling REBAR on people and linking to your articles and telling them they are wrong but you never giving them a real solution to the answer. You may as well just say RTFM and tell them to read books online.
Props to "Zahran" on his helpful quick solution. And props to "SSC-Enthusiastic" also who had a really good helpful post when he pointed out that.
"using an IF instead of a WHILE to implement a loop, it is far better"
Sure my post wasn't that helpful and I didn't demo any examples. But I didn't really need to as the previous posters already covered it so well. One of the more important thing that one can do when deciding on what method to use is looking at the performance numbers. That is what I suggested.
At least I didn't come in crying "RBAR" and saying stuff like "The best thing to do would be to pretend that WHILE doesn't exist."
It’s all about the right tool for the job and what is acceptable performance wise. Sure there are other ways of accomplishing the same thing. If the WHILE loop is really that bad why would Microsoft include it in 5 versions of SQL Server over how many years?
August 19, 2008 at 9:21 am
Is this public flaming really necessary ?
I, for one, have found several of Jeff's articles really useful.
Let's keep the tone of this forum civil. I can do without the overt hostility.
August 19, 2008 at 10:06 am
DB_Andrew (8/19/2008)
Instead of preaching your “RBAR” cliché and putting people down for doing it the wrong way why don’t you get off your soap box and make yourself helpful for a change and answer their questions and give them the proper set based solution. I see this countless times on here where you come in calling REBAR on people and linking to your articles and telling them they are wrong but you never giving them a real solution to the answer. You may as well just say RTFM and tell them to read books online....
While I don't necessarily always agree with the tone of Jeff's comments, I usually do agree with the content. Sometimes it's better to point someone in the right direction than out and out providing a solution. If I just post the code that solves the problem then all they do is copy and paste and they really haven't learned anything and will be back with the same question. This was not a question that required a specific answer, but asked for opinions. The articles Jeff linked to provide information on why looping is inefficient and how it can be avoided. Why would he duplicate code he has already provided in the articles he referred the OP to.
BTW-I am responding because my original response is very similar to Jeff's. I voiced my opinion and gave the OP some search words which he could use to find some help on the issue of looping. I believe one of the searches I suggest will lead to at least one of the articles Jeff links to.
... And props to "SSC-Enthusiastic" also who had a really good helpful post when he pointed out that.
"using an IF instead of a WHILE to implement a loop, it is far better" ...
I don't see a post anywhere that says to use an IF instead of a WHILE. The actual post says to use a WHILE instead of an IF.
(I could also show you how to use the WHILE clause to implement an IF. I am reminded of the "Great C Code Obfuscation Contest" of a few decades past ...)
And, note the bolding I added as it is what you left out:
And if performance is the issue in using an IF instead of a WHILE to implement a loop, it is far better, as mentioned in other posts to first question if you really are using the best method when you write a loop. This is far more useful than a code "tweak" which probably reduces legibility and results in negligible performance gain.
If the WHILE loop is really that bad why would Microsoft include it in 5 versions of SQL Server over how many years?
Because there are instances when it is necessary and because there are many programmers who do not think in sets and no one steers them in that direction, they just get solutions given to them.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply