How to divide my transactions
I have a stored procedure which deletes a couple of million records and then re-inserts changed ones.
Right now I have organized my transactions thus:
begin tran - delete records - commit tran
begin tran - insert records - commit tran
I was wondering though whether the following might not be faster:
begin tran - delete records - insert records - commit tran
Because then it might recognize that the new records can take the place of the old ones, and thus开发者_开发问答 be faster?
The transaction boundaries should be based on the logic of what you are doing. In the first scenario you can potentially leave the database in the state that the old records are deleted but the new ones are not inserted. Is this acceptable? Can you recover? If not, then you should use the second scenario.
精彩评论