Pretty Big Delete Task

Need to delete 500K rows of a 6 million row table.

[woop01]

What's the best way to delete about half a million rows from a table? The only criteria for deletion is they are just old. I.e. the where section is "where date < dateadd(month,-2,getdate())".

Right now when I do a "delete from table where date < dateadd(month,-4,getdate())" the query cost is in the thousands.

Thanks,

[Easy_Coder]

Is there a sequential id associated with the record?

[woop01]

Yes, there is.

[duckhunter]

Use a Cursor. This will prevent locking large blocks of the table. SQL2K should only row level lock during each cycle of the cursor.

DECLARE @orderid int

DECLARE mycursor CURSOR FOR
SELECT orderid
FROM orders
ORDER BY orderid

OPEN mycursor

-- Get the first row.
FETCH NEXT FROM mycursor
INTO @orderid

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Deleting OrderID: ' + convert(varchar(15),@orderid)
-- INSERT YOUR DELETE STATEMENT HERE
-- Get the next row.
FETCH NEXT FROM mycursor
INTO @orderid
END

CLOSE mycursor
DEALLOCATE mycursor

[woop01]

Thanks a million, that's working pretty well so far.

[sun818]

Drop any indexes associated with the table before deleting the rows. You can rebuild them after deleting the rows. I know in Oracle, you can also disable the rollback segment for better performance. Is there something similar in SQL 2k's transaction logs?

[duckhunter]

All you need to do is Recompute Statistics after the delete process is complete and indexes will get refreshed.

[woop01]

"Drop any indexes associated with the table before deleting the rows."

I would but that essentially takes the site down for about two hours when they are rebuilt. We do it once every two weeks but this needs to be done more often than that. So far, it's working pretty well.

[sun818]

My point in suggesting dropping the indexes is to reduce the number writes the database performs. Each time the row is deleted, the index is updated. Alternatively, you could create a new table with only the remaining data you want. Drop the old table and rename the new table to the old one. (Deleting half a million rows sounds like a task you would do after-hours anyway...)

[woop01]

Thanks for the advice. If it isn't evident, I sort of got stuck as a DBA by default.

You have to love wearing twenty different job titles as a small business owner.