efficient large table cleanup

Question

Post reply

efficient large table cleanup

Bruin

SSCrazy

Points: 2058

November 28, 2021 at 2:26 pm

Go to Answer

#3957586

looking for examples on doing a large table cleanup where my attribute value selected has an attribute_value of Null. I would like to

maybe do it in Batches where I deleted say 20,000 in 5 iterations then exit.

Attribute = 'csx' and attribute_value is null

Thanks.

CREATE TABLE [dbo].[Quality_Attribute](
[Quality_ID] [int] NOT NULL,
[Attribute] [nvarchar](100) NOT NULL,
[Attribute_Value] [nvarchar](100) NULL,
[Attribute_DateTime] [datetime] NULL,
 CONSTRAINT [PK_Quality_Attribute] PRIMARY KEY CLUSTERED 
(
[Quality_ID] ASC,
[Attribute] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Jeffrey Williams

SSC Guru

Points: 90303
More actions
November 28, 2021 at 3:25 pm
Answer
#3957592

Is this a repeat of this question: https://www.sqlservercentral.com/forums/topic/large-table-cleanup-using-delete
Why start up a new conversation for something where you accepted an answer?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs

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

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

Bruin SSCrazy Points: 2058 More actions · Answer 1

Thanks was trying to find it...

Was there a way to log the numbers of deletes as I go.

Thx.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

If you need to find previous posts - go to your profile and select 'Topics Started'. Review the previous thread - @JeffModen provided a similar template with additional options for reporting progress.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Bruin SSCrazy Points: 2058 More actions · Answer 3

Bruin

SSCrazy

Points: 2058

November 28, 2021 at 5:04 pm

#3957604

Thx.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 4

As a bit of a sidebar, one does have to wonder what the word "efficient" in the post title actually means because, IMHO, you selected to go with the least "efficient" method in the other thread.

--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)

Bruin SSCrazy Points: 2058 More actions · Answer 5

Are you suggesting

Depending upon how much data you are saving, it may be faster/easier to select what you want to keep into a new table. Drop the PK, FK and indexes on the original table, rename the original table, and rename the new table to the old table's name, and then re-apply the constraints

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 6

That's almost exactly what I suggested in the other post. You ARE trying to delete 41% of the table. After doing that (deletes), you're going to have to rebuild all the indexes, anyway.

And, no. I wouldn't drop the clustered index even if it's a named constraint being used for a Primary Key. Dropping it will cause it to "rebuild" the old table as a "heap" and that can take a lot of time. Constraints can be renamed just as easily as a table and, the cool part is, it'll rename the underlying objects. For example, if you change the name of a PK constraint, it'll also change the name of the underlying index.

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-rename-transact-sql?view=sql-server-ver15

And, DELETEs are always fully logged. You could do almost all of this in a very high speed, minimally logged fashion if you can make a trip to the BULK LOGGED Recovery Model.

--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)