September 9, 2016 at 7:29 am
Well, I haven't used a cursor in years. Of course, I've been retired for a number of years too. :-). Anyway, I'm not sure I would use MSFT as an example to justify it either way. The judicious use of cursor should always be based on the individual situation. To me one of the main considerations is going to be the expected volume of rows to be processed, the more rows the more questionable the use of the cursor. More is probably not going to be better.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
September 9, 2016 at 7:53 am
One of the most frustrating things in my current job is that I sometimes spend hours rewriting an existing stored proc to eliminate cursors only to find there is little to no benefit. Obviously this isn't the case all the time, but it underscores the need to do a-b testing and not assume when it comes to MS-SQL.
September 9, 2016 at 8:06 am
Carlo: "So, except some rare case, there's no reason to use cursors!"
Which, I guess, to a point, is what he was saying. I think everyone will agree that there are times (rare cases) where you can't do it any other way except with a cursor. Let me rephrase that or someone will take exception: there are times (rare cases) where using a cursor turns out to be the more efficient way of doing something. Sure, you might be able to replace the cursor with 4 back-to-back queries that populate temp tables, query a temp table into another temp table,. built a recursive CTE off the final temp table then use a WHILE loop against the final result, but really! Just to avoid using a cursor?
September 9, 2016 at 8:07 am
dbishop (9/9/2016)built a recursive CTE off the final temp table then use a WHILE loop against the final result, but really! Just to avoid using a cursor?
A WHILE loop is just a cursor in disguise--it's still row-by-row processing, which is what you want to avoid when possible.
September 9, 2016 at 8:09 am
There's not that can be added here.
The times I use a cursor are when I can't find a set based way of doing something. In which case I tend to grab the information I need and put it into a temp table the use the cursor on the temp table.
As with everything its a trade-off between concurrency and performance. If you can't do it set based you have to determine whether the RBAR method is taking place on something with a concurrency consideration or not.
I'd keep quiet about the existence of cursors and then strongly discourage their use simply because you don't want people to get into the habit of using them.
A use case may be completely valid but you don't want a solution for an edge case becoming the norm.
As for Microsoft using cursors....my doctor smokes.
September 9, 2016 at 8:21 am
One thing that would be useful to see is the row counts in the objects that have cursors.
If you are looping through a list of databases in a master db that would make sense versus looking through a billion record transaction table.
Context matters.
412-977-3526 call/text
September 9, 2016 at 8:29 am
patrickmcginnis59 10839: What I like is that if you Google "considered harmful" you will find a plethora of articles on things considered harmful in the programming world. The one I like best is Eric A. Meyer's essay of December 2002, entitled ""Considered Harmful" Essays Considered Harmful"
Or, how about John McCarthy's December 1989, essay published in ACM and entitled "Networks Considered Harmful for Electronic Mail". 🙂
September 9, 2016 at 8:29 am
David.Poole (9/9/2016)
There's not that can be added here.The times I use a cursor are when I can't find a set based way of doing something. In which case I tend to grab the information I need and put it into a temp table the use the cursor on the temp table.
As with everything its a trade-off between concurrency and performance. If you can't do it set based you have to determine whether the RBAR method is taking place on something with a concurrency consideration or not.
I'd keep quiet about the existence of cursors and then strongly discourage their use simply because you don't want people to get into the habit of using them.
A use case may be completely valid but you don't want a solution for an edge case becoming the norm.
As for Microsoft using cursors....my doctor smokes.
haha.. "My doctor smokes". That just makes it so clear. Well said.
Anyway, my take on cursors is it's fine to use for database administrative purposes to run queries through SSMS to get some tasks done. Usually only the dba sees the script and if it begins to slow down due to more data, well, he can modify the script. I never allow programmers to submit a stored procedure to a production OLTP database that uses a cursor for their application/service. We either figure out a set base approach and if that becomes too much of a brain drain then I tell them they must query the data into their application and do their RBAR processing in the application. Programs fair extremely better at the RBAR approach then the SQL Engine. So, basically, use the proper tool to do the task at hand. Having sprocs with cursors in production systems is just an event waiting to happen as data grows. And when after many months customers start to complain their application is slow, we have all but forgotten why that maybe and the finger pointing begins :hehe::w00t:.
September 9, 2016 at 10:11 am
Thanks for this demonstration. I have been programming for 50 years. I love these set operations and efficiencies we have but just rarely we still need to be able to provide row by row operations and a gentle GOTO. It is always important to know when to use what to generate efficient, maintainable, and reliable code.
September 9, 2016 at 10:39 am
RonKyle (6/1/2015)
While a programmer, I never heard that GOTO was bad. Why is that so bad?
A bit of ancient history.
The problem with the GOTO was magnified by the style of teaching computer programming up to the mid-1970's.
For instance, the single sort that was taught was the bubble sort, without any mention of scaling issues which would warrant the use of a less-easy-to-understand method.
There was no effort to even address coding style - clarity was not a concern. Instead, you had to rely on hand-drawn flowcharts with lozenges (two-path decision) and rectangles (action) to guide you through the program. A long program required at least two of the 11 x 17 "pages of "free" paper from the batch printer (one-sided printing of the previous listing) tied with scotch tape. You could use a plastic template so the shapes looked clean. And only a fool would use ink instead of a lead pencil. Yes the flowcharts had to be redrawn any time a correction was required in the code. Yeah - right.
Forget about indenting code blocks. All FORTRAN code started at col 7. Even numbering the labels in some kind of a sequential ordering was already asking for too much. With GOTO's you had the "freedom" to jump forward and back, so "creative" programmers could re-use part of the code to "save" some lines of code. "More efficient".
The end result turned into spaghetti coding. Indeed, trying to follow a sequence of instructions was like trying to follow a long noodle intertwined with many other noodles.
About the only thing missing from this mess would have been a "COME FROM" statement.
New structured constructs such as IF - ENDIF and indented code blocks helped bring sanity into code production. By making GOTO's non essential, a major source of grief was taken out of the picture. So obviously simple as they sound now, these concepts were a revolution when they first appeared. Bad programmers could be helped to write less messy code. But even structured programming was not a bullet-proof shield against bad programmers.
I sincerely hope you missed out on this less than glorious era of computer programming. Believe me, abuse of the GOTO statement was the most visible culprit of unmaintainable, bug-ridden spaghetti code. The GOTO statement would remain tarred forever - even though the real culprit laid at a much deeper level: poor teaching.
September 9, 2016 at 1:18 pm
I've used them. Most memorable was a large calculation routine where the cursor went month-by-month, recalculating a year's worth of data where one month's result was based on the previous month's result etc. This was before windowing functions that made a running sum a bit more of a possibility.
September 9, 2016 at 6:04 pm
dbishop (9/9/2016)
Let me rephrase that or someone will take exception: there are times (rare cases) where using a cursor turns out to be the more efficient way of doing something. Sure, you might be able to replace the cursor with 4 back-to-back queries that populate temp tables, query a temp table into another temp table,. built a recursive CTE off the final temp table then use a WHILE loop against the final result, but really! Just to avoid using a cursor?
I have to admit, I've not yet seen a case where replacement of a cursor has required such an effort. I also wouldn't use an rCTE that processed one row at a time because even just a well written While loop can usually beat such "incremental" rCTEs.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 9, 2016 at 11:03 pm
g.brennan (9/9/2016)
It's not so much a case for cursors as education by/for designers in using the right tools for a given use case.Most developers begin with a row-by-row education then progress to set based so it's easy to default to using cursors as it suits the mind-set.
I remember the early days of VB3 and databases where a typical developer would ask ' just present the data as a single table and I'll handle it in (row-by-row) code'. Education.
Yes most of were taught wrong. It is easy to fall back and use what you already know. Rember that programmers are lazy by profession. To learn is to grow.
The cursor methods grab a record set and then loop through that element by element (row by row). This can hog memory as the whole record set has to stay even if you are totally done with the rows you have past. Remember that is is possible to rewind a cursor and even move backwards.
SQL is a great language for many things and not great at others. It excels at set based and is is not great for what we call procedural stuff.
I would like for some body to test what happens if a procedure that uses a cursor is re-coded as a CLR proc where FOR loops (like a FOR EACH) are efficient. Any volunteers.
BTW: Sorry for being MIA these last months
ATBCharles Kincaid
September 9, 2016 at 11:40 pm
Charles Kincaid (9/9/2016)
I would like for some body to test what happens if a procedure that uses a cursor is re-coded as a CLR proc where FOR loops (like a FOR EACH) are efficient. Any volunteers.
I know of at least one instance where that's already been done. CSV splitters. Set based methods pretty much blow the doors off the close relative of Cursors... While Loops. And the CLR was about twice as fast as that until 2012 came out, where it's pretty much a dead heat because of "preceeding".
Of course, there are other things where I've seen SQLCLR take a beating because of the use of RegEx behind the scenes.
But, I'm with you... I couldn't write a CLR to save my soul but it would be kind of neat to see someone solve a problem such as the "Bin Stacking" problem or some such.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2016 at 11:31 am
Jeff Moden (9/9/2016)
dbishop (9/9/2016)
Let me rephrase that or someone will take exception: there are times (rare cases) where using a cursor turns out to be the more efficient way of doing something. Sure, you might be able to replace the cursor with 4 back-to-back queries that populate temp tables, query a temp table into another temp table,. built a recursive CTE off the final temp table then use a WHILE loop against the final result, but really! Just to avoid using a cursor?I have to admit, I've not yet seen a case where replacement of a cursor has required such an effort. I also wouldn't use an rCTE that processed one row at a time because even just a well written While loop can usually beat such "incremental" rCTEs.
My point was that sometimes a cursor is the best way to process. I too try to avoid them at all cost, and generally will opt for, as you stated, a well written WHILE loop. The second part of my point is those who state "cursors are evil" (and I'll agree, in certain contexts they 'can' be) will go to the extreme of coming up with something that is likely less efficient than the cursor just to say they didn't use a cursor. I've seen it!
Viewing 15 posts - 181 through 195 (of 215 total)
You must be logged in to reply to this topic. Login to reply