CODEWITHSUNDEEP
c#databaselinqoptimizationsql-server-2012-express
OSH
OSH

Reputation: 2937

Deleting a large number of records takes a VERY long time

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,

Upvotes: 14

Views: 5133

Answers (4)

Joe Ratzer
Joe Ratzer

Reputation: 18569

Deleting huge amounts of data can take a long time.

You might have to move the sql out of your application and run it as a single sql script via SQL Server Agent. It could be run, for example, once a day during the quietest period.

Upvotes: -2

Magnus
Magnus

Reputation: 46967

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.

Upvotes: 20

Kenneth
Kenneth

Reputation: 28737

The reason for this is that you execute a DB update for every single record. You need to do a bulk update.

EntityFramework.extended can handle this scenario.

Upvotes: 5

Mathew Thompson
Mathew Thompson

Reputation: 56429

You should look at EntityFramework.Extended it was created to help with both bulk deletions and updates.

Using it, you could simply do:

msdc.CpuMeasurements.Delete(curr => curr.Timestamp < oldestAllowedTime);

Upvotes: 14

Related Questions