Performance - Delete multiple MSSQL database entries with LINQ

Question

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?

Suraj Singh · Accepted Answer · 2014-01-09 16:27:29Z

1

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

edited Jan 9, 2014 at 16:27

answered Jan 9, 2014 at 15:56

Suraj Singh

4,0871 gold badge23 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christoph Brückmann Over a year ago

I do not want to drop the table. I only want to delete entries with lower ids.

Vishal Patel · Accepted Answer · 2014-01-09 15:34:15Z

0

Try this...

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

edited Jan 9, 2014 at 15:34

answered Jan 9, 2014 at 15:17

Vishal Patel

9735 silver badges12 bronze badges

1 Comment

Christoph Brückmann Over a year ago

It does not work. This also raises the System.OutOfMemoryException while trying to SaveChanges() on entity.

Collectives™ on Stack Overflow

Performance - Delete multiple MSSQL database entries with LINQ

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related