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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Purge a big table

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"
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -