Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to delete about 100,000 records with minimal server overhead. I've had a few nagging questions I haven't been able to test properly so I figured I'd ask some experts here. Which would be better:

1-

BEGIN TRAN
DELETE FROM dbo.x
WHERE ID IN (
1
,2
,3
,4
...
,100000
)
COMMIT

2-

BEGIN TRAN
DELETE FROM dbo.x
WHERE ID = 1
GO
DELETE FROM dbo.x
WHERE ID = 2
GO
...
COMMIT 

3-

DELETE FROM dbo.x
WHERE ID = 1
GO
DELETE FROM dbo.x
WHERE ID = 2
GO
...

My assumption is that #1 would cause a scan based on sample %, pick out the items, remove them, and log it as a series of transactions. Perhaps it would base the info in the transaction log as to what pages were changed instead of each indvl. transaction, thus you can only rollback the entire activity and remark the pages, or you could commit. Is that correct?

On #2, I'm thinking does the GO statement cause a lot more transaction log activity by not allowing the SQL Server Storage Engine to roll all of these into 1 large transaction, but still provide some optimization for the transaction log with the BEGIN TRAN - COMMIT blocks, thus making it more effective than #3 but less effective than #1.

I then would assume #3 would be the worst one as each indvl. transaction is logged.

If anyone has any good blog posts or test scenarios they could point me to that would be great too. I've looked at ways to dig deep in the transaction log to figure it out myself but at this point, I thought I would ask you guys.

Thanks!

share|improve this question
1  
Have you considered adding the ID values you want deleted into a temp table and doing the delete via a join clause on the temp table? –  Max Vernon Nov 28 '12 at 22:52
    
Hi Max, I haven't! Thanks for the thought. I love how many ways there are to tackle a issue in MS SQL Server. –  Ali Razeghi Nov 28 '12 at 22:57

1 Answer 1

up vote 3 down vote accepted

From a logging point of view 1 and 2 will be about the same as in both cases you are doing all the deletes within a single transaction. There will be lots of locking and probably blocking while the delete is being run. #3 is a single transaction per batch, so users won't be impacted very much and you'll have lots of small transactions in the transaction log instead of one large one. #2 and #3 should take about the same amount of time to run. #1 should take less time than #2 and #3 because it's just one command, however the run time will probably still cause problems.

I'd probably want to do something like this. This will minimize the locking and blocking that needs to happen by only dealing with 1000 rows at a time.

SELECT NULL
WHILE @@ROWCOUNT <> 0
BEGIN
  DELETE TOP (1000) FROM dbo.x
  WHERE ID IN ( /* if possible use "BETWEEN 1 and 100000" instead*/
  1
  ,2
  ,3
  ,4
  ...
  ,100000
  )
END
share|improve this answer
    
Ah yes the WHILE loop! Thanks Denny, I forgot that's the best method for cases like this. Quick question though, would the total transaction log activity be almost the same in #1 and #2, but #3 would generate significantly more transaction log data due to the metadata needed per transaction? –  Ali Razeghi Nov 28 '12 at 22:56
    
no problem. :) Happy to help. –  mrdenny Nov 28 '12 at 23:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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