Table Delete

Question

Post reply

Table Delete

Viewing 7 posts - 16 through 21 (of 21 total)

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

Bruin SSCrazy Points: 2071 More actions · Answer 1

Bruin

SSCrazy

Points: 2071

November 17, 2024 at 3:46 pm

#4484998

27Million

58,000 per day

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 2

Bruin wrote:

27Million
58,000 per day

Depending on the width of the rows and the number of indexes on the table, deleting a day's worth of rows should typically take no more than a few minutes. If you batch the deletion, ensuring each batch completes within 1 to 3 seconds, other processes can continue to operate with minimal impact.

Keep in mind that searching the table ([dbo].[Image_Classification_Master]) for the rows to delete will require a single full scan since the SpoolStartDt field isn't indexed. This may increase query time. However, you can minimise the impact on other processes by:

Adding WITH(NOLOCK) to avoid locking issues.

Using OPTION (MAXDOP 1) to ensure the query doesn't consume excessive CPU resources and impact online processes.

These adjustments may slightly increase the total runtime but will maintain the availability and performance of the system for other operations.

INSERT INTO #ToDelete (Id)
SELECT Id
  FROM dbo.Image_Classification_Master WITH(NOLOCK)
 WHERE CONVERT(DATE, SpoolStartDt) = '2022-02-22'
OPTION (MAXDOP 1);

Bruin SSCrazy Points: 2071 More actions · Answer 3

Currently deleting using by day

WHERE SpoolStartDt = '2022-09-30 00:00:00.000';

How could I delete a MONTH at a time..

Thanks.

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

Bruin wrote:

Currently deleting using by day
WHERE SpoolStartDt = '2022-09-30 00:00:00.000';
How could I delete a MONTH at a time..
Thanks.

Using YEAR and MONTH Functions:

INSERT INTO #ToDelete (Id)
SELECT Id
  FROM dbo.Image_Classification_Master WITH(NOLOCK)
 WHERE YEAR(SpoolStartDt) = 2022
   AND MONTH(SpoolStartDt) = 2
OPTION (MAXDOP 0)
;

Using a Date Range:

INSERT INTO #ToDelete (Id)
SELECT Id
  FROM dbo.Image_Classification_Master WITH(NOLOCK)
 WHERE SpoolStartDt >= '2022-02-01'
   AND SpoolStartDt < '2022-03-01'
OPTION (MAXDOP 0)
;

I recommend selecting the number of rows you want to delete in a single run. For example, if you need to delete rows older than 365 days, you could use the following query:

DECLARE @RowCountToDelete INT = 1000000    -- Specify the maximum number of rows to delete (TOP n)
DECLARE @DaysToKeep INT = 365  -- Specify the number of days to keep

DECLARE @TopN INT = 1000000    -- Specify the maximum number of rows to delete in one run
DECLARE @DaysToKeep INT = 365  -- Specify the number of days to retain

INSERT INTO #ToDelete (Id)
SELECT TOP (@RowCountToDelete) Id
  FROM dbo.Image_Classification_Master WITH(NOLOCK)
 WHERE SpoolStartDt < DATEADD(DAY, -@DaysToKeep, GETDATE())
 ORDER BY SpoolStartDt ASC -- Deletes the oldest rows first
OPTION (MAXDOP 0)
;

This query will delete up to 1,000,000 rows, starting with the oldest ones, and will leave untouched any rows that are less than 365 days old. You can adjust the values of @RowCountToDelete and @DaysToKeep to fit your requirements.

Bruin SSCrazy Points: 2071 More actions · Answer 5

Jonathan AC Roberts,

I was wondering If I could ask another question. I have a very similar cleanup to perform based off a CostKey found in 2 tables. I built a driver table that has 2 fields (ID,CostKey). The table I'm going to Delete from carries the CostKey and varies on the amount of data for each one. I would like the SP to loop thru similar to the other example you gave a throw messages as to which CostKey it's deleting from and of course the Batchsize as it deletes.

Thanks.

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 6

Hi Bruin,

Thanks for your question! Could you start a new thread and include the table definitions (DDL), details of any indexes, and explain exactly what you need the cleanup to do? That’ll make it easier to help.

Cheers,

Jonathan

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 7

Using YEAR and MONTH Functions:

I'm going to seriously recommend NOT doing it that way. Use the Date Range method, instead.

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