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:
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.
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.
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.
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.
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.
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!