GoTo Looping
-
Comments posted to this topic are about the item GoTo Looping
Joe Bonomo
-
I agree that you sometimes need to do the loop in one of it's many forms. I have no qualms and fairly minor shivers from things like that. 😀
However... your trick with DELETE/SELECT has created the equivalent of a double triangular join which makes this solution a fair bit worse than it needs to be. It has to scan all rows for the DELETE and it has to scan all the rows for the COUNT(*).
I believe that it would be much better to add an IDENTITY column to the table variable and make that the PK for the table and then use a counter to step through the rows instead of SELECT TOP 1/DELETE. Lordy... if you have just 20 companies to work with, the combination of the DELETE with the lookup and the SELECT COUNT(*) are going to have to look through 210 rows each for a total 0f 420 rows because of the triangular joins.
If you absolutely insist on doing the deletes instead of using a counter, at least define the Company_ID column as the PK (which makes a Clustered Index) to get rid of the implied triangular joins.
We went through the same thing at work but we had a 90,000 row table. They didn't do a COUNT(*) but they did do the same kind of delete with no index as you did. That's (90,000^2 + 90,000)/2 rows (4,050,045,000!!!) that had to be touched instead of just 90,000. It's bad enough that RBAR had to be used but that's massive hidden RBAR.
--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) -
Ack...GOTO. 🙁
Didn't the world teach everyone how evil GOTO is and never to use it in higher level languages? 😀 It's evolution. Let's not evolve backwards. 😉
I understand it's an opinion, and Jeff is being very kind here, IMO. But, please don't let me be the one maintaining the code with GOTO's. 🙂
-
Can you elaborate on why you state GoTo is evil as a general statement? Are there other implications other than maintainability? Agreed, overuse of GoTo can lead to a hard to maintain script. This script does not overuse and is easy to read (I make this claim because I have walked through the script this originated from with many business analysts who cannot write any SQL and they fully understood what the code was doing).
Joe Bonomo
-
@JeffModen,
Thanks for the detailed response. Agreed, could have made this example more performant following some of the guidance you mention.
Regarding why there is no index, the row set in my case was small (5 rows only). This is why I chose a table variable instead of temp table. The optimizer won't even use the index if it were added. I agree if the list of companies was larger, it would be better to use a temporary table with a clustered index.
Joe Bonomo
-
I do not wish to get into the pros/cons of GoTo as a quick google search will return a ton of good content. And since you mentioned "not overused" makes me think you understand the issue.
Better people than I have written dissertations on the subject of GoTo
https://homepages.cwi.nl/~storm/teaching/reader/Dijkstra68.pdf
GOTO and T-SQL – SQLServerCentral
I suppose I was just trying to point out that since there are other, IMO, more elegant techniques out there, why even bring up the topic of GoTo? I am definitely not trying to tell you not to use it, if you want. :). Although, if such code crossed my path and I was responsible for it (this is key), refactoring such code would be on top of my list.
If you are talking about GoTo only in T-SQL, then I'd argue, "sure, use it, but try to keep it in the context of error management if the script must support older versions of the SQL engine."
I agree, GoTo construct is easy to understand. Doesn't mean, IMO, it should be used. It just doesn't "smell" right to me. 😀
-
Thanks for those references and elaborating on your initial response. Appreciate the feedback!
Joe Bonomo
-
A go to statement can jump into the middle of a begin – end block. This means that all of those local variables have to be allocated beginning of the program, rather than being materialized as their needed. The go to his just destroyed all the advantages of a block structured language. In all other optimizations that depend on knowing the state program. At that point something is executed are completely destroyed. When I worked for AIRMICS, we did a lot of readability studies. With the go to. You had no idea where you came from, so you had no idea what the state of the program space was. This is where the slang term "spaghetti code" came from.
Decades of research showed us that the easiest to maintain programs are made up of modules that are loosely coupled, have one entry and one exit point, and which perform one clearly defined function. The go to of course completely destroys any hope of this
Please post DDL and follow ANSI/ISO standards when asking for help.
-
We limit GOTO to a single use-case: DEADLOCK Mitigation loops. We have two SSMS templates: TRAN-bound multi-statement, and Atomic. Commented boilerplate TRY-CATCH code gets the retry loop controls from persisted Policy or SESSION_CONTEXT() - the latter may situationally override the defaults: count of attempts to make, and any retry-delay; has standard logging calls, and uses template-substitutions for the label, nature of operation, artifact involved, and the final RAISERROR sev-17 with RETURN. They are easy to read, easy to implement, easy to maintain (i.e., we don't change them after initial development as there's no need (beauty of templates)). Yes, in other use-cases the dangers of spaghetti-code loom, but in well-understood, limited-implementation form, the elimination of another WHILE loop in favor of simplicity and repeatability wins out. In our case, this makes for fast, reliable development in our very large adjunct-ERP solution). Our use of GOTO is in the performance of one clearly defined function. Perhaps this may be a help to others.
-
GOTO's... ah where to start. In addition to everything said before me, I'll add:
- IMHO... GOTO's should ONLY be used (maybe) at the beginning of the code to check for bad data or parameters. Use the GOTO to skip your code before doing anyting.
- GOTO's or multiple returns get extra difficult when you are dealing with allocated items - CURSORs for example. Did you close and deallocate your cursor EVERY where you have a return.
- GOTO's also add confusion. In your code, you have:
GOTO ExecuteLoop;
RETURN;
ExecuteLoop:
Why does this code does this even exist?
4. I'd redo you code with a simple WHILE loop at the top: WHILE @continue = 1 (Also, setting a bit to a 0 or 1 is probably better than setting it to a string of 'false' or 'true')
5. With the WHILE loop, you don't need the ELSE BEGIN RETURN; END that's near the bottom of the code because the WHILE loop will exit cleanly.
Just my thoughts... I didn't get into the logic of the code, just the flow that's there
John
-
The go to has been suggested to try to avoid at all costs for the past 35 plus years.
- jcelko212 32090 wrote:
A go to statement can jump into the middle of a begin – end block. This means that all of those local variables have to be allocated beginning of the program, rather than being materialized as their needed. The go to his just destroyed all the advantages of a block structured language. ...
Yes, Joe! This just reminded me of the times I used to use setjmp()/longjmp() functions in C. Ya, been a while. Used it for handling exceptions in code. This way I could register a single exception handler for the whole application and jump to it from anywhere in the code. Precursor to the try/catch mechanism of today. Although this pair of functions solved the state issue, the Spaghetti dilemma still had to be contended with.
-
When I do use looping in conjunction with SQL, it's because I'm doing something DBA-ish: like looping across databases to execute a DBCC or BACKUP command, or querying meta data views into one consolidated resultset. However, even then I don't actually use T-SQL to implement the looping and instead use PowerShell.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
This thread is giving me flashbacks to the days when I wrote columns on structured programming in the trade press. That was decades ago! In particular, there was the Bohm-Jacopini (sp?) Theorem, which proved any program written with goto could be rewritten with if-then-else, while-loop and begin-end blocks. One of the big questions at the time was when the test loop. We had pretest WHILE-DO in some languages and posttest REPEAT-UNTIL in others. There was a formal proof that they could be made equivalent.. Then there are all sorts of weird proposals for various kinds of loops. The one I really liked was the WHILE-DO-UNTIL loop. Then at Datamation magazine ran a gag article about the "come from" flow control structure as a replacement for the traditional "go to" flow control.
Please post DDL and follow ANSI/ISO standards when asking for help.
- Joe Bonomo wrote:
@JeffModen,
Thanks for the detailed response. Agreed, could have made this example more performant following some of the guidance you mention.
Regarding why there is no index, the row set in my case was small (5 rows only). This is why I chose a table variable instead of temp table. The optimizer won't even use the index if it were added. I agree if the list of companies was larger, it would be better to use a temporary table with a clustered index.
It's amazing what the optimizer might chose to do if you give it a choice. A triangular join (or lookup) is still 15 rows on 5 rows. Why waste clock cycles? The other thing is that you actually have zero control over how other might use your code especially if they in a pinch and can't figure something out. Since it's so easy to do the right thing even when RBAR is required, why not do it all the time? 😉
As for people that rail against the use of GOTO, I agree that it goes against the nature of "object oriented programming" and "functional programming", etc, etc. Of course, many people will still make the mistake of using DELETE on this type of thing and still others will make stored procedure calls 8 freakin' levels deep and somehow think that's better than using GOTO. 😀
My take on GOTO is that, just like While Loop, Incremental rCTEs, function recursion through self reference of a scalar function, real or implied triangular or full square joins, everything has a purpose. Well, except for that damned PIVOT operator and the awful FORMAT function. 😀 And I can guarantee that almost everyone railing against GOTO thinks that REORGANIZE is useful and resource friendly.
I have to admit that I disagree with Dijkstra. He says it should be eliminated from all "higher-level languages" and makes the exception for machine language. He also states that GOTO is just "too primitive" and I think that's probably the real reason why others take exception... it "feels" too primitive to them. While I certainly don't embrace the regular use of GOTO even as a possible replacement for WHILE loops, it does have a place when you DO need that "primitive" functionality. That means that Dijkstra was entirely correct in saying that you can judge the quality of a Developer as an inversely proportional function of the number of GOTOs in their code... but so it is true of a great many other things. 😀
Of course, the same is frequently true for While loops, incremental rCTEs, recursive function calls, the number of levels of stored procedures called, and several dozen other things. 😀 People also say to "go procedural if you have to". I find they don't spend enough time understanding that most of the time they say they "have to", they really don't.
An extreme example of that is when converting Adjacency List Hierarchies to Nested Sets. And the While loops that most people wrote for the old "push stack method" isn't any better than someone using GOTOs. It's just a different way of committing the same sin. 😀
As with all else, "It Depends".
--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)
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply