3

I need to delete 6.3 million records from 25 tables in production. I have the following two queries, but I don't know which one will be best:

delete jc 
from Jtable jc
join J1Table j 
on j.JobId = jc.JobId 
join nonJobReProcess n
on n.jobid = j.JobId

or:

while exists (select top 1 * from dbo.Jtable jc (nolock)
join J1Table j (nolock)
on j.JobId = jc.JobId 
join nonJobReProcess  n  (nolock)
on n.jobid = j.JobId)
begin 

delete  top (10000) jc 
from dbo.Jtable jc (nolock)
join J1Table j (nolock)
on j.JobId = jc.JobId 
join nonJobReProcess n  (nolock)
on n.jobid = j.JobId

end 

Please explain the reasons I might choose one over the other, or any alternative methods.

3

1 Answer 1

3

Using the second method will result in less locking, transaction log usage and Tempdb resources, because SQL Server doesn't have to perform all of the delete operations in one transaction.

However, instead of the "while exists" statement, you can use a variable that will get the value of @@rowcount after the delete. This can help a little more with performance.

1
  • 1
    To add to that, if I don't care about how long the delete takes to complete but care about impact to production I will add a delay of a second or 2 between each chunk. I normally do this on highly used tables where I want to make sure no other queries get to backed up. Feb 12, 2014 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.