Bulk Copy Update C# ASP.NET MVC

Question

I have 2 tables both with relatively large amounts of information. As part of the process I need to update Table 2(Inv) with the information from Table 1. Essentially copy the CC value from Table 1(Acc) to PCC value in Table 2.

The amount of updates vary from 4000 to 40000 rows and the page hangs for quite a while. I cannot run this in the background because another process has to run directly after it which needs this update to happen first and output to user.

How can I speed things up?

Current code:

            var invList = db.Inv.Where(m => m.ID == GI.Id).ToList();
            var invListAcc = invList .Select(m => m.AccNum).ToList();
            var accExtract = db.Acc.Where(m => invListAcc .Contains(m.AccNum)).ToList();
            foreach(var inv in invList)
            {
                var invDB = db.Inv.Find(inv.Id);
                var accCC = accExtract.Where(m => m.AccNum== invDB.AccNum).Select(p=>p.CC).FirstOrDefault();
                if(accCC != null)
                {
                    invDB.PCC = accCC;
                    db.Entry(invDB).State = EntityState.Modified;
                    db.SaveChanges();

                } 
            }

Table : Acc

+------------+---------+
|    AccNum  |   CC    |
+------------+---------+
|    123     |   34    |
|    124     |   24    |
+------------+---------+

Table : Inv

+-------+------------+---------+
|  Id   |    AccNum  |   PCC   |
+-------+------------+---------+
|   1   |    123     |         |
|   2   |    124     |         |
+-------+------------+---------+

if you are on EF Core this may be of use: https://github.com/borisdj/EFCore.BulkExtensions — jazb, Oct 19 '18 at 06:16
this is => https://entityframework-extensions.net/bulk-update — er-sho, Oct 19 '18 at 06:16
@JohnB, its free nuget package => https://www.nuget.org/packages/Z.EntityFramework.Extensions/ — er-sho, Oct 19 '18 at 06:25
@ershoaib =>$$$ your 1st link was not - your new link looks free however — jazb, Oct 19 '18 at 06:30
How would I update without looping the the values in direct sql? — user2806570, Oct 19 '18 at 06:31
@JohnB, both are same if you go to link `Get Started` in my 1st link you will see the second link option with blue color font `Nuget` — er-sho, Oct 19 '18 at 06:32
Save in batches instead of saving for every object. I would suggest batches of 1500. — Travis J, Oct 19 '18 at 06:44
move the db.SaveChanges(); out side of the foreach look... yes it will all be done.. your using the framework incorrectly — Seabizkit, Oct 19 '18 at 06:56
@JohnB, i think you'd see wrong in link there, anyways i used it from long time and its free — er-sho, Oct 19 '18 at 06:58
If this needs to run first, and then the Output something to an user. Try using a Task with a [Continuation](https://learn.microsoft.com/en-us/dotnet/standard/parallel-programming/chaining-tasks-by-using-continuation-tasks). — Christoph K, Oct 19 '18 at 07:23

score 3 · Answer 1 · answered Oct 19 '18 at 06:42

3

You can do it quickly using pure SQL

update A
set A.ACC =  B.CC
from Inv A inner join Acc B
on B.AccNum = A.AccNum

answered Oct 19 '18 at 06:42

Hüseyin Burak Karadag

1,848
17
23

Seabizkit · Answer 2 · 2018-10-19T11:00:52.053

move the db.SaveChanges(); out side of the foreach look... yes it will all be done.. your using the framework incorrectly

 var invList = db.Inv.Where(m => m.ID == GI.Id).ToList();
 var invListAcc = invList .Select(m => m.AccNum).ToList();
 var accExtract = db.Acc.Where(m => invListAcc .Contains(m.AccNum)).ToList();
 foreach(var inv in invList)
 {
      var invDB = db.Inv.Find(inv.Id);
      var accCC = accExtract.Where(m => m.AccNum== invDB.AccNum).Select(p=>p.CC).FirstOrDefault();
      if(accCC != null)
      {
           invDB.PCC = accCC;
           db.Entry(invDB).State = EntityState.Modified;

      } 
 }
 db.SaveChanges();

also see if you can just fetch what you are working with instead of calling var invDB = db.Inv.Find(inv.Id); on each loop... this is a db call to the db for each loop which is expensive

use the other answer here to fetch all the records to loop... then update then SaveChanges.

UPDATE

update the PCC of all Invoice with the CC of an Account where the invoice is linked to the account.
your solution depends on how you have modeled your entities
i wouldn't recommend you keep this running in the system, as it should be a once of requirement
- but if you modeled them correctly you could do something like;

using include

var invList = db.Inv.include(x=>x.Acc).tolist(); -- required EF extension for include, this is 1 db call
foreach(var item in invList)
{
    var account = item.Accl
    if (account != null )
    {
        item.PCC = account.CC  
    }   
}
db.SaveChanges();

Tried this and there has not been any change in performance. Still takes the same amount of time. — user2806570, Oct 19 '18 at 07:10
with your current solution its would needs to do 4K to 40K actual DB selects to the db.... so I'm not surprised its taking very long... but that is nothing compared to how long it would take to do 4K to 40K SaveChanges — Seabizkit, Oct 19 '18 at 10:58

Bulk Copy Update C# ASP.NET MVC

2 Answers2