I am trying to delete thousands of records say 35K from a table which consists like 10 million records.
Delete * from Table where ScenarioID=859
I have also tried bulk deletion
DECLARE @RowsDeleted INTEGER
SET @RowsDeleted = 1
WHILE (@RowsDeleted > 0)
BEGIN
DELETE top(100) FROM tableWHERE ScenarioID= @ID
SET @RowsDeleted = @@ROWCOUNT
END
I would like to ask
- Generally How much time it would take to delete such no of records?
- If there is any Index on that table then is it gonna affect the delete statement?
Note: I have a non-clustered index on a column ID. Does this have any affect on performance of if i delete records based on that ID - are there any common bottle-necks that may cause delete to take long time than it should take ?
Is there any thing going wrong in this plan ?
PS: My database is hosted in Azure and it takes just below 10 seconds to execute the same command in Azure portal
Aucun commentaire:
Enregistrer un commentaire