Table Delete

Question

Post reply

Table Delete

Viewing 4 posts - 16 through 18 (of 18 total)

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

Bruin SSCrazy Points: 2058 More actions · Answer 1

Bruin

SSCrazy

Points: 2058

November 17, 2024 at 3:46 pm

#4484998

27Million

58,000 per day

Jonathan AC Roberts SSCoach Points: 18354 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: 2058 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: 18354 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.