February 8, 2022 at 10:41 pm
Hello SSC,
I hope everyone is well and staying safe.
Basically I have to update 83 million rows in a single SQL table. I am looping this UPDATE in 1 million row batches. I have heard and read different things about using COMMIT at the end of statements. When I Google this, it is suggested that I just roll this into a Transaction, but transactions are slow, right?
So my question is... If I simply add COMMIT after 1 million row batches, would that help performance? I hope I explained myself correctly, it's been a long week.
As always thank you in advance for taking the time to answer my post. This is a great site!
--Dave
The are no problems, only solutions. --John Lennon
February 8, 2022 at 10:52 pm
No, adding a COMMIT will not speed it up, the associated explicit BEGIN TRAN will likely slow it down slightly. SQL will (auto)commit after the statement finishes anyway.
Be sure you have pre-allocated enough log space to handle all the DELETEs.
Hopefully you're able to put the db into SIMPLE mode when doing this.
Doing CHECKPOINTs at assorted intervals helps SQL to truncate the log so it doesn't grow too much.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply