Which Rows in Cursor?

Question

Which Rows in Cursor?

Viewing 4 posts - 16 through 18 (of 18 total)

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

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 1

ken.trock (6/15/2009)
Thanks for looking at this. I'll take a look at table vars and error handling. These procs operate on anywhere from several hundred rows to a worst case scenario of 100k+ rows. It can chug along for a few min.
Ken

[font="Verdana"]With the potential for 100k+ rows, taking the copy is going to be quite an overhead (you will get that same overhead with a static cursor.) You could probably still use a table variable even in that case, as you're only reading one row at a time for it, but it would be safer to use a temporary table.

Maybe a compromise: take a copy of the keys for the rows you want to process. Then inside the loop look up the details for each row using the next (copied) row key.

If you're really processing 100k+ rows... doing it row by row is going to be slooooow. If the performance isn't an issue, then who cares I guess. But if it's a concern, refactoring to process the entire set would do wonders.

[/font]

ken.trock SSCertifiable Points: 5147 More actions · Answer 2

Sorry Bruce, Jeff had asked how much data 1 of the procs inside the loop would operate on. That would be thousands. The size of the cursor itself is really small; maybe half a dozen rows.

Ken

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 3

[font="Verdana"]Whew!

In which case, a table variable being passed around would be fine.

[/font]

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 4

ken.trock (6/15/2009)
Thanks for looking at this. I'll take a look at table vars and error handling. These procs operate on anywhere from several hundred rows to a worst case scenario of 100k+ rows. It can chug along for a few min.
Ken

I've not read all the responses you've gotten but the key here is that you're NOT using the procs to process 1 row at a time. You're using a cursor in a fashion similar to certain admin jobs... you're using the cursor to control the process... not process rows one at a time. Process control is one of the few places where I take no exception to the use of loops. Sure, there're ways around using cursors and While Loops even for this... but this is an OK place to use a loop.

--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)