Bulk Delete

we are trying to delete data from a huge 75 million records table
it takes 4hr to prune data

delete from Company where recordid in (select top 10000 recordid from
recordid_Fed3 where flag = 0)

we have a loop that prunes 10000 records at a time in a while loop
let me know if there is a better way to acheive thisIf you just want to delete *every* row in the table you can use
TRUNCATE TABLE :

<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_2hk5.asp
If you want to selectively delete rows, that won't work, though. If
you want to delete most of the rows (but not all of them) you could
insert the ones you want to keep into some other (temporary) table,
truncate the main table, then insert the rows back.|||kumar (svengala@.gmail.com) writes:
> we are trying to delete data from a huge 75 million records table
> it takes 4hr to prune data
> delete from Company where recordid in (select top 10000 recordid from
> recordid_Fed3 where flag = 0)
> we have a loop that prunes 10000 records at a time in a while loop
> let me know if there is a better way to acheive this

Rather than using SELECT TOP, try use a condition that matches the clustered
index and slice that up in intervals. Assume that the clustered index is
on recordid, and that this is an integer you would do:

SELECT @.recordid = MIN(recordid) FROM Company (WHERE flag = 0),
@.increment = 100000
WHILE EXISTS (SELECT * FROM Company WHERE recordid = @.recordid)
BEGIN
DELETE Company
WHERE recordid BETWEEN @.recordid AND @.recordid + @.increment - 1
AND flag = 0
SELECT @.recordid = @.recordid + @.increment
END

In this way you are only scanning the table once for rows to delete.

If you anticipate that you will delete more rows than you will retain,
you could create a new table, and insert the rows to keep. In this case
you need to make sure that you also bring with you constraints, indexes,
and triggers, and you will have to move referencing foreign keys. The
insert can be further speedied up by using SELECT INTO, but SELECT INTO
may not give you a faithful copy of the table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp