Sample Header Ad - 728x90

Instead of delete trigger performance

1 vote
0 answers
87 views
We have 3rd party tool which is deleting data via below parametrize query:
delete from [sap].[PRCD_ELEMENTS]
where [rowid] = @P1
Table contains around 3 billions of records and is partitioned by different nvarchar(10) column. We have non clustered index on rowid (rowid is not a part of PK/clustered index). We could have 200 000 deletes per minute which means above query will be executed in one sessions with different rowid in a loop. I am thinking to speed it up and create instead of delete trigger which will be collecting deleted records with partitioned column. Also create job which will be executed every 1 minute which will be deleting collected records via join with new table with rowid and partition column. Something like this:
delete prcd
from [sap].[PRCD_ELEMENTS] prcd inner join 
dbo.newTableWithDeleteTriggerValues d on prcd.rowid = d.rowid and prcd.partitioned_column = d.partitioned_column
The question is what is the performance of instead of delete trigger and will handle this kind of problem.
Asked by adam.g (465 rep)
Dec 21, 2023, 11:03 AM