Unanswered: Fastest way to delete from a large table
I have a problem with deleting from a very large table. The table is a log of some sort with a combined clustering index on the columns type and time. I want to delete all files older than some date. It seems to me that there are two ways to do this:
1. Create a loop that deletes all rows for each type, using the index already on the table.
2. Create a new index for time and delete all in one command.
My question is which is faster? And of course if you know of any other magical way of doing this.
I made some tests with the method Peso suggested and my method with creating a cursor to delete each type separate. The cursor way was much faster. I tested it on a table with 6,152,721 rows. With cursor it took 1:04 and with the other 3:39.
I'll post the code so you can comment:
First with cursor
declare CustList cursor for
SELECT type from TypeTable
FETCH NEXT FROM CustList
WHILE @@FETCH_STATUS = 0
delete from logTable where type= @type AND time <'11-04-2008'
FETCH NEXT FROM CustList INTO @type
Next without cursor
WHILE @@ROWCOUNT > 0
DELETE TOP (1000)
SELECT ROW_NUMBER() OVER (PARTITION BY type ORDER BY time) AS recID
WHERE time <'11-04-2008'
) AS f
Yep - I would expect that. If you look at the plans, #1 will seek on the index, #2 will scan. In effect you are batching the deletes in #1 anyway so you get the double whammy of seeking on the index and using Peso's batches.
The above assumes that type is the leading column in the index.
Actually Peter - I've reread your query - what is the point of the ROW_NUMBER()? You don't use it...