SQL DELETE-最大行数 [英] SQL DELETE - Maximum number of rows

问题描述

在SQL语句中应删除的行数应设置什么限制?

What limit should be placed on the number of rows to delete in a SQL statement?

我们需要删除1到数十万行，并且需要应用某种最佳实践限制，以便在每次清空废纸bas时不会绝对终止SQL Server或填充日志.

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:

• 您的并发级别是多少? delete语句在受影响的行上设置排他锁.根据数据库引擎，删除的数据分布等，这些信息可能会升级到页面或整个表.可以在删除期间阻止您的数据读取器吗?

• 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?

delete语句有多复杂?您要加入多少个其他表，或者有复杂的WHERE子句?有时，要删除的行的标识可能比删除本身更昂贵"，所以一个大的删除可能是便宜".

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.

您是否关注吞吐量性能?与任何SQL语句一样，通常开销是恒定的(连接内容，查询解析，返回结果等).从单连接的角度来看，删除1000行比删除1000行1行要快.

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.

不过，总的来说，我以每条语句1000行作为基准.我使用过的大多数系统(企业")最终每次删除都会有500到5000条记录.我喜欢做这样的事情:

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 ...]

这篇关于SQL DELETE-最大行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！