Deleting a large number of records takes a VERY long time

Question

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);
}

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. does this seem like a normal behavior?

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

Thanks,

Answer 1

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.