March 15, 2017 at 11:35 am
Hi SSC,
I ran across a block of code today which does deletes from a table in batches of 5000. Nothing crazy there. However the developer wrapped the entire set of batches in an explicit transaction, like so:
declare @rc int = 1
begin tran
while @rc > 0
begin
delete top (5000)
from myTable
select @rc = @@rowcount
end
commit tran
Am I missing something obscure that this accomplishes differently than just doing it all in a single transaction? I thought batching was largely there to reduce blocking and more importantly transaction log bloat. But if everything is taking place in an explicit transaction, doesn't that mean SQL is unable to release any of that log space anyway? Similarly, isn't the table just as blocked as it would be in a single transaction for the same reason?
March 15, 2017 at 11:42 am
Yes, there s a single open tran for the full duration of the looping and deleting.
Since there is no where clause ... why would he not simply do a TRUNCATE TABLE.
March 15, 2017 at 11:55 am
DesNorton - Wednesday, March 15, 2017 11:42 AMYes, there s a single open tran for the full duration of the looping and deleting.
Since there is no where clause ... why would he not simply do a TRUNCATE TABLE.
I simplified the example. There is a where clause (and some other junk) as well in the real code.
March 15, 2017 at 12:46 pm
Xedni - Wednesday, March 15, 2017 11:35 AM...Am I missing something obscure that this accomplishes differently than just doing it all in a single transaction? I thought batching was largely there to reduce blocking and more importantly transaction log bloat. But if everything is taking place in an explicit transaction, doesn't that mean SQL is unable to release any of that log space anyway? Similarly, isn't the table just as blocked as it would be in a single transaction for the same reason?
This entire block of code IS a single transaction, if this was an attempt to break it down into smaller 5000 row chunks it's not really doing that right now. Is the database this is run in Simple or Full recovery model? If Simple recovery, then removing the explicit transaction can help transaction log bloat since each DELETE 5000 would be its own implicit transaction, but for full recovery it will need to put all the records in transaction log anyway so they would be there until transaction log backup.
It's somewhat difficult to guess what the blocking impact will be without knowing how many rows are expected to be deleted, how this table is indexed, if there are any foreign keys to it, and what the WHERE clause criteria is compared to how it's indexed. Each batch of 5000 rows may be on adjacent pages or scattered throughout the table. Again those locks will be held longer with the code as-is than if each delete 5000 rows was its own implicit transaction.
March 15, 2017 at 12:53 pm
Chris Harshman - Wednesday, March 15, 2017 12:46 PMXedni - Wednesday, March 15, 2017 11:35 AM...Am I missing something obscure that this accomplishes differently than just doing it all in a single transaction? I thought batching was largely there to reduce blocking and more importantly transaction log bloat. But if everything is taking place in an explicit transaction, doesn't that mean SQL is unable to release any of that log space anyway? Similarly, isn't the table just as blocked as it would be in a single transaction for the same reason?This entire block of code IS a single transaction, if this was an attempt to break it down into smaller 5000 row chunks it's not really doing that right now. Is the database this is run in Simple or Full recovery model? If Simple recovery, then removing the explicit transaction can help transaction log bloat since each DELETE 5000 would be its own implicit transaction, but for full recovery it will need to put all the records in transaction log anyway so they would be there until transaction log backup.
It's somewhat difficult to guess what the blocking impact will be without knowing how many rows are expected to be deleted, how this table is indexed, if there are any foreign keys to it, and what the WHERE clause criteria is compared to how it's indexed. Each batch of 5000 rows may be on adjacent pages or scattered throughout the table. Again those locks will be held longer with the code as-is than if each delete 5000 rows was its own implicit transaction.
It's running simple recovery.
March 16, 2017 at 5:39 am
Xedni - Wednesday, March 15, 2017 11:35 AMHi SSC,I ran across a block of code today which does deletes from a table in batches of 5000. Nothing crazy there. However the developer wrapped the entire set of batches in an explicit transaction, like so:
declare @rc int = 1begin tran
while @rc > 0
begin
delete top (5000)
from myTableselect @rc = @@rowcount
endcommit tran
Am I missing something obscure that this accomplishes differently than just doing it all in a single transaction? I thought batching was largely there to reduce blocking and more importantly transaction log bloat. But if everything is taking place in an explicit transaction, doesn't that mean SQL is unable to release any of that log space anyway? Similarly, isn't the table just as blocked as it would be in a single transaction for the same reason?
You're absolutely right and not missing anything.
The code with explicit transaction as it's written is equivalent to a single "big" DELETE of all data to be deleted (atonce, no loop), but the loop makes it slower.
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply