efficient large table cleanup

  • 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
  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Thanks was trying to find it...

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

    Thx.

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Thx.

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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)
    Intro to Tally Tables and Functions

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

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