Delete large number of rows help

Question

Post reply

Delete large number of rows help

SQL_Surfer

SSCrazy Eights

Points: 8320
More actions
October 22, 2023 at 9:49 pm

#4313001

I have a batch process where i have to delete around 75 MIL rows out of 1.3 Billion rows table. And then insert the new rows back. I have been told that there is no way to do incremental load on these. I believe the DELETE operation is taking very long (2-3 hours). Database is in Simple Recovery model. Any ideas how to speed up this delete? I would sincerely appreciate it.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Are the rows all at the "end" of the Clustered Index or are they scattered throughout?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL_Surfer SSCrazy Eights Points: 8320 More actions · Answer 2

SQL_Surfer

SSCrazy Eights

Points: 8320

October 23, 2023 at 1:56 pm

#4313396

Scattered throughout.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

That's about 5% of the table, so it's unlikely that an index will help tons. However, have you looked at the execution plan for the DELETE statement? How is it satisfying the query?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

Have you got a clustered index on the table? You can do a single table scan and insert the values of the clustered index key that need to be deleted into a temporary table then use the temporary table to join to the table and delete rows in multiple batches.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

DELETE in batches rather than all at once, as long as each DELETE won't require (up to) a full scan of the table to find the rows to delete.

If you need to delete all rows at once, for whatever reason, be sure to pre-grow the log file to handle the DELETE. Allowing the log file to grow dynamically during the DELETE instead will drastically slow down the DELETE.

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

Stewart "Arturius" Campbell SSC Guru Points: 72625 More actions · Answer 6

Has the table in question been partitioned (or the clustered index)?

If so, then swap out each partition to it's staging table, run the deletes/inserts, then swap back in.

If not, learn about it here:

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”