I have an cursor that loops 720 times.
Each FETCH does 6 INSERTS.
Each FETCH the 6 INSERTs a total of 200-300 records appended.
Would explicit transactions speed up the cursor?
In Oracle, I would keep track of how many times I've looped, and commit every nth time. I would put a catch remainder COMMIT at the end of the procedure.
I've got the IF @.@.TRANCOUNT > 19 COMMIT TRAN (about 6k records), but say I had <= 19 remaining at the end of the cursor, how do you get those to commit? I tried to put an IF @.@.TRANCOUNT > 1 COMMIT TRAN, but that didn't work.
What's a good @.@.TRANCOUNT to commit at in this case?
Thanks crew,
CarlIs it possible to transform the cursor to a set based operation which may optimize the operation. can you post your code?|||Carl, Oracle is cursor heavy, and often requires their use. SQL Server is optimized for set-based operations, and cursors are usually the worst method of managing data.|||I was able to go set based with the sub procedure at the prompting of you folks. Perhaps I can get rid of this cursor as well. If I can
SELECT CASE x=y THEN INSERT INTO xyz (SELECT * from abc) END
Can you do stuff like this?|||INSERT INTO xyz
SELECT *
from abc
where x=y
...but you should enumerate your columns and avoid the use of "select *..."
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment