When To Use Cursors
-
I have over 20 years experience as a DBA. I have had to use cursors in the past, I don't anymore. I have encountered all manner of different issues where a cursor could be used, such as your DTS example, and I have developed enough code over the years that I almost always have something in my utility-belt that I can use to avoid cursors.
There are, undoubtedly, times when sequential processing is preferable to set based processing, but I stick by my statement that such processing is best handled outside of SQL.
I have to live with deadlines too, but I don't resort to coding inefficient SQL just because I'm under the gun, there lies the road to ruin.
A deadline is only one factor affecting code development. Efficiency, flexibility, maintainability and clarity should also be considered.
I am probably one of those senior DBAs who other people think believe cursors to be evil, actually I don't, I simply don't believe they are a good solution to most SQL problems, and that I can find a better way to deal with the issue. As I previously mentioned, I have not had to resort to using a cursor since 6.0.
-
Its not just as simple as rewriting the code to handle multiple rows. It can be, but not always. One concern is - should all stored procedures be built to accept multiple rows? Suppose you have a stored procedure used by a client app that creates an order and then up to 10 (the supposed max) of order details in one swoop (and maybe does some complicated validation/lookup just for the heck of it). Would you rewrite that proc to pass in a bunch of XML docs so you can handle multiple orders in the case where you get a batch request from a clerical person that has transcribed a bunch of orders from faxes? Or would you build some other table that the fax stuff can be loaded into so that the order proc can join to it? Or do you just loop through the records?
DTS/SSIS rarely eliminate loops, they just hide it. I don't have a list, but I know many of the actions inside the data flow are intrinisically row based. Not that you shouldnt use SSIS! Its a decent tool for many things.
-
Micheal,
Keep in mind Im not advocating the wide spread usage of cursors. As an industry we've made decent progress towards set based ops and I'd like to see that continue.
"A deadline is only one factor affecting code development. Efficiency, flexibility, maintainability and clarity should also be considered. "
I agree, except you omitted 'time to build'. The funny thing is in many cases the cursor would win at all of those. Here's an example. Write a stored procedure that will calculate the score of a bowling game where the results of each ball are stored in a table. It is possible to write a single statement that will do the calc but I bet most of us will not find it easy to do. On the other hand, using a cursor makes it easier to create some logical breaks to help mortals like myself work through the cases. As a business owner time to market/time to solve any given problem is important to me. I don't want spaghetti code or other half baked solutions, but I don't want a developer/DBA spending 2-x times as long as a simple solution just to arrive at a book perfect answer, especially if the performance of the simpler solution is adequate.
-
Wow. Just wow.
Nice job Andy. If you've helped one person, it was worth the effort.
jg
-
I used to be one of those "cursor is a four-letter word" people - I avoided them at all costs. Recently though - call it experience or just getting soft in my old age - I have started to use cursors on occasion. So far, I have not rolled out a cursor-based solution to a live production environment; I limit the usage to internal scripts or processes that I used for development and ETL where performance is not an issue.
Good article Andy.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices -
I am very glad that someone had the stones to present this concept. When you blindly accept the rules, you eliminate the possibility of an exception. My experience has been that cursors should be avoided, unless they are un-avoidable.
Earlier in the post there was a comment about a WHILE loop. If I have a cursor that is reading table data, I have found tremendous performance gains by transferring the results into an (@) table with an identity(1,1) column and looping thru the (@) table in a WHILE loop. Yes, the benchmarking would be useful.
Excellent article.
-
Andy,
I have encountered situations where cursors could have been used many times, and I avoid them. I have enough experience and template code that, in the majority of cases, I don't need to burn the midnight oil to develop an appropriate set-based solution. Maybe it's just the way I think.
I am actually the other way entirely, I would have to stop and think carefully about implementing a cursor. I personally find writing a set-based solution vs a sequential process to be much easier and more natural. Guess I'm just a born DBA.
Anyway, I don't think we will ever entirely agree on this issue, so I guess we will have to agree to disagree, nevertheless an interesting article and subsequent discussion.
Mike
-
Thanks to one and all for providing an interesting discussion. I'll throw my support to Andy on 1, 2 and 3. Time and experience have shown that they work in the situations that I have experienced.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Andy, thanks for being willing (daring) to write such a controversial article. I'm all for dialogue whatever your point of view is.
In my 12 years of SQL coding I don't think I have every rolled out a cursor to production. In my consulting work I have gone from gig to gig rewriting and optimizing SQL code that "is now running very slow" - it is a fun way to earn bread and butter. One of the top things on my list is to look for cursors, and in many cases I will convert them to WHILE loops. I do admit that I leave some of them intact because some of them them are innocuous when I did some benchmarking.
One typical culprit is when someone writes a cursor to process, say, 10,000 rows. Then they leave the company, and 3 years later because the business is growing the cursor now processes 1,000,000 rows and is now hogging system resources.
- Paul
http://paulpaivasql.blogspot.com/
-
I often find myself having to use cursors (i.e. having to call an ActiveX with paramaters being taken from the rows of a table).
Using a cursor here is the only option. I suppose I could get a table row count and keep a counter variable in my SP and do a WHILE on that, or loop if it's < than the row count.
But I can't see that being any faster. And I'd have to scan the temp table (or at least use the index) to get to each row. In the cursor the next row is there for me without additional table access.
-
Adam Machanic posted a comparison of the performance on a 'running total' style problem. He compared set based, cursor and CLR proc solutions. The set based calculation performed worse than both the cursor & CLR solution.
I am aware I have just committed SQL heresy via hearsay but unfortunately I can't find the study any more...
-
Bravo. I'm standing on my desk applauding. I especially agree with one point - "we get paid to solve problems." Most clients don't care if we write "elegant" code. They just want it to work.
I envy those that have unlimited time, patience, resources and intelligence to avoid cursors in every single instance. Although I always try my best, some days my best isn't very good. Some days I don't appreciate the challenge of finding the best possible way to do everything. Some days I'm under a tight deadline. Some days I need a "quick and dirty" solution.
Who ever said that "if a job is worth doing, it's worth doing right" is wrong. Some jobs just need to get done as best you can. I agree that cursors should not be our first, or even second, choice, but I think that these people who are making a Holy War out of avoiding cursors need to get over themselves!
-
woops spoke too soon, here are the links
CLR solution:
http://blogs.sqlservercentral.com/blogs/amachanic/archive/2006/03/01/510.aspx
Cursor vs Set based:
http://blogs.sqlservercentral.com/blogs/amachanic/archive/2006/02/28/508.aspx
-
Did he use a triangular join or a temp table solution??
I guess that even the temp table solution would be very comparable to the cursor if not faster.
-
I also read that article and can't find it anymore :-).
The important thing, I think, is that there are underlying REASONS why cursors perform badly (or well) in different circumstances. That information is available if you take the time to find and digest it. "Cursors are always bad," "Stored procs should always be used," and other such blanket rules, just mask the real reasons -- and thereby can steamroll right over legit exceptions. Sometimes it's convenient to do so (e.g. "just tell all the young devs to stop using cursors") because the subtlety of the problem is lost on people, or they don't care enough to invest the time, or are in to much of a rush -- but if you are really serious about this DBA profession, it pays to know what's really going on under the hood, and decide accordingly.
Viewing 15 posts - 16 through 30 (of 81 total)
You must be logged in to reply to this topic. Login to reply