What limit should be placed on the number of rows to delete in a SQL statement?
We need to delete from 1 to several hundred thousand rows and need to apply some sort of best practise limit in order to not absolutely kill the SQL server or fill up the logs every time we empty a waste-basket.
This question is not specific to any type of database.
That's a very very broad question that basically boils down to "it depends". The factors that influence it include:
What is your level of concurrency? A delete statement places an exclusive lock on affected rows. Depending on the databse engine, deleted data distribution, etc., that could escalate to page or entire table. Can your data readers afford to be blocked for the duration of the delete?
How complex is the delete statement? How many other tables are you joining to, or are there complex WHERE clauses? Sometimes the identification of rows to delete can be more "expensive" than the delete itself, so one big delete may be "cheaper".
Are you fearful about deadlocks? As you decrease the size of your delete, your deadlock "foot print" is reduced. Ideally, single-row deletes will always succeed.
Do you care about throughput performance? As with any SQL statement, there is a generally constant amount of overhead (connection stuff, query parsing, returning results, etc.). From a single-connection point of view, a 1000-line delete will be faster than 1000 x 1-line deletes.
Don't forget about index maintenance overhead, fragmentation cleanup, or any triggers. They can also affect your system.
In general, though, I benchmark at 1000-lines per statement. Most systems I've worked with (sub-"enterprise") end up with a sweet-spot between 500 and 5000 records per delete. I like to do something like this:
set rowcount 500
select 1 -- Just to force @@rowcount > 0
while @@ROWCOUNT > 0
delete from [table]
[where ...]
Though limiting the number of rows affected by your delete using the set rowcount option and then performing a loop is very good (and I've used it many a time before), be aware that from SQL 2012 onwards this will not be an option (see BOL).
Therefore, another option may be to limit the number of rows being deleted using the TOP clause. i.e.
SELECT 1
WHILE @@ROWCOUNT > 0
BEGIN
DELETE TOP (#)
FROM mytable
[WHERE ...]
END
Unless you have a lot of triggers or integrity constraints to verify, deletion shouldn't be that expensive an operation.
But if you're that concerned about performance, my initial hunch would be to mark the appropriate rows as deleted and then physically delete them later during a periodic cleanup. But I'm not a big fan of this because you'll have to change any queries on that table to exclude logically- but not physically-deleted rows.
Whenever I see a database that routinely deletes large amounts of rows in bulk, it makes me think the data model or processing design is not optimal. Why load 1 million rows and then delete them? If you need to do something like purge historical data, then consider table partitioning.
a general answer is to drop the table and re-create it, that is a good performing solution, but applies for the full table
I run into this question and found my own answer to be quite effective: do a subselect.
delete from urls where url in ( select top 10000 url from urls)
