SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursors - need to help to explain why to not use them


Cursors - need to help to explain why to not use them

Author
Message
smv929
smv929
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2231 Visits: 1137
I'm a junior DBA and know that everyone book and DBA guru says to rarely use cursors because their not as efficient as the set-based approach and the cursor logic goes against what SQL is designed for. However a programmer/co-worker recently gave me heat because I rewrote his procedure to be set-based rather than use a cursor to perform INSERT and DELETEs. It's a delete and archive procedure that basically moves old order-related data from several source tables to corresponding archive tables. The databases size usually are around 1.5 million records.

He likes the cursor because it's reliable, although it takes a long time. He has had problems with set based approaches for moving large amounts of data in the past because one time it taxed the database hard. But that's because he was runniing the delete and archive for the first time -- in that case, it had to move a lot of records to delete and archive. The procedure is designed to run daily and when do so fewer records are moved and thus it's less taxing on the database. I have since improved it by limiting the rows in each transaction. But he still asks me to tell him why even the improved set-based procedure is better than the cursor. I can show him countless books that say it's bad, but he's wanting evidence. Any suggestions on what I can present to him to make my argument against cursors? By the way, he thought that I never used cursors because I just didn't know how to program them! Arrogance!


smv929
Steve Jones
Steve Jones
SSC Guru
SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)SSC Guru (610K reputation)

Group: Administrators
Points: 610988 Visits: 21175

Cursors are less efficient, but sometimes are the best approach. As for why he chose cursors for this issue, I have an idea. Here's my approach:

http://www.sqlservercentral.com/columnists/sjones/batching.asp



Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Prasad Bhogadi
Prasad Bhogadi
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8319 Visits: 450

Well, There will be many instances where Cursors come handy and even performance efficient while doing batch updates, so I believe that outrightly we cannot make a statement that Cursors are inferior to set statements, however it has to be decided on a case basis.

Thanks



Prasad Bhogadi
www.inforaise.com
LondonNick
LondonNick
SSChasing Mays
SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)

Group: General Forum Members
Points: 649 Visits: 520

Yes as mentioned, try to avoid believing that cursors are always bad. Sometimes their performance can be as good as, if not better than other approaches. However, when using them you really should try to explore other ways of doing things as more often than not, they're not really required.

I think the main problem with cursors is that they present a certain way of thinking. That way of thinking for some people can be easier to visualise than other approaches. However, many times there is a set based solution to what the cursor is being used for. I think the best analogy I've heard is to think of the task of putting sugar in a coffee. You can use a pair of tweezers and take one grain at a time or you can use a tea spoon and do it in one go. You want to make sure you select the tea spoon approach. The tweezer approach might be technically correct but you're leaking performance. So when using a cursor see whether you can do bulk processing on the records you are feeding into your cursor rather than against each record in turn.





OrbMan
OrbMan
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 32
An example I can give is that I turned a 27 hour legacy stored procedure (Oracle, running on some pretty big hardware) into a 5 second stored procedure, by switching it from cursor to set-based. Those are the actual, non-inflated numbers. My manager would not believe me when I told him, and said bullsh*t several times over after hearing me say 5 seconds. But he didn't make me switch it back.

I have met many DBAs who write cursor-based routines almost exclusively. Like your guru who assumed you didn't know cursors, I assumed they don't know GROUP BY But of course everything has its place in this world, sometimes cursors are the way.
smv929
smv929
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2231 Visits: 1137

Thanks to all for the respones. Please don't misunderstand me. I do actually believe cursors are good and do consider them. However, I tend to 95% of the time envision a set-based technique that works great, so I rarely have to resort to them. Whereas this other guy (and other programmers here) always suggest a cusror first because I think they are not comfortable with UPDATE/DELETE joins or ignorant of using CASE in such statements or derived tables. I was just frustrated because of their SQL ignorance and arrogance assumingthat I just don't know how to program!

I explained to him the moving sugar analagy (with a tweezer vs. teaspoon) and that performance is better with a teaspoon. He basically argued that if the data is too big, a shovel instead of a teaspoon would be used which would kill the performance. I told you limit the transactions to a tablespoon, but he didn't buy it. I guess I will have to time both techniques and grab statistics to show him.




smv929
mssql_rules
mssql_rules
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1783 Visits: 1

From my experience, use cursors only when there is no set based solution. And trust me, there are quite a few where there is just no set based solution (for example list files in each database from sysfiles, or users for that matter). If there is a set based solution, that beats the cursors hands down, every time I used. Even I used to use cursors until I have seen some very elegant solution from SQL gurus on this forum and other places that do not require cursors. One caveat is that when your using a set based solution brings more data into memory and tempdb than the system capacity I may use cursors to stay within resources. Once again I see this as a system resource limit than the set based solution limit. One example is if you have three very large tables (10 mil each ) a set based solution joins them and criteria requires about 1 million records from each table, your temp db should hold 1 mil * 1 mil * 1 mil rows. If you do not have that space, you may limit the query to go againt N records each time using the cursor depending on the memory and tempdb space.





George Hayduke
George Hayduke
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 1

I've also heard that cursors are generally not fast, but for many of the things I've been writing lately, I've used cursors a lot. An alternative I've come across is using a table variable, and performing what I need on the table records one by one, essentially mimicking a cursor.

In addition, can anyone shed some light on cursors vs. table variables, where each is stored, and the pros/cons of each. It seems like they could be used equivalently, so I'd like to know how to choose which is better for what I'm doing.

Many times, I need to do several things with each record (insert/update records in several supporting tables based on the cursor record and/or values derived from values in the cursor record), and that's why I've been using cursors. So far, I haven't run into any speed issues; however, my cursors typically hold up to perhaps 30,000 records. However, if anyone has any better approaches, I'd like to hear them.


Mike Good
Mike Good
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6258 Visits: 1244

I have similar purge/archive routines. In dev environment I can run these as single large delete, and they run twice as fast as when cursoring thru and deleting 1 row at a time.

But in busy prod environment, single large delete (not that big, say 20-50k rows from 10M row table) never finishes. That is to say I have never waited long enough to let one of them because a) it takes long time, and b) it causes massive blocking.

I think you cannot win your argument, in general, because there are times when you cannot exec one big stmt, either takes too long or blocks too much, or grows txlog too much, etc. One could argue that your co-worker's approach at least will always perform reliably & predictably, whereas yours may or may not work at all. But you should be able to prove one way or the other whether yours works and which approach is faster in your environment.

Be sure to run your test either in prod or prod-like environment to mirror contention issues that will face your real routine. Measuring IOs may show no differences, I'd be interested in time duration and blocking impact. Post your results!





philcart
philcart
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37243 Visits: 1441
Just to add another twist in the story.

We have a regular process that archives records on an hourly basis from a table that has 10mil+ rows. Each hour approx. 1000 records get archived.

This is all done without cursors using simple a WHILE loop and limiting the number of rows using SET ROWCOUNT x.

This gives the best of both worlds, set based insert/delete's as well as a cursor like process that minimises impact.

Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search