Quick way to update many rows

Question

I'm developing a system that, when working, will update a given number of rows in DB with an new ID.

Currently I have ~9 million rows and after selecting a small subset of that data (~10k items) I then want to iterate through each of these items and update one column with a new ID.

What I have currently does work but is too slow to be usable so I need to improve it somehow. Here's the code, this was originally a ForEach loop but I read that 'For' loops on Arrays were quicker so I tried that without much improvement.

public string UpdateWithNewWidgetId(List<string> allTheWidgetIds, int newWidgetId)
    {
  var repoPerson = _repositoryFactory.CreateRepository<Person>();

        try
        {
            var person = repoPerson.GetAll(x => fieldToUpdate.Any(val => x.WidgetId == val)).ToArray;


            for (int i = 0; i < person.Length; i++)
            {
                person[i].WidgetId= newWidgetId;
                repoPerson.Update(person[i]);
            }

            repoPerson.SaveChanges();
        }
        catch (Exception ex)
        {
            //log error
        }

So, the repoPerson returns a list of about 10K items pretty quickly but the foreach loop takes ages and then the SaveChanges (this just does a Context.SaveChanges()) takes even longer.

Initially I had the SaveChanges within the foreach and thought that was my issue, but removing it from the loop hasn't improved things.

I'll investigate using a sproc, in the above example would you recommend doing the entire thing through the sproc or just the update? — Full Time Skeleton, Dec 15 '15 at 17:03

score 1 · Answer 1 · edited May 23 '17 at 12:15

1

For large data amount you need to look through this topic:

Bulk Update in C#

Generally if you need to perform a large amount of inserting/updating data, Entity Framework is not enough - need to use SqlBulkCopy approach which is much much faster.

edited May 23 '17 at 12:15

Community

1
1

answered Dec 15 '15 at 16:47

MagisterCrazy

225
1
10

SqlBulkCopy doesn't do UPDATEs – stuartd Dec 15 '15 at 16:51
In the topic I mentioned there is a good workaround using intermediate table. – MagisterCrazy Dec 15 '15 at 16:58
@MagisterCrazy just to clarify - in that linked topic, is it the selected answer you're recommending as a solution or a different answer further down? – Full Time Skeleton Dec 15 '15 at 17:01
Selected answer is good enough, I think ) We did the similar in our project before. – MagisterCrazy Dec 15 '15 at 21:49
Using a Staging table is the way to go. – stuartd Dec 15 '15 at 22:39

score -1 · Answer 2 · answered Dec 15 '15 at 16:54

Try to add transaction, should speed up context SaveChanges event

using (Context context = new Context())
        {
            using (var dbContextTransaction = context.Database.BeginTransaction())
            {
             ...yours code
             context.SaveChanges();
             dbContextTransaction.Commit();
            }
        }

Quick way to update many rows

2 Answers2