Deleting records

I am trying to find the most efficient way to delete a sizable chunk of
records from a table. I have 20MM records that I need from a table that has
over 1 billion records in it. What is the best way to do this? I thought
about doing a select into, ignoring the unwanted records but space may be a
factor. Would an index on the key be beneficial? This ran for 14 hours
before I killed the process.
Thanks,
Jeff> factor. Would an index on the key be beneficial? This ran for 14 hours
> before I killed the process.
It most likely will. First the rows have to be located before they can be
deleted. One thing that you might be able to do is chunk the delete. Just
put something like this in a loop:
set rowcount 1000 --you balance this to what turns out to be fastest
delete from table
where condition
set rowcount 0 --reset the rowcount
In another connection you might be able to monitor how many rows remain by:
select count(*)
from table (nolock) --so you can see how many rows remain.
where condition
That may not work depending on how slow the query is.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Jeff" <jeffrey.southworth@.gmail.com> wrote in message
news:e1Wy3%23brFHA.2880@.TK2MSFTNGP12.phx.gbl...
>I am trying to find the most efficient way to delete a sizable chunk of
> records from a table. I have 20MM records that I need from a table that
> has
> over 1 billion records in it. What is the best way to do this? I thought
> about doing a select into, ignoring the unwanted records but space may be
> a
> factor. Would an index on the key be beneficial? This ran for 14 hours
> before I killed the process.
> Thanks,
> Jeff
>