ansaurus

Question

optimizing the DELETE statement with a count

Answer 1

+2 A:

Since both queries perform the exact same task, I'd use the first one, because it's simpler to read and understand.

(Also, since both queries do the same work, I suspect that they'd generate the same execution plan--can you check this?)

Philip Kelley 2010-09-28 17:05:01

Answer 2

+6 A:

Both and neither. You need to delete in batches when dealing with a large number, because of single transaction log growth issues. Assuming you want to delete all records for a given @productId:

declare @batchSize int = 10000;
do while(1=1)
begin
   delete top(@batchSize) from ProductInfo where productId = @productId;
   if (0 = @@rowcount)
      break;
end

The two forms of DELETE you posted are basically identical, the important thing is the table is organized by a clustered key based on productId key. If this is not true and you have a NC index on productId then the @batchSize has to be calibrated as to avoid the index tipping point.

Remus Rusanu 2010-09-28 17:05:26

1. What happens if the delete statement fails? Does it continue in the while loop?2. What happens if the delete fails, does it set the rowcount = 0 and falsly exit the loop?

RPS 2010-09-28 17:17:13

Adding error handling to every single example post would bloat the sample and dilute the value of the example. But if you explicitly ask for error handling, then I would recommend wrapping this in a BEGIN TRY/BEGIN CATCH block. You then deal with the error appropriately, based on what was the error.

Remus Rusanu 2010-09-28 17:31:11

A @batchSize that large will lead to Lock Escalation unless it has specifically been disabled through various methods.

etliens 2010-09-28 17:58:30

ansaurus

tags:

views:

answers:

optimizing the DELETE statement with a count

related questions