删除大量记录需要很长时间 [英] Deleting a large number of records takes a VERY long time

问题描述

我有一个数据库表(在SQL Server 2012 Express上运行)，包含约60,000行.

I have a database table (running on SQL Server 2012 Express) that contains ~ 60,000 rows.

我正在使用以下代码清除旧行:

I am using the following code to purge old rows:

//Deleting CPU measurements older than (oldestAllowedTime)
var allCpuMeasurementsQuery = from curr in msdc.CpuMeasurements where 
    curr.Timestamp < oldestAllowedTime select curr;
foreach (var cpuMeasurement in allCpuMeasurementsQuery)
{
  msdc.CpuMeasurements.Remove(cpuMeasurement);
}

当删除的行数很大时(表中约90％或更多的记录将被删除)，该操作将花费非常长的时间.在相对强大的计算机(Intel I5台式机)上，大约需要30分钟才能完成此操作.

When the number of deleted rows is large (~90% or more of the records in the tables are being deleted) the operation takes exceptionally long. It takes about 30 minutes to finish this operation on an relatively strong machine (Intel I5 desktop).

1. 这似乎是正常现象吗?

1. does this seem like a normal behavior?

关于如何减少手术时间的任何想法?

any ideas about what I can do to reduce the operation's time?

谢谢

推荐答案

实体框架不能很好地处理这种批量操作.您应该使用 ExecuteStoreCommand 在这种情况下直接针对数据源执行SQL.

Entity framework is not very good at handling bulk operations like this. You should use ExecuteStoreCommand to execute SQL directly against the data source in situations like this.

var deleteOld = "DELETE FROM CpuMeasurements WHERE curr.Timestamp < {0}";
msdc.ExecuteStoreCommand(deleteOld, oldestAllowedTime);

这样做，您无需将实体加载到内存中(只需删除它们)就可以向数据库发出数千个删除命令.

By doing so you don't need to load the entities into memory (just to delete them) and issue thousands of delete commands to the database.

这篇关于删除大量记录需要很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！