April 1, 2014 at 2:39 pm
I was reviewing a process with a colleague and mentioned cursors. He said, "Please don't use cursors as it is really hard on the server." I know there are case by case instances but is this generally true? Is it considered bad programming to use cursors?
April 1, 2014 at 2:43 pm
Rog Saber (4/1/2014)
I was reviewing a process with a colleague and mentioned cursors. He said, "Please don't use cursors as it is really hard on the server." I know there are case by case instances but is this generally true? Is it considered bad programming to use cursors?
In SQL Server, yes. The problem is also partly due to the fact that most people use the default settings of the cursor, which is not efficient in most cases.
The largest problem with cursors is that they process the data row by row, while SQL Server is optimized to handle set based queries.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 1, 2014 at 2:56 pm
Since the IO processing from a cursor is not efficient, what are some other alternatives? I know it will vary on the situation but, are there some things I can research 'generically' to determine a more efficient way?
April 1, 2014 at 2:57 pm
Most of the time cursors can be avoided but it's not a religion, there are tasks which do justify the use of cursors. Obviously if an operation can be achieved using a set based methods, it fits the environment better. But some things like one off operations, continued operation where there are serious exceptions and so forth might justify it. All boils down to "it depends".
To answer the question if cursors are hard on the servers, "it depends".
😎
April 1, 2014 at 3:06 pm
I'm not sure that I agree with Eirikur about saying "it depends". I'm sure that cursors are always hard on servers, but some methods can be worse (e.g. triangular joins) and sometimes a well defined cursor is the way to go.
Cursors are not evil, they're a tool that should be use properly.
You might want to take a look to these articles:
There Must Be 15 Ways To Lose Your Cursors... part 1, Introduction[/url]
There Must Be 15 Ways To Lose Your Cursors… Part 2[/url]
And since cursors are simply a while loop:
The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]
April 1, 2014 at 3:10 pm
OK thank you all for the suggestions.
April 1, 2014 at 3:25 pm
Luis Cazares (4/1/2014)
I'm not sure that I agree with Eirikur about saying "it depends". I'm sure that cursors are always hard on servers, but some methods can be worse (e.g. triangular joins) and sometimes a well defined cursor is the way to go.Cursors are not evil, they're a tool that should be use properly.
Almost saying the same thing here, "it depends" on the total cost of the operation 😀
One can concatenate several thousand operations in a dynamic sql string and call in not-cursed thingy, still not set based but not a cursor. Probably a cursor would be better in that situation. I still agree that it should be avoided "when applicable".
April 2, 2014 at 7:45 am
there are tasks which do justify the use of cursors
I'm sure this is true, but I haven't run into one yet. Once I thought I had, but I was wrong. If the DO WHILE command is considered a cursor, as some seem to think, then I use one every few years to populate my warehouse date dimension.
April 2, 2014 at 8:35 am
RonKyle (4/2/2014)
there are tasks which do justify the use of cursors
I'm sure this is true, but I haven't run into one yet. Once I thought I had, but I was wrong. If the DO WHILE command is considered a cursor, as some seem to think, then I use one every few years to populate my warehouse date dimension.
Actually often times a well formed cursor can outperform a while loop. However since you are using it to populate tables a while loop is often a decent way to go about it. You can sometimes use a tally/number table to avoid this type of looping but that is not always the case.
There are many administrative tasks where a cursor is really about the only way to do it. Also, if you are sending personalized emails to a number of people a cursor is often the best choice.
Good grief....I just typed up two paragraphs defending cursors and April Fool's day was yesterday. I am now going to turn in my Anti-RBAR card.
_______________________________________________________________
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/
April 2, 2014 at 9:09 am
RonKyle (4/2/2014)
there are tasks which do justify the use of cursors
I'm sure this is true, but I haven't run into one yet. Once I thought I had, but I was wrong. If the DO WHILE command is considered a cursor, as some seem to think, then I use one every few years to populate my warehouse date dimension.
Depending on your requirements, you might not even need a cursor/while loop to populate the date dimension. You might be able to populate it using a tally/numbers table as we do on our company.
As Sean said, cursors are good for administrative tasks or maybe multi-database queries.
May 23, 2014 at 11:32 am
I would take a cursor over a triangular join if I was reporting on a running total in a columns. That's once instance.
----------------------------------------------------
May 23, 2014 at 12:44 pm
MMartin1 (5/23/2014)
I would take a cursor over a triangular join if I was reporting on a running total in a columns. That's once instance.
That's ok if you can't (or don't want to) use the quirky update or the windowing functions available on 2012 and 2014.
May 23, 2014 at 5:32 pm
Eirikur Eiriksson (4/1/2014)
Most of the time cursors can be avoided but it's not a religion, there are tasks which do justify the use of cursors. Obviously if an operation can be achieved using a set based methods, it fits the environment better. But some things like one off operations, continued operation where there are serious exceptions and so forth might justify it. All boils down to "it depends".To answer the question if cursors are hard on the servers, "it depends".
😎
To add to that, there are a whole lot of people who simply think certain tasks "justify the use of cursors" because they just don't know differently and that frequently results in the inappropriate use of Cursors and other RBAR. A great and prominent example of this is the number of people that STILL post "splitters" with WHILE Loops and rCTEs.
As for "Continued Operation where there are serious exceptions", I suggest that using cursors in such a fashion should be replaced by prevalidating data whenever possible... and it's normally possible. To coin a phrase, "There should be no expected failures." 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2014 at 5:37 pm
MMartin1 (5/23/2014)
I would take a cursor over a triangular join if I was reporting on a running total in a columns. That's once instance.
A fairly rare exception, indeed. Of course, I wouldn't use either even if I needed to work with something less than 2K12.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply