June 13, 2008 at 2:35 am
I am trying to make some sense from all the squabbeling and cannot really make up my mind whether it is better to have a cursor or not. The point is that sometimes you have to read the data as Jeff Moden says: "Row by agonizing row". Whether you do it in a while loop or in a cursor souns like the same difference to me. Let me put a scenario to you.
I have to create a report for a customer that has a project management system. The tables as follows:
ProjectTable with ProjectId int (primary key and identity column), Description varchar(255), start date and enddate datetime.
ContractTable with ContractNo int, ProjectId int, RegNo (vehicle regno) varchar(12), DatePlaced datetime
I have now to do an accumulative count of the contacts for each month that the project is running so what I did was to select the projectid, projectname, start date and end date into a cursor, use another while to insert a record into a ## temp table for each month that the project is running and then join the contract table with the ## table to do the accumulative count. This sounds hectic I know but give me a better idea if you have.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
June 13, 2008 at 2:50 am
I strongly DISLIKE this article because it completely misses the point about cursors...
Like the author, I always use some form of non-cursor based looping if I really have to because so many (junior?) C#/TSQL programmers are not qualified enough to understand why they should stay away from "this way of thinking" and it is simpler to "forbid" cursors entirely.
The main reason the code is slow when calling a stored proc within a loop based cursor is... just that you are calling many times "something". It is not so relevant what the loop mechanism is.
To solve the speed problem, you must re-assess what that "something" does each time on one row and see if you could not turn this into one or several operations on a set of rows instead.
The "war" against cursor is simply that these things fly into the set based approach underlying SQL and that many developers may not understand that issue at all.
Using any form of WHILE loop (temporary table, table variable... anything!) is just a "cursor in disguise".
For a complete discussion on the subject, I highly recommend Inside SQL Server 2005: TSQL Programming where Itzik Ben-Gan shows an example of cursor beating any set based approach... but he is avery clever guy and most people would struggle to find such an example (I would...).
Cursors???
Bad... Bad... :angry:
June 13, 2008 at 2:52 am
Look @ the following article
http://www.sqlservercentral.com/articles/SQL+Puzzles/eliminatingcursors/2223/
June 13, 2008 at 4:12 am
Leea, the link you gave goes to the very article that are being discussed here.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
June 13, 2008 at 6:41 am
Japie Botma (1/17/2006)
I have used this method for years (because I hate cursors), but does it slightly different. I use a #Temp table and then select the TOP 1 at the beginning of the loop with a delete at the end of the loop. I would recommend this method to anybody in a position that HAS to use a cursor.
I've learned to do, pretty much what you are saying here, using a table type.
I've only needed it for small one time procedures and not batch procedures, so I have never checked for performance issues, but as far as writing it goes, it is fairly simple to plug the values into a table type value and do something like
while (select count(*) from @table) > 0
begin
select top 1 'do something', @id=id from @table
delete from @table where id=@id
end
to quickly finish up a job.
obviously you need to create some sort of unique id when you are populating the table, if it doesn't already have one.
I've seen others comment on why there is hatred about using cursors. I have to admit, it's been a long time since I've used cursors, and I can't really remember why I hated them so much, but I do recall hating them :angry:. Maybe I just wasn't as adept at SQL as I am now :cool:, but I seem to recall it getting very complicated when I attempted to use multiple nested cursors :crazy:. I honestly can't recall :ermm:.
NOTE: I apologize for not noticing at the beginning how old this discussion was and how many posts there were when I added this to it. I hopefully didn't regurgitate anything. Noob mistake.:Whistling:
June 13, 2008 at 6:54 am
When I began reading this article, I wondered why it had been chosen to be re-posted, as I've read several other articles by Jeff, Matt, et al that demonstrate the advantages of Tally tables versus looping / set-based approaches that solve problems much more efficiently / problems with table variables, etc. that would seem to reveal this solution as an inefficient one.
However, I think the discussion related to this article is exceptional. Thanks to all of you who take the time to run tests, post your results, discuss other solutions and share your wealth of knowledge with the rest of the community.:D
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 13, 2008 at 7:42 am
"I hate cursors" is an awful vague statement. I hate UPDATE statements. I'd rather do a DELETE and then an INSERT! Wait, that is what SQL does when you update a table. Oh well. There are times when cursors have their use. You talk about not using cursors when you could use SET based logic, but you have done absolutely nothing in this example to turn it into SET based.
You have converted a cursor into a table variable, which, behind the scenes, is what SQL does with a cursor. However, instead of letting SQL use its internal mechanism for tracking position within the set (yes, a cursor in itself is a set), you have taken full control. Not always the most advantageous, or efficient way of doing it. @FETCH takes care of positioning to the correct row in the cursor, @@FETCH_STATUS takes care of knowing when you've reached the end, etc.
I agree with André Cardoso, make it static, or declare FORWARD_ONLY. I can provide you examples where using a cursor increased performance 100-fold. As a rule, I don't like to use them and try to avoid them as the 'easy way out,' but to say you NEVER use them and you HATE them show that you don't know SQL as well as you could (and I certainly don't either).
June 13, 2008 at 7:48 am
Have a suggestion to make:
Instead of using @icounter = @icounter + 1,
I would use SELECT TOP 1 * FROM TEMPTABLE WHERE IDFIELD > @iCounter at the beginning and SELECT TOP 1 @iCounter = IDFIELD WHERE IDFIELD > @iCounter
The reason for this is that I have been in situations where I had to use the table more than once and the IDFIELD is not reset but increments from the last max value.
June 13, 2008 at 7:53 am
This still seems to be missing the point...
The title of this article was "Eliminating Cursors" while it's just "hidding" them.
Cursor are only "bad" because they are processing row by row.
Processing "Row by Row" another way does not improve anything fundamental.
Eric
PS: I have used cursors very occasionaly but I was hiding and did not get caught! 😛
June 13, 2008 at 8:39 am
I don't understand why such an old and bad rated article is still alive in this site.
* Noel
June 13, 2008 at 8:46 am
It's very dangerous and counterproductive choosing one technique over another based on bias alone.
Let's call this total aversion to cursors just what it is, pure cursorism, and people espousing it cursorists. Perhaps one day this will be considered just as politically incorrect as other "isms" in our day and age... :w00t:
Each technique has its place and whether or not it is adopted for a particular problem should be based on testing and testing alone. Sure, set-based logic is superior to cursors, but once we start replacing cursors with other loop structures such as WHILE loops we need to be careful.
As someone has already mentioned, WHILE loops are not necessarily faster than cursors. In fact the opposite is true in some situations.
If you have to apply loop logic to your situation, test your favorite "WHILE-loop" construct against using a cursor. Pick the fastest option, whichever that may be. End of story...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 13, 2008 at 8:47 am
jcrawf02 (6/13/2008)
When I began reading this article, I wondered why it had been chosen to be re-posted, as I've read several other articles by Jeff, Matt, et al that demonstrate the advantages of Tally tables versus looping / set-based approaches that solve problems much more efficiently / problems with table variables, etc. that would seem to reveal this solution as an inefficient one.However, I think the discussion related to this article is exceptional. Thanks to all of you who take the time to run tests, post your results, discuss other solutions and share your wealth of knowledge with the rest of the community.:D
I agree completely. I didn't even bother to read all of the discussion posts to this old article, but I'm baffled as to why it was chosen for re-posting when there's such a rich selection of articles to choose from that describe true set-based solutions.
As Jeff Moden and others have pointed out in the past, it doesn't matter if you use a cursor loop or a roll-your-own-cursor while loop: Row By Agonizing Row (RBAR) is still RBAR. :sick:
June 13, 2008 at 8:53 am
I have to admit I'm a little confused as to why this article has been "re-published" and especially as it is describes as "T-SQL does some things wonderfully, but cursors are the bane of the language, often causing performance issues. Changing your queries around to remove cursors can be tricky and new author Kamran Ali brings us one technique he has used to dramatically improve performance." in the email newsletter. The author is not new, at least not now seeing as the article is two and half years old, and the content of the article, as other people have posted, may well get rid of the cursor but only to replace it with a pseudo-cursor as implemented with a WHILE loop.
I would also agree with a number of posts that state that in the majority of cases a set-based solution is preferable and possible.
Not knowing exactly what the SP being called is doing does make it rather vague as to how best to solve this issue with a set-based solution but it would seem like an issue I had to deal with a few years ago for which we were importing csv data, and originally the UI would loop through the csv and call a stored proc for each row, with the SP returning an OUTPUT parameter containing the ID for the newly inserted row. This was extremely inefficient, took forever to run and was causing our customers to become apoplectic, ranting and raving at our Customer Support Reps. So we had to find a solution that could process the whole csv at once. The UI took the CSV and turned the data into XML which was then passed as a parameter to the SP. The XML can then be processed as a table, although now I have seen articles about using Tally tables that offer better processing performance but the general approach is similar, i.e. pass the entire data set and process as a set rather than using RBAR processing.
Anyway, once the data has been passed to the SP and is available as a set, it can be processed accordingly, no need for cursors or WHILE loops. The trick is to return a resultset of the new IDs for the UI to use, and that's easy as pi.
Seems to me that such an approach would be suitable for the issue portrayed in the article.
Still a very interesting discussion about cursors.
Michael MacGregor
Database Architect
June 13, 2008 at 8:56 am
Yeah the discussion on the article is much much more interesting. One can learn a lot from these type of discussions. Though I like cursor programming very much, some of the points made in the discussion are very valid.
June 13, 2008 at 8:58 am
Michael MacGregor (6/13/2008)
The trick is to return a resultset of the new IDs for the UI to use, and that's easy as pi.
Not being able to resist . . .
precisely how easy is pi? :hehe:
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 15 posts - 121 through 135 (of 296 total)
You must be logged in to reply to this topic. Login to reply