Delete operation on a big table

regtha · #1 (**permalink**) 01-24-11, 18:10

Hi,

I have a table with 50 million records and have to delete 10 million records based on one condition. what is the best way to do this (without using much resources and also less time).

can we split those 10 million records and delete few at a time (say one million at a time).

Let me your ideas on this?

pdreyer · #2 (**permalink**) 01-25-11, 02:54

Code:

declare @rowcnt int, @batchsize int
select @batchsize=1000000 -- Delete in batches of this amount
  ,@rowcnt=@batchsize
set rowcount @batchsize
while @rowcnt=@batchsize
begin 
  delete records where thedate<'20100101'
  set @rowcnt=@@rowcount
end

regtha · #3 (**permalink**) 01-25-11, 08:54

Thanks for your reply.
Seems I confused. I would like to know what is the best way to delete.

agrawal.meet · #4 (**permalink**) 01-28-11, 07:29

Assuming original table is table_1

How about:
1. Inserting the rest 40 million into a different table (use select * into).
2. Truncate and than drop the old table (table_1)
3. rename the table to table_1 created at step1
4. run sp_recompile table_1

Log usage will be minimal (in-fact if you do it rite, there will be no log usage) and operation will be much faster.

atish_amd · #5 (**permalink**) 01-31-11, 01:47

But truncate table is non log operation. If you want to recover the data from transaction log then it will not be possible.It will stop your dump transaction,if any scheduled. And you have to make dump database.

Internet Services

Web Development

Digital Marketing

Consumer Tech