July 29, 2009 at 3:12 pm
Why are cursors even an option in SQL you allways hear your self and others tell newbies to stay away from cursors, and that there is no reason to ever write a cursor. Why would you want to have it apart of SQL if there is never a reason to need it?
Just wondering if anyone knows the answer. I don't. 😉
July 29, 2009 at 3:18 pm
They do have uses. Few and far between, but valid uses.
The usual reason to tell people to "just don't use them", is an overreaction to overuse.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2009 at 3:26 pm
[font="Verdana"]What?!! Then what would we argue about all day? :-P[/font]
July 29, 2009 at 3:30 pm
If there are uses what are they. Just saying there are valid uses doesn't really answer the question.
Who argues about it? I think everyone is in agreement that there bad.
July 29, 2009 at 3:40 pm
I suspect that the SQL devs wanted a way to do bad things like allocate lots of memory and never let it go to see how long the server would last before it died. But, they didn't want to have to use an external program to do it.. Hence, cursors!! 🙂
j/k
but they are unsafe.
July 29, 2009 at 3:40 pm
Here are a couple of uses I can think off, although many will probably argue — and probably with due cause, I'm not looking to fight here 😉
1) Some administrative tasks, like some set of actions on a list of databases or users
2) Executing dynamic sql generated into a multi row result set
3) I'll use a cursor based solution for a one-off fix when I don't have the time or imagination to come up with a set-based solution, and I don't have to commit the code to an application, so no-one'll ever know 🙂
July 29, 2009 at 3:44 pm
Bryan Wright (7/29/2009)
Who argues about it? I think everyone is in agreement that there bad.
[font="Verdana"]I would, for one.
They're bad in many circumstances, I agree. However, they are perfectly valid in some circumstances.
For example, I have some code where the performance isn't critical (it's processing a couple of hundred transactions a day), but the maintainability and understandability of the process is critical. While I can express that in set based logic, even I have difficulty wrapping my head around how that process works. Whereas when it's written as a loop with a cursor (and normally I don't use cursors with loops, but in this case it made sense) the logic is fairly self evident, and hence it is far easier to maintain.
So it takes 2 minutes to run instead of 10 seconds. Big hairy deal. It's part of an ETL package that takes over an hour to run in total, so by no means a critical time portion.
Cursors are a valid part of the SQL programmer's toolbox. They are just over-used and mis-used.
[/font]
July 29, 2009 at 3:46 pm
Cursors are rarely needed to solve problems in SQL.
I have used a cursor in a loop to read a table of linked server names and build identical queries to extract data from each of them. I knew the loop will only pass through a dozen or so iterations so the performance hit was minimal, and it was a quick and convenient solution.
On the other hand I would NOT want to use a cursor to read a single huge input table and do inputs or updates to three different output files. I've seen that done. It may work, but it takes days for it to execute.
The reason for all the warnings is that many developers gravitate to cursors because they are procedural in nature, not understanding the performance differences between a cursor and a set-based solution.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 29, 2009 at 3:51 pm
I consider cursors as a last resort: If I can't find any other way to get the task completed with a "column based" solution I still have the RBAR (= cursor) option.
What you'll see in some of the posts is just the opposite: a cursor is used as first choice even though there is a much faster non-RBAR solution. In most cases a more efficient non-RBAR solution has been provided.
To tell newbies to stay away from cursors pretty much means "to think at least three times about the task if there will be no way to avoid a cursor".
"Never" or "ever" normally aren't terms used by frequent posters around here. Usually, you'll get something like "it depends" or "under almost every condition".
I use cursors for instance to loop through databases for maintenance / cleanup / archiving purpose and a few procedures where I couldn't find a non-RBAR solution yet. But those procedures won't run frequently and aren't performance critical (yet).
To summarize, cursors aren't my first choice but my last resort.
July 29, 2009 at 4:09 pm
I don't know why cursors where actually added to SQL back in version 6.5, but you can bet it was some market pressure by those who don't know how to write set-based code. To a lesser extent, some of the pressure may have come from DBA's who wanted the ability to promote code and do other things to many databases without having to do it all manually.
I strongly recommend the reading of the following article, it's predecessor, and the discussions that ensued after both articles.
http://qa.sqlservercentral.com/articles/T-SQL/66494/
As you've seen, many folks suggest using cursors as a "last resort". The real problem with most folks is that they either give up too early or they simply don't have the knowledge to avoid cursors for things that seem difficult. Even when they do have such knowledge, if you consider such performance nightmare's like those involved with the use of triangular joins and the like, the whole thing boils down to just one thing...
... do you know how to use a database well or are you going to write a cursor. 😉
--Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply