Monday, March 19, 2012

Explicit Transactions

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 *..."

No comments:

Post a Comment