Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tampasounds
Starting Member
6 Posts |
 Posted - 2007-05-14 : 01:27:27
|
Hi all I have table with about 67 million records
that are marked for deletion.
I know that I can
DELETE from table
WHERE ToBeDeleted='t'
But this may be too big a task for the server considering the amount
of data to delete at once. And if it runs out of resources and errors then nothing gets deleted....
Is there a way to segmant or loop so i can delete like 100k records at a time?
Many thanks in advance |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-05-14 : 01:35:28
|
See if this discussion helps: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77084[/url]
Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-05-14 : 02:20:20
|
[code]WHILE 1 = 1
BEGIN
DELETE TOP 100000
FROM Table1
WHERE ToBeDeleted = 't'
IF @@ROWCOUNT = 0
BREAK
END[/code]
Peter Larsson
Helsingborg, Sweden |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-14 : 02:50:19
|
Probably not a good idea if
ToBeDeleted = 't'
takes some resource-effort to resolve. Each iteration will spend more time finding the next 100000 than doing the deleting. Also needs attention to TLog backups, a WAITFOR if other users are accessing the DB, tuning of the TOP number (i.e. use of SET ROWCOUNT instead if SQL2000), most of which is covered in the thread Harsh pointed to.
Kristen
|
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-14 : 02:52:21
|
By the by, I presume that the 67 million rows to be delete is a SMALL proportion of the whole table?
If its MOST of the table it would be better to:
Copy rows-to-keep to new, temporary table
Drop original table
Rename temporary table to original table's name
Kristen |
|
|
|
tampasounds
Starting Member
6 Posts |
Posted - 2007-05-14 : 02:58:46
|
67 million rows is about 30-40 percent of tables weight
My understanding is that even if i tell it 1000 rows at a time it is
still going to read every row before deleting.
It takes about 4 hours to go through this table may it be reindex or defrag or whatever. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-14 : 03:47:59
|
"My understanding is that even if i tell it 1000 rows at a time it is
still going to read every row before deleting."
Not if the "thing" you delete on is based on a unique clustered index (which the PK will normally use).
In the thread Harsh pointed to you will have seen that I proposed using a temporary table to make a list of the PKs, and then delete in batches from that.
But does it matter how long it takes? If you come up with a low-impact method and a WAITFOR to allow other processing, if it takes days will it matter?
Kristen |
|
|
|
tampasounds
Starting Member
6 Posts |
Posted - 2007-05-15 : 00:15:42
|
| Moving the good records to a matching table and dropping the old did the trick in about 2 hours. Thanks again to everyone |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-05-15 : 01:18:32
|
i bet it would have been even faster if you'd have used bcp.exe for the export and import. or did it take that long with bcp?
www.elsasoft.org |
|
|
|
|
|
|
Purge a big table