I wants to delete some records from Production DB, the total number of rows could be 2000 rows or 200 thousand rows. I want to make sure no Dead Lock happen or I don't want any Problem in Production DB
Option 1:-
Delete from TableName
Where id in (select id from #temp1)
-- The total could be 20 Thousands records to 200 Thousand
Option 2:-
With CTE_Delt As (TableName.Field1, TableName.Field2, TableName.Field3,.........)
(
Select * from TableName
where id in (Select ID from #temp1)
)
Delete from CTE_Delt
My Question which option is safe? Correct me If I am wrong Option 2 will delete rows one by one, am i right?
Thank You for your reply sqlfresher2k15.
quick question, if I am deleting a big amount of rows (No one knows, could be 100 thousand to 1m), first Delete will not affect Prod DB (Dead Lock or table lock) happen? I will Google what is WITH(READPAST). However, If I use Option 2, it will safe and good or deleting one by one? am i right?
When we delete a large number of records we use a loop, delete a "modest number of rows" on each loop iteration, use a WAITFOR to delay for, say, 2 seconds (to allow other processes to run) and run a Transaction Log Backup every 2 minutes.
Of course this cannot be done within a transaction ... so consideration is needed to data integrity whilst the job is running.
We always insert the Clustered Index Keys into a #TEMP table first, sorted in Clustered Index order, with an additional IDENTITY column, and then delete in blocks based on the IDENTITY column, joining to the actual table to be deleted. (reason for this is because we are using a loop it avoids the time for the WHERE clause (and any JOINs) to re-find the records for deletion, on every iteration of the loop, to then only delete part of them [using TOP or SET ROWCOUNT] )
We calculate the elapsed time of the loop iteration and adjust the size of the batch accordingly - e.g. if the server becomes busy and loop iteration time exceeds @MaxLimit we reduce the batch size (by half), if the iteration elapsed time is less than @MinLimit then we add 20% to the batch size.