When To Use Cursors

Question

When To Use Cursors

Viewing 7 posts - 76 through 81 (of 81 total)

You must be logged in to reply to this topic. Login to reply

Malcolm Daughtree SSCrazy Points: 2382 More actions · Answer 1

I haven't read the entire thread. But WOW all those nay sayers....:w00t: A simple point to consider - SQL server was never designed to be a 'Sequential processor'. That said here is a situation that required a cursor.

I was given the task of creating an automated Emailing system that alerted employees that their timecards were late. This application has no client application. It also has to take into account time zone differences for world wide office locations. All processes are managed by set based inserts and updates including triggers to control the sequence and yes one Cursor.. Why ? because a trigger only fires once for a set based insert or update and I need to sent 1000's of emails every week. Would happily take instruction on a better way ... 😛

I did by the way investigate other ways and software solutions, but nothing that would interface with custom databases/application and multiple timezone/servers ...

Good one Andy

CodeOn:P

Malcolm Daughtree SSCrazy Points: 2382 More actions · Answer 2

Ahhh!

Yup 😀 doin that. Except that the insert into Staging tables (updatable VIEW of delinquent timecards) originates from AUST (as that is where the Timecards a ultimately posted to) and the emails originate from AUST to receipents in other time zones.

But with time difference aside is there a better way send the emails using SQLDBMail and the 'Delinquent View' without a cursor or a 'While' loop ?

CodeOn 😛

humbleDBA Hall of Fame Points: 3490 More actions · Answer 3

I know I'm a bit late in my post...as it references comments made much earlier...

Gives me a bit of confidence to see that I'm using similar techniques to others, such as Phil Factor, and I guess what Jeff had sent to Colin (Colin - was this the same technique? If not, any chance of sharing? - haven't see anything in this post. Hope I didn't miss it :Whistling: ). I tend to use these tricks quite a bit as I don't often make use of cursors and so, whilst understanding how they work and a quick look at BOL to remind me of the syntax, I find the other ways much more readable. When I have carried out some testing, I have found almost always that this technique has worked better than the cursor. In most cases, by a considerable amount and not just millisecounds.

That said, in my line of work, if I go to a company to help resolve performance issues and find cursors are already in-place and which are not affecting performance in any way, then I put on the 'Nice To Do/Look at' list. I have often found that I can get more out other 20%'ers to give me 80% benefits than fiddling with some DBA-admin type cursor. Just to add to the copious links and names re: cursor performance, Itik Ben-Gan has demonstrated some good examples, and demo's, of where cursors can beat set-based solutions (tho, I believe some ANSI SQL statements available in SQL2008 will negate these - and Itzik mentions them).

Calvin Lawson,

Re your statement "I decided in the end that nobody was going to come up with an answer to my previous question about the performance difference between a cursor-based procedure, and one based on 'While' Loops."

I think Noel Kennedy and Kathleen had given some links to Adam Machanic's blog where he demonstrated a number of tests and showed in some cases that Cursors could beat WHILE loops. Not sure if you saw it?

I think this is a great discussion, though there have been quite a number before, and I'm sure there will be again...well, for the foreseeable future anyway... 🙂

Rishi Girdhar SSC Veteran Points: 200 More actions · Answer 4

Hi,

I have a problem and can you suggest a solution without using a cursor.

Problem:

The structure of the Table A is

ID Txt

1 CEO.P.VP.PM.TL.Dev

and of Table B is

ID Node Value ParentId TreeId TreeNode

After the execution of the sp, Table B should look like this

ID Node ParentId TreeId TreeNode

1 CEO Null 0 CEO

2 P 1 1 *P

3 VP 2 2 **VP

4 PM 3 3 ***PM

5 TL 4 4 ****TL

6 Dev 5 5 *****Dev

Please let me know if the problem is not clear. I know the formatting is not proper.

Thanks in Advance.

Jeff Moden SSC Guru Points: 1003870 More actions · Answer 5

Sharon Matyk (1/3/2008)
I needed to get a cumulative running balance column in a table. So I used a cursor. I'm not very advanced but it seemed like the only way to look at a numeric column, then add that value to the row above. (I first set the running balance column equal to the amount column for row 1, and had my logic looking for all rows > 1.)
Further, I had to take my table and move the date value up 1 row (don't ask, it's about manually calculating interest on a bank account). So again, I used a cursor to loop through each row, setting the date column equal to the value in the row below.
Then, I had to set a 'days passed' value on each row, which was the difference in the date column's value to the date column in the row above. (Again, setting the row 1's date passed value to 1 and skipping that in the remaining logic.)
My final table looked like:
identity, date value, days passed, amount, cumulative balance
If anyone has any idea how to do those three things without a cursor, I'd really like to learn. Sharon.

Sharon...

Yeah... I know... I'm a couple months late... But, please see the following URL for a way to do it without a cursor. And it's nasty fast... it'll do a million rows in less than 7 seconds. Get's all the way up to 10 seconds if you chose to do all 4 formulas in the article...

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Malcolm Daughtree SSCrazy Points: 2382 More actions · Answer 6

Rishi,

If you are use SQL 2005 then your problem can be solved using a Common Table Expression. Check BOL SQL 2005. These are awesome, other wise .....

Code On 😛

Rishi Girdhar SSC Veteran Points: 200 More actions · Answer 7

Thanks Malcolm,

I will check CTE and try to implement the solution.

Thanks

Rishi