views:

108

answers:

4
+4  Q: 

SQL Mass deletes

+2  A: 

TRUNCATE and CREATE AS SELECT are much faster than deleting the whole table since they generate less redo logging.

They are, however, DDL commands and cannot be rolled back in some systems.

However, DELETE may still be faster if there are few rows to delete.

If half of rows are to be deleted, then most probably the first option will be faster, at least in Oracle.

Quassnoi
+1  A: 

Your option of moving out the data you want to keep is very valid, but works only if the amount being kept is small compared to the data being removed.

To perform the delete more efficiently you may wish to batch a delete operation that only deletes the top 1000 rows (do not use an order clause!) so that the size of each delete transaction is kept small and does not hold locks / use much temp etc.

Shrinking the database after you remove all the data will fragment the index up btw, if you have to shrink a filegroup, move the data to another filegroup and remove the original if you can, any shrink will fragment you, causing you to reindex and use more space again (and you can rinse repeat it to no avail)

Andrew
A: 

Use truncate when you can, because its way faster. However this won't work when there are foreign keys referencing the table.

When doing those type of delete statements on a big table it can be faster to just select the last N days into a temp table, drop the table, recreate and insert. However this can get tricky when you have foreign keys.

I've had problems maxing out disk space on transactions logs, so I've sometimes used while loops that just keep deleting N rows at a time.

Should also make sure your databases are using Simple Transactions Logs, you may also want to set Auto Shrink.

Ryu
A: 

I think you're bang on with the idea to extract the data you want to keep into a temp table, truncate the original table, and then put it back.

You could also create a new table with the extracted data, drop and rename the new table to the original tables name. This seems more error prone, but if you're doing this kind of work during a maintenance period, then maybe it's OK.

Ryan Montgomery