Posted by Erland Sommarskog on 09/28/59 11:31

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/techinfo/productdoc/2000/books.asp

[Back to original message]