Mass/Bulk SQL server updates

Question

Code

foreach (var item in _context.Duiven.
                .Include(p => p.Duivenmelker.VoedselInstelling)
                .Include(p => p.VoedselHistoriek)
                .Include(p => p.Duivenmelker.Personeel)
                .Include("Ziektes.DuifZiekte")
                .ToList() // 25.000)
            {
                item.FoodHistory();
                item.HealthSimulation();
                item.DiseaseSimulation(diseaseTypes);
                item.CureSimulation(_context);
                item.DeathSimulation();
                item.Feed();
}

_context.SaveChanges()

Problem

The above code loops through aprox. 25000 items. It updates all kinds of stuff on these objects (This process takes about 10 minutes) The problem lies in saving all these changes to the database, which takes almost 2 hours.

It updates (sql) each change one by one probably? (This is sql azure) The process also includes delete 25000 records & inserting 25000 new ones (4 ints)

I have reduced the calls to the database by including a lot that is needed. But I discovered that it's not the process but the saving that takes so long :S

Questions

Is there a difference in performce between [updating all values from a record] and [deleting current & inserting new]?

How could I improve saving this to the database? Like in 10 minutes instead of 160m ?

Write a server-based stored procedure that does these updates, and execute it from your application.... — marc_s, Apr 02 '13 at 17:24
I was hoping to avoid this (rewriting a lot of code to T-SQL) :) But at least I can stop trying to improve this. Thanks — user1834463, Apr 02 '13 at 17:31

score 3 · Accepted Answer · answered Apr 02 '13 at 17:23

3

Entity Framework is just not well suited to bulk updates.

I was in the same situation and ended up rewriting my bulk update logic to use ADO.Net with a stored procedure. Performance gain was around 10x for my particular use case.

Another option may be SqlBulkCopy depending on your exact situation.

answered Apr 02 '13 at 17:23

Eric J.

147,927
63
340
553

Depending on your situation I would definitely investigate SqlBulkCopy. It can be 100x plus faster. – Craig Apr 03 '13 at 00:38

score 0 · Answer 2 · answered Apr 02 '13 at 17:25

0

There are some tricks you can use like only commit every 100 operations or so.

But, I've always had to fallback to writing a stored procedure for database operations that preform too slowly from the ORM.

answered Apr 02 '13 at 17:25

Andrew Walters

4,763
6
35
49

1

Even with those tricks, EF is really not suited for bulk operations. – Eric J. Apr 02 '13 at 17:35

Mass/Bulk SQL server updates

Code

Problem

Questions

2 Answers2