Cursor type for performance

Question

Cursor type for performance

Ramanathan Meyyappan

SSC Rookie

Points: 47
More actions
December 15, 2005 at 10:35 pm

#111033

Which type of cursor can give best performance under the following scenario:
Large amount of data to be fetched.
Records are not be locked at the time of reading.
Movement of the cursor is Forward only.(UserID=290224)
Thanks and regards,
Ramanathan M.
Thanks & Regards,
Ramanathan M.
December 19, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 1

Go on then, i'll do it....

Can you give us a little more detail on what it is you're trying to accomplish, as everyone knows on this site cursors are very bad performance wise for 99.999999995% of the time (i think was generous enough?).

If you can give us a little more detail as to what you're trying to achieve i'm sure that we could come up with a set based solution that will blow the cursor out of the water.

Mike

Ramanathan Meyyappan SSC Rookie Points: 47 More actions · Answer 2

I know, cursors are DON'T GO THERE!! area in SQL Server.

I've 2500 records of financial postings to be done in production server. Each record has to be validated for account code, posting date, etc., If all validations are ok, then an insert has to be made in finance book table.

I've used FAST_FORWARD cursor. Tell me, if you have any suggestions.

Thanks & Regards,
Ramanathan M.

magarity Ten Centuries Points: 1037 More actions · Answer 3

Have you considered a validation constraint on your insert rather than a cursor? But 2,500 records isn't much in the scheme of things as long as the checks aren't too bad. It shouldn't take long unless your server is just really busy. You can also consider bashing out a VB program that will do the validations instead of SQL Server.

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 4

I agree with Mike.

Can you break up the seperate validations in set-based validations?

Mostly every week I hear a story of a cursor getting replaced with a set based solution reducing duration from 3 hours to below 5 minutes.

Ramanathan Meyyappan SSC Rookie Points: 47 More actions · Answer 5

Too many validations to be done for each record before making a posting. Server is also too busy. Cursor in my application is unavoidable.

I've tuned the code, to complete the cursor as soon as possible. Its working fine now.

Only thing I would like to know now is, is fast_forward better than forward_only cursor in this scenario?

Thanks & Regards,
Ramanathan M.

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 6

fast_forward read_only cursor would be the fastest

see fast forward only cursor in the books online