SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Purge a big table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tampasounds
Starting Member

6 Posts

Posted - 05/14/2007 :  01:27:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 05/14/2007 :  01:35:28  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
See if this discussion helps: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77084

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 05/14/2007 :  02:20:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
WHILE 1 = 1
    BEGIN
        DELETE  TOP 100000
        FROM    Table1
        WHERE   ToBeDeleted = 't'

        IF @@ROWCOUNT = 0
            BREAK
  END


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/14/2007 :  02:50:19  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 05/14/2007 :  02:52:21  Show Profile  Reply with Quote
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 - 05/14/2007 :  02:58:46  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 05/14/2007 :  03:47:59  Show Profile  Reply with Quote
"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 - 05/15/2007 :  00:15:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 05/15/2007 :  01:18:32  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000