I need to delete 400,000 records

Question

I need to delete 400,000 records

interflex

SSC Enthusiast

Points: 171
More actions
June 16, 2009 at 10:19 pm

#133235

We have a SQL 2005 database and there are some old records that need to be deleted with the matterid id of 2098-75. The support rep for the company started to run the script below but then realized it could take around 10 hours to complete. Questions for the sql experts:
1) If the script below is run does it delete records immediately, or is there some type of temporary table that is created and are the records deleted from there.
2) I've heard of something called the 'commit' word, can it help.
3) Is there a better statement that can delete this many records.
4) How could I delete only 1000 of the 400,000 that need to be deleted so I can calculate how long it will take to do 400,000.
delete from transactions
where matters in (select matters from matters where matterid = '2098-75')
and stage = 'wip'
Thanks,
Dennis

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29423 More actions · Answer 1

interflex (6/16/2009)

delete from transactions
where matters in (select top 1000 matters from matters where matterid = '2098-75')
and stage = 'wip'

This will delete only 1000 records

Also to delete the records, i'd suggets u do this

1. move all data except matter id='2098-75' to a new table. This will be very fast.

Insert * into New_table from matters where matterid'2098-75'

2. rename old table (drop it later if u see everything worked well)

3. rename new table with original name

4. create constraints/triggers/indexes(if any) on the new table.

Pradeep Singh

ps. One Orange Chip Points: 29423 More actions · Answer 2

Forgot to mention one imp point.

Keep the database recovery in bulk-logged mode before running that query. This will speed-up the query to a very large extent.

Once query is finished, put the db back in FULL recovery mode.

Pradeep Singh

interflex SSC Enthusiast Points: 171 More actions · Answer 3

I should have placed the post under newbie :hehe:

I'm not sure how I would recreate the constraints, triggers, or indexes on the new table.

Dennis

ps. One Orange Chip Points: 29423 More actions · Answer 4

http://www.mssqltips.com/tip.asp?tip=1294 shows many ways how you can find out dependencies (eg. there might be an index that is dependent on your base table).

Once you know what all objects(index, constraints etc) are dependent, you can recreate those on the new table.

Pradeep Singh

steve block SSCertifiable Points: 7698 More actions · Answer 5

What are the total number of records? If the total number is not much more than the number you want to delete, then copying only the records you wish to keep to a new table should work faster.

Deleting directly from a table, the sub-select will slow you down. Use a join instead.

DELETE transactions

FROM transactions t INNER JOIN matters m ON t. = m.

WHERE m.matterid = '2098-75' AND t.stage = 'wip'

Steve