CODEWITHSUNDEEP
c#mysqllinq
Christoph Brückmann
Christoph Brückmann

Reputation: 1373

Performance - Delete multiple MSSQL database entries with LINQ

I have got a MSSQL database table with a few million entries. Every new entry got an ID +1 from the last entry. That means that lower ID numbers are older entries. Now I want to delete old entries in the database with the help of it's ID. I delete every "Entry" that is lower than the "maxID".

while (true)
{
    List<Entry> entries = entity.Entry.Where(z => z.id < maxID).Take(1000).ToList();

    foreach (var entry in entries)
    {
        entity.Entry.DeleteObject(entry);
    }

    if (entries < 1000)
    {
        break;
    }
}

I can't take all entries with one query because this would raise a System.OutOfMemoryException. So I only took 1000 entries and repeat the delete function until every entry is deleted.

My question is: What would be the best number of entries to ".Take()" in performance?

Upvotes: 2

Views: 238

Answers (2)

Suraj Singh
Suraj Singh

Reputation: 4069

It's faster to drop and recreate the tables in the database, You can directly execute commands against the database by calling your stored procedure using ExecuteStoreQuery method.

Any commands automatically generated by the Entity Framework may be more complex than similar commands written explicitly by a database developer. If you need explicit control over the commands executed against your data source, consider defining a mapping to a table-valued function or stored procedure. -MSDN

As i can see your code (Please correct me if i am wrong or improve the answer), Your code is actually loading entities in memory which is an overhead cost because you need to perform delete operation ,and your query will create delete query for each entity marked by DeleteObject. So in terms of performance it will be better to call a stored procedure and execute your query directly against the database.

ExecuteStoreCommand Method

Directly Execute commands

Upvotes: 1

Vishal Patel
Vishal Patel

Reputation: 973

Try this...

entity.Entry.Where(z => z.id < maxID).ToList().ForEach(entity.Entry.DeleteObject);
entity.SaveChanges();

Upvotes: 0

Related Questions