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