"With hold" cursor

[Dagon]

I have a stored procedure which reads from a source table, does some updates, then sets a status on the source table to indicate the row was processed successfully.

After a fixed number of records, it commits. Unfortunately, it re-opens the cursor after the commit and I think this is causing it to run very, very slowly. I want to change the cursor declaration to have the "with hold" option on it so that I can leave it open during the commit.

I was wondering if anyone could see any problems with this. For example, will I run into locking problems when I try to commit the update on a table which has an open cursor against it ?

 

[FearsomeFastFemale]

I would do this my keeping the rows unique key and using that as a greather than condition when opening the cusor, or by excluding rows with the process indicator set. I have not tried 'with hold' in this situation but I can see you can commit if it is held.

HTH

FFF

 

[Dagon]

The "with hold" seems to work OK. I'm not sure about your suggestion. It would mean ordering the select by the unique key, which would slow the query down. It can't use the index to do the order by because it is selecting non-key fields as well. The "greater than" condition would only start to be efficient once the number of rows in the source table had been reduced considerably.

 

[chris72]

are there indexes for all foreign key?

 

[gregsimpson]

I personally would use the "WITH HOLD" option but ORDER the CURSOR on the Partitioning INDEX. You are then much less likely to suffer from INDEX creep and will hopefully only be working on 1 partition at a time for most of the time.

Cheers
GREG