To Use Cursors OR not To Use What is the Answer !!!

[123ASP]

Falling back on cursors because they remind you of datasets will result in poor performance "

Hi, The above quote was from an article which advice to use store procedure and avoid T-sql in the body of a webpage.
Anyway, To me, I always use cursor when I want to process one row at a time. Is there an alternative to cursor in MS sql server to have similar results.?

thanks
Al

 

[MDXer]

whenever you work one row at a time the result will be poor performance. No one says "Never use cursors" but rather "Avoid Cursors". Sometimes you have no choice but to use a cursor.

I've used cursors when I have to create new fact tables in a DW based off an existing template table or the same with views. I never use cursors to update or process data.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[SQLSister]

You should not think of trying to process one row at a time. Rather you should think of performing operations on a set of data.

For instance you could use a cursor to select a dataset, check each row to see if it meets a condition and update each row that meets the condition or you could write one SQL statement that updates all the rows meeting the condition in one pass. The second way is much faster and uses far less network resources.

The only time you must use a cursor (or a while loop) is when the operation you intend to perform cannot be done on multiple records. In practice, this generally means that cursors are most useful when processing meta data. It would be very rare indeed that they should be used to process ordinary records.

 

[SemperFiDownUnda]

What SQLSister says.

Its an alternate way of thinking.

Sure if I wanted to add 5% to the price of every item in my inventory table I could loop through them and do it one at a time or I could do it with one statement.

Now that is a simple example but its not even hard to do something like this in one SQL statement

Add 10% to all items that have a price below $100, add 7.5% to all items that are classified as childrens toys, add 5.5% to all items that are above $1000, add 30% to all items that have a back order of over 90 days and less then 15 in stock, and add 6% to the remaining items.



Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

 

[jby1]

Just to add to that, when I first started programming SQL I used cursors a lot, as they reminded me of using normal programming languages. I also thought there were many occassions where they were required. However, as I became more experienced I started to consider how I could use set-based operations whenever I thought I needed a cursor. I have not yet in a number of years ever encountered a situation where I wasn't able to use a set-based operation when it seemed at first glance that a cursor might be required. It requires a bit of extra thought, and a different approach, but the performance benefits make it very worthwhile indeed.

 

[johnherman]

There is a guy at Group One software in Maryland (I forget his name) who has challenged his staff and peers to find a problem that he cannot solve except by using a cursor.

My opinion, if it is a one-time job or if performance is not an issue (short runtime), use the cursor if you can't solve it using pure SQL within a short period of time. Your time is worth more than the extra computing time, even if the cursor needs to run all nigbt or on a weekend in the case of the one-time job.

However, if performance is an issue, consult with your peers (including here at TekTips) to try to find the most efficient solution.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[123ASP]

Hi guys, Ok, I got your points, but none of you guys answered my question?
What alternative code I can use to replace a cursor?
jby1 mentioned " a set-based operation " but he did not explain how to create it.

Thanks
Al

 

[jby1]

A set based operation is simply a SQL statement that performs operations on a set of data. For example,

[/code]
SELECT fld1, fld2, count(fld1)
FROM tbl1
GROUP BY fld1, fld2
[/code]

is a simple set based operation which tells you how many occurences of each unique fld1-fld2 combination exist in tbl1. I am sure the other guys can give you some much better and more enlightening examples than this one

 

[jby1]

PS forget the [/code]s, that was my failed attempt at text formatting ;-)

 

[SQLSister]

Suppose you want to insert records to an audit table based on whether the item is closed. You could do this with a cursor or you could do the following:

Insert into AuditTable (field1, field2, field3)
Select field1, field2, field3 from table1
where table1.closed = 'yes'

Another way to avoid a cursor is to use the case statement
or to use a join to another table in an update or delete statment.

 

[NeilTrain]

johnherman, I'd like to know who that guy is, because ive been working on a SQL problem for almost a year now and have not been able to do it without a cursor. I even posted it here [link:

http://www.tek-tips.com/viewthread.cfm?SQID=560412&SPID=183&newpid=183&page=1

]linky[/url] and everyone agreed a cursor was the way to go