Deleting large amounts of rows in SQL server 15
0
votes
0
answers
59
views
I'm trying to execute a batch delete, but I'm having trouble executing it due to not properly using clustered index. Clustered index consists of three fields(int, int, datetime2).
The table consists of four columns(PK[integer, integer, datetime2], decimal)
What I have tried so far is doing TOP(X) delete which in the end slows down alot and deleting by days (one batch is one day) which is also not a good solution. Below I have attached the "by day" deletion script.
DECLARE
@ITERATION INT = 0
, @TOTALROWS INT = 0
, @MSG VARCHAR(500)
, @STARTTIME DATETIME
, @ENDTIME DATETIME
, @StartValue DATETIME = (SELECT MIN(DateTimeValue) FROM TableX)
, @EndValue date
, @MaxValue date = '2022-04-12';
SET NOCOUNT ON;
SET DEADLOCK_PRIORITY LOW;
WHILE @StartValue = @StartValue AND DateTimeValue > ' + CAST(DATEDIFF(millisecond, @STARTTIME,@ENDTIME) AS VARCHAR);
RAISERROR (@MSG, 0, 1) WITH NOWAIT;
SET @StartValue = @EndValue;
END;
Is there any way to use clustered index to my advantage here to speed up deletion?
Asked by elitra
(1 rep)
Jul 12, 2022, 03:13 PM
Last activity: Jul 12, 2022, 03:35 PM
Last activity: Jul 12, 2022, 03:35 PM