December 9, 2009 at 2:21 pm
Hi SQLGuru, go to the following URL:
http://www.sqlservercentral.com/Forums/Topic831649-338-1.aspx#bm831800
December 9, 2009 at 2:32 pm
Thanks. Looks like this is the URL template to use to find a post by number:
http://www.sqlservercentral.com/Forums/FindPost831649.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 17, 2010 at 10:06 pm
Why re-post this article?
It does nothing to educate people about replacing cursors with efficient, set-based, code.
However, reading some of the excellent discussion after does 🙂
June 18, 2010 at 1:05 am
antony-688446 (6/17/2010)
Why re-post this article?
I agree fully. It's a terrible disappointment to see such a bad article being re-posted. Especially because people who do not read the comments, or stop after the first page, might come to believe this and think they can improve their code by using some cursor-less iteration method.
For those who do read this page, please keep in mind that:
1) Using cursors in T-SQL code is indeed bad. Usually. But that goes for any kind of row-by-row iteration, with or without the cursor; the proper alternative is to use set-based code.
2) In the very few cases where you do need row-by-row processing, just use a cursor. While it is indeed possible to write cursor-less iteration code that beats a cursor with default options, it is far easier and more efficient to add the correct options to the cursor declaration. If the data to be processed fits completely in cache, use STATIC; if you have more data than cache will hold, use FAST_FORWARD.
But always remember determining the fastest kind op iteration is like finding out if it's faster to enter T-SQL code with one hand tied behind your back and two eyes closed, or with two hands tied and one eye closed. A very interesting discussion from an academic point of view, but if you want to enter the code fast, you open both eyes and use both hands. And you use them to write set-based code, not iterative code.
June 18, 2010 at 2:34 am
The author has made their mind up before putting this to press (as shown in the naming convention 'badCursor' and the article title 'Cursors Be Gone!').
The author has, however, totally neglected to set the cursor attributes which can, of course, show cursors in a far more efficient light. I added one word ("insensitive") and the result was suddenly 50/50. Now ask what the resource hog was.
Its not so much cursors that are bad, as iteration in general. Iteration in sql should only be used if absolutely unavoidable (or for one-time-only data updates and even then be careful!).
June 18, 2010 at 3:15 am
Good effort Gaby but I agree with all the other posts! I tried this method a couple of years back (I've always been anti cursors) but found that the temp table method was only ok for a very small amount of rows. Dare I say it, but cursors do have their place, as long as you know when it's ok and efficient to use them!
June 18, 2010 at 5:31 am
I agree with Pete here. Cursors have a bad rep, but are good for those cases where you actually want to process something 'RBAR' as someone earlier in this thread. Sometimes you don't care if it takes 30mins to update something. We use cursors for administrative processes, mainly for emails from our custom monitoring jobs. I can use a cursor to stuff all error messages into an email body so I can just view one email from all of my servers instead of 50+. We've also used them on very large tables (75+ million rows) for a data migration/backfill. The trick for us was putting an explicit begin tran/commit tran inside of the cursor so my unit of work was kept to just a one row update. Just my .02.
June 18, 2010 at 6:24 am
Wow! 24 pages of posts and counting. Nothing riles up the troops like a cursor discussion!
One thing that has always bothered me is the comparison of relative costs in the batch. It's not enough to know that the cursor costs 60+% and the non-cursor method cost 30+%. It's just a meaningless percentage unless you know all of the statistic of the batches.
Suppose you have two batches, A, and B that do exactly the same thing. A takes 2 minutes to run and B takes 5 minutes. The execution plan of A shows an operation's costs was 80% of the batch. B's plan has no single operation that costs more than 30% of the total batch. Does that mean that A's 80% operation is bad? Not at all. I just know that A is faster. Of course, they would have to be tested on large numbers of rows which could change the results.
🙂
June 18, 2010 at 6:29 am
"Remember... if you're gonna practice the piano, no sense in intentionally hitting the wrong keys. 😀 "
It DOES make sense to intentionally hit the wrong keys. It teaches you what's good and what's bad. And, you may create something great. Experimentation is important.
June 18, 2010 at 6:58 am
OCTom (6/18/2010)
"Remember... if you're gonna practice the piano, no sense in intentionally hitting the wrong keys. 😀 "It DOES make sense to intentionally hit the wrong keys. It teaches you what's good and what's bad. And, you may create something great. Experimentation is important.
Taken out of context here, it does not make sense if you're trying to play the piece that is on the page.
In context of the comment, he is referring to coding in a correct manner, if you intentionally code poorly, you're not 'experimenting', and you're not going to 'create something great', you'll do the exact opposite.
If you're playing a literal piano, and you want to have a sound like Thelonius Monk, then by all means, intentionally hit the wrong keys (but only the ones that are really right by being wrong).
---------------------------------------------------------
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 18, 2010 at 7:03 am
Myself and a number of other MVPs have been hitting Microsoft up to get them to make cursors perform much better on the platform, which would be a benefit to MANY organizations out there, especially those with legacy and/or ISV apps that use them heavily and always will. Keep your fingers crossed!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 18, 2010 at 7:06 am
GabyYYZ (12/24/2008)
Sigh...The while loop I used was only a proof of concept, and was designed to show people the mechanics of breaking out of a cursor mindset. They are both still loops, but the overhead of a cursor is much higher.
If you can get better performance out of a set based query, by all means do so. If you have to use a loop, try the more conventional ones. If you have an issue using the more typical while loops, you may have to use cursors. Perhaps I didn't make it that clear in the article.
I'll focus on a more realistic scenario next time.
Merry Christma-Hannu-Kwanza Everybody!
Hi Gaby,
I agree with this statement to a certain extent. what I dont agree with is the statement that while loops have less overhead than cursors. This is not always true and even not true in MOST cases. The fact of the matter is the performance of the cursor depends on the type of cursor you create. For example, had you declared a local static cursor, you would probably see more comparable results.
My friend Brad Schulz has written a great blog series on the innards of cursors, which I highly recommend.
http://bradsruminations.blogspot.com/2010/05/truth-about-cursors-part-1.html
http://bradsruminations.blogspot.com/2010/05/truth-about-cursors-part-2.html
http://bradsruminations.blogspot.com/2010/05/truth-about-cursors-part-3.html
June 18, 2010 at 7:13 am
There's a funny thing about absolutes "there is 'always' a better alternative to using [cursors]", they're rarely absolute.
I tend to use the @table route as much as I can, but it's not always the better route. So when it comes to which one to use...it depends...
One of the things I noticed though, is that when you've got a person who's use to coding in some other programming language using the WHILE loop is so much easier for them to get than trying to dive into a cursor and understand the craziness/cost.
June 18, 2010 at 7:15 am
TheSQLGuru (6/18/2010)
Myself and a number of other MVPs have been hitting Microsoft up to get them to make cursors perform much better on the platform, which would be a benefit to MANY organizations out there, especially those with legacy and/or ISV apps that use them heavily and always will. Keep your fingers crossed!
...but then I'll have to actually learn how to use them....:-P: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."
June 18, 2010 at 7:21 am
Thanks Gaby -- and thanks to all who added their replies... lots of good ideas have been shared. I'll mark this one as a keeper!
Viewing 15 posts - 226 through 240 (of 272 total)
You must be logged in to reply to this topic. Login to reply