June 1, 2015 at 7:20 am
There are other cases for cursors as well. First, let's get real here and everybody admit that we're not all working on high-speed trading systems where every nanoseconds counts. I have seen SOOOO many ridiculous SQL acrobatics done "Just because you can". Statements that are undreadable and unmaintainable. Ultra-long lines of SELECTs with nested, embedded CASE statments, complex AND and OR operators where the slightest mistake the position of a paren will still allow the statement to run, but UTTERLY DESTROY the intent of the statement.
This is not maintainable code. Just because some hot-shot said "Hey, I can do this in one SQL statement. Look at me--oh--and please, protect your knees as you cow-to my brilliance", and and he/she's spent hours (if not days) constructing it and finally getting it wor work--even they may not understand that in one week. When the business rules change, and you've got to move those parens around and change this AND to an OR and deal with all that???
No.
Sometimes top-down procedural loop on a Cursor is the more easily understandable and maintainable way to code something. Sure, like I said--you've got to consider performance but again--most of use can afford the the perf penalty in return for ease of understanding and maintenance in at least some of these cases where SQL-Peackocks write some of this unecessarily unreadable code.
And let's also consider the other reality--just because *you* may think you're a sql God--chances are the person maintaining that in 6 months won't be.
June 1, 2015 at 7:23 am
The author hit on a topic that should be near and dear to all of us and that is the ability to read, decipher and modify other developers SQL code. In many cases there are always better ways to do something and sometimes these "other" ways improve performance only marginally. This is not a bad thing at all as we should always strive to produce better performing code when we can. However, sometimes these ""better" ways of coding are a nightmare to understand, debug, modify etc... We need to look at our environments and make sure that we are putting policies and procedures in place that not only keep performance in mind, but also the ability to quickly and accurately debug and maintain our code across a group of developers. If some code is written that performs great BUT is so hard to understand that other developers take forever to modify and /or introduce bugs into the code when trying to modify it, what good is it?? I would rather standardize on a method of writing code and handling certain situations that makes it easier for all developers in a team environment, senior and junior alike to be able to understand, debug and modify a piece of code in the required amount of time than to have code that may perform better but can be extremely complicated to change.
Having said all of that, training of course is huge in a team environment and when a better method of writing code for certain situations arises, as long as we can ensure that all developers in the team(s) are well trained in how this new code is written and understand how it works, then by all means we need to migrate away from the old methods and onto newer methods...
Just my .02
June 1, 2015 at 7:25 am
While a programmer, I never heard that GOTO was bad. Why is that so bad?
Comes from "Edsger Dijkstra's letter Go To Statement Considered Harmful, published in the March 1968 Communications of the ACM". (from wikpedia)
June 1, 2015 at 7:34 am
There are absolutely times where cursors are the best coding methodology for the task at hand. I think this editorial would have been a LOT more persuasive if we had some examples of when a cursor is appropriate. Just saying that cursors are OK because Microsoft uses them does not make evoke much persuasion.
As far as the responder complaining about people using other constructs to avoid cursors as being sql peacocks or otherwise some form of arrogance I am saddened that your experience with knowledgeable t-sql developers has been riddled with that type of mindset. What is worse is that the code you have experienced is either so brittle or so poorly formatted that you can't tell where the parenthesis belong.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 1, 2015 at 7:34 am
patrickmcginnis59 10839 (6/1/2015)
While a programmer, I never heard that GOTO was bad. Why is that so bad?
Comes from "Edsger Dijkstra's letter Go To Statement Considered Harmful, published in the March 1968 Communications of the ACM". (from wikpedia)
link:
Go To Statement Considered Harmful
So, GOTOs have been "considered harmful" for almost 50 years!
It's a little surprising to me that no one has riffed that title to write a "Cursors considered harmful" article yet.
Gerald Britton, Pluralsight courses
June 1, 2015 at 7:44 am
And let's also consider the other reality--just because *you* may think you're a sql God--chances are the person maintaining that in 6 months won't be
For as much as I know about TSQL, I would have a lot of trouble maintaining a database with a lot of cursors. Passed on reviewing a job once because it has a requirement for strong knowledge of cursors. This isn't abnormal. I can program in Access, but could not do macros.
June 1, 2015 at 7:50 am
Microsoft Uses Cursors. Boy, if I used that argument, I could get away with almost anything. If I looked hard enough I could probably find some code provided by Microsoft that stated something like:
DECLARE csr CURSOR FOR SELECT 1
OPEN csr
FETCH NEXT csr INTO @one
...
likely left over from code incorporated into SQL Server 6.0 that was never updated. Please, if you are going to justify doing something (and yes, I will agree there are some valid uses for cursors) don't EVER use the justification, "Microsoft does it!"
June 1, 2015 at 7:56 am
There are times in system design when one may choose to implement significant chunks of business functionality in T-SQL, as opposed to a non-database language. The reasons for doing so are beyond the scope of this post. When programming business rules and logic in a non-database language, one frequently uses non-set operations. When one is programming those same rules and logic in T-SQL, it often makes very good sense to use the procedural-programming features of T-SQL, including row-by-row processing of data sets. This is where cursors really shine. For example, I have used them very successfully in data warehouse ETL operations, especially the Transform and Load sections.
June 1, 2015 at 8:07 am
While I appreciate the hair the author had to say that not all cursors are bad, I find this article woefully short of any meat on the subject.
First, just because MS uses cursors, doesn't make it right. It simply means that it works. Much of MS cursor code works only because they expect a small number of rows and, in real life, that's not usually the case. Even when it is the case, you have to be really careful that no one in desperate need of a solution copies the cursor code for a much larger scale problem.
Second, the interpretation of "Avoid using row-by-row operations when possible." is absolutely spot on but it does not provide the rest of the information and that is 99.9% of the time, it's not only possible but, when done correctly, will also provide better performance and less use of resources, as well. Some will also argue that doing it correctly takes extra time and that's absolutely true if you don't actually already know how to do something correctly. The only way that you're ever going to learn that is to stop using cursors instead of continuing to use them just because you think that it's not possible or it's too hard.
And, I have to tell you, using rCTEs (recursive CTEs) is frequently no better and can sometimes be worse than a cursor or While loop. Unless multiple rows are handled by an rCTE (such as in the conversion of "Adjacency Lists" to HierarchyID where each iteration handles an entire set of rows for each level), rCTEs are just another form of RBAR.
To be sure, this article is way too short and contains no information on when it might or might not be appropriate to use cursors and falls just short of being a simple rant.
And, yes. I do occasionally use some form of RBAR to solve problems where it could not otherwise be resolved (such as "bin stacking") so please don't take any of the above as an anti-cursor rant because it's not.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 1, 2015 at 8:10 am
g.britton (6/1/2015)
patrickmcginnis59 10839 (6/1/2015)
While a programmer, I never heard that GOTO was bad. Why is that so bad?
Comes from "Edsger Dijkstra's letter Go To Statement Considered Harmful, published in the March 1968 Communications of the ACM". (from wikpedia)
link:
Go To Statement Considered Harmful
So, GOTOs have been "considered harmful" for almost 50 years!
It's a little surprising to me that no one has riffed that title to write a "Cursors considered harmful" article yet.
IN MY OPINION, cursors aren't fundamentally faulty like goto's, its just that some implementations and uses of cursors are really really poor performers. They can also impede parallelism and other optimizations because of the non declarative nature. Set based code is declarative in nature, and based on that, the optimizer can substitute equivalent set expressions, whereas the optimizer can't do this (as well?) for procedural code.
So cursors as a programming construct are fine on the one hand that they don't impede programming clarity like goto's can and do, but on the other hand, they lack the efficiency advantages of declarative code.
I am also personally suspicious that Microsoft's T-SQL interpretation in particular exacts a pretty severe penalty that can be amortized overy many rows by set based operations, and a cursor has to interpret programming lines for each row processed so it can't reuse individual line interpretations, but I have no real specifics on that (besides a few experiments I ran).
June 1, 2015 at 8:10 am
peter.row (6/1/2015)
Added a cursor just the other week when a recursive CTE used in a cross apply was causing a query to take over the .NET default 30 second timeout - 34-40 seconds in some cases.I removed the cross apply and used the same recursive CTE in a cursor on the same data set and boom it took only 2-3 seconds to populate a temp table that I inner joined to the original query.
You should post that code because it might just be that no form of RBAR is necessary at all. Remember that rCTEs are NOT necessarily a form of Set Based code. In fact, they are typically a form of RBAR on steroids that can frequently be pants even by a lowly While Loop never mind a properly written cursor.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 1, 2015 at 8:15 am
qbrt (6/1/2015)
RonKyle (6/1/2015)
Sometimes we use CURSORS in our order processing environment to move data between systems and changing statuses on each record processed. The main reason for using a cursor is that one record MUST NOT stop other records from being processed.
Depending on the complexity of what you're doing, you could consider using set based queries to test the data and then flag the rows. I have the same situation with the OLAP download. Single records can't stop the rest going forward. Each record has a flag that is default No, and only updated to Yes when all the checks are based.
That's a good point. Unfortunately, testing for valid data (ranges, types and all other variations) on most columns is not worth maintaining that type of code and running it when there could be a bad record once every 3 months. Better to just fail, alert and act on it while other records are unaffected. Would be awesome if set based operations could log the failing records somewhere and throw it out of the set and continue processing. That would be a great feature. 😀
Actually, it usually IS worth maintaining both from a performance aspect and a resource usage aspect. Done correctly, doesn't take much, if any, maintenance at all.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 1, 2015 at 8:19 am
There are absolutely times to use cursors. Creating a procedure to automatically rebuild or reorder indexes throughout your entire database is one.
The "Don't use cursors" is primarily a warning to junior developers or to people more used to C# or similar languages. They jump to cursors as a default way to process data. You should try to go the set-based way first and if you find there's no way, then turn to cursors.
Sometimes, on small data, cursors will be faster, but you have to make sure that set isn't going to scale up. Cursors tend to be horrible at scaling.
Is maintaining a complex SQL query more difficult than maintaining a cursor? Sometimes, but in the end, performance is king. The code isn't there to make your life easy, it is there to make the system usable and the end-user's life easy. Make it easy to maintain, sure, but it can't be at the cost of end-user happiness.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 1, 2015 at 8:22 am
Creating a procedure to automatically rebuild or reorder indexes throughout your entire database is one.
Great example if you are going to do something other than simply reindexing the entire database. So I guess I do use one other cursor. (I didn't write it, but see no way to replace it and am not trying).
June 1, 2015 at 8:24 am
I fall into the "avoid row-by-row operations" camp. Around here that's commonly referred to as RBAR. Itzek Ben-Gan, an well respected author an authority on T-SQL warns against iterative approaches to SQL problems (for which cursors are one). I fall into that camp because I have learned enough about T-SQL over the years to write queries that perform much better using set-based methods than their iterative/RBAResk counterpart.
Obviously, I don't fall in the die hard, anti-cursor camp. That doesn't mean I can't be converted, but again I haven't heard or read a definitive reason why cursors are so evil. Maybe it's time to change the mantra from "avoid cursors" to "avoid row-by-row operations"
If you have any queries that use a cursor, post them on a SQLServerCentral.Com forum and ask, "can anyone improve this? Make it faster? Cleaner?" If you do that I promise you will be converted rather quickly. This I can absolutely promise you.
Cursors are not evil. They are just slower and uglier than set based code written to solve the same problem.
-- Itzik Ben-Gan 2001
Viewing 15 posts - 16 through 30 (of 215 total)
You must be logged in to reply to this topic. Login to reply