0

My process is doing a insert into to a backup table 'B from a table 'A' which gets updated daily[truncate and load] in the azure sql db. A column 'TSP' [eg value =2022-12-19T22:06:01.950994] is present in both tables. TSP for all rows inserted in a day is same. Later in the day, I'm supposed to delete older data. Currently using 'delete from 'B' where TSP<'today-1day' logic Is there a way to optimize this delete using index or something?

SSMS suggested to create non clustered index on the table.TSP column. I tested it but seems there isn't much difference. If this was the data: 50mil TSP1 50mil TSP2 50mil TSP3 My expectation was it would skip scanning TSP2,TSP3 rows and delete TSP1. Whereas if table doesn't have index it would need to scan all 150mil rows.

Ashwin Mohan
  • 108
  • 9

1 Answers1

0

The batched delete operation utilizes a view to simplify the execution plan, and that can be achieved using Fast Ordered Delete Operation. This is achieved by refreshing the table once which in turn reduces the amount of I/O required.

Below are the sample queries: -

CREATE  TABLE tableA
(
id int,
TSP Datetime  DEFAULT  GETDATE(),
[Log] NVARCHAR(250)
)

WHILE @I <=1000 BEGIN  INSERT  INTO tableA VALUES(@I,  GETDATE()-1,  concat('Log message ', @I)  )  SET @I=@I+1 END

Option 1:- using CTE

;WITH DeleteData 
AS
(SELECT id, TSP,  Log FROM tableA 
WHERE  CAST(tsp AS  DATE)  =  CAST(GETDATE()  AS  DATE))
 
DELETE  FROM DeleteData

Option 2:- using SQL views

CREATE  VIEW VW_tableA AS (SELECT  *  FROM tableA WHERE  CAST(tsp AS  DATE)  =  CAST(GETDATE()-1  AS  DATE))

delete  from VW_tableA

Reference 1: An article by John Sansom on fast-sql-server-delete.

Reference 2: Similar SO thread.

Naveen Sharma
  • 349
  • 2
  • 4