How can (DbContext) SaveChanges() be used for large number of records? - the solution for the best performance is to be sought and found

Question

It can be running SaveChanges() for large number of records, but the performance is not acceptible.

Here is an example Two tables:

[contact] (id, name, zip, city, status)

[zip_city] (id, zip, city)

Needs to update the status in table contact for all column zip, city being availabel in table zip_city

It works and the performance is acceptable when the number of records is less than 10,000.

But for +10,000 records, Visual Studio (Debugger) complains that it takes too long: The CLR was unable to transition from COM context 0xb67898 to COM context 0xb67728 for 60 seconds.... at building the query "qry"

var ctx = new DbContext();

var qry = ctx.contact.Join(ctx.zip_city, c => new { c.zip, c.city }, z => new { z.zip, z.city }, (c, z) => new { c, z })
           .Select(x => new dtoContact { id = x.c.id }).ToList();

foreach (var con in ctx.contact)
{
    if (qry.Any(x => x.Id == con.id))
    {
        con.status = "P/O";
    }
    else
    {
        con.status = "???";
    }
}

ctx.SaveChanges();

With following code it raises same message by running ctx.SaveChanges();

var ctx = new DbContext();

var zc = ctx.zip_city.ToList();

foreach (var con in ctx.contact)
{
    if (zc.Any(x => x.zip == con.zip && x.city == con.city))
    {
        con.status = "P/O";
    }
    else
    {
        con.status = "???";
    }
}

ctx.SaveChanges();

Reference: Data Transfer Object

public class dtoContact 
{
    public int id { get; set; }
    public string name { get; set; }
    public string zip { get; set; }
    public string city { get; set; }
    public string status { get; set; }
}

Info: Visual Studio (Debugger) message! google translation from VS15 German version

The Managed Debugging Assistant "" ContextSwitchDeadlock "" encountered a problem in "C: \ Projects \ Sweepstakes_EF6 \ TrafficSightAct \ bin \ Debug \ Sweepstakes.exe". Additional Information: The CLR was unable to transition from COM context 0xb67898 to COM context 0xb67728 for 60 seconds. The thread that owns the target context / apartment either waits without moving messages or processes a very long-lasting operation without moving Windows messages. Such a situation typically slows performance and may even cause the application to stop responding or to increase memory usage.

Answer 1

In this case, I would like to recommend using SqlCommand.

for example:

var ctx = new DbContext();

string sql = "update contact c inner join zip_city z " +
             "on c.plz = z.plz and c.ort = z.ort set c.status = 'P/O'; ";
sql = sql  + "update contact set status = '???' where status <> 'P/O'; ";

ctx.Database.ExecuteSqlCommand(sql);

That could take just few seconds for your 27,000 records, I think.

Please try.

Answer 2

Thank you all for valuable comments!

To keep the above question-comments in lines, I create an answer here.

I tested in comments provided recommendations with 27,000 records in table contact and 47,000 records in table zip_city but without any database index change (add index suggested by Panagiotis Kanavos).

Followings are performance test results

Original code 1, it takes 113 seconds.

var ctx = new DbContext();
var qry = ctx.contact.Join(ctx.zip_city, c => new { c.zip, c.city }, z => new { z.zip, z.city }, (c, z) => new { c, z })
           .Select(x => new dtoContact { id = x.c.id }).ToList();
foreach (var con in ctx.contact)
{
    if (qry.Any(x => x.Id == con.id))
    {
        con.status = "P/O";
    }
    else
    {
        con.status = "???";
    }
}
ctx.SaveChanges();

Base on DevilSuichiro, "bulk savechanges" is not available in DbContext with MySQL, so here only SaveChanges() is called for single batches of 100 entities. It takes 107 seconds.

var ctx = new DbContext();
var qry = ctx.contact.Join(ctx.zip_city, c => new { c.zip, c.city }, z => new { z.zip, z.city }, (c, z) => new { c, z })
           .Select(x => new dtoContact { id = x.c.id }).ToList();
int count = 0;
foreach (var con in ctx.contact)
{
    if (qry.Any(x => x.Id == con.id))
    {
        con.status = "P/O";
    }
    else
    {
        con.status = "???";
    }
    ++count;
    if (count % 100 == 0)
    {
        ctx.SaveChanges();
    }
}
ctx.SaveChanges();

Based on Rand Random, HashSet is used to increase the performance. It still take more than 100 seconds.

var ctx = new DbContext();
var qry = ctx.contact.Join(ctx.zip_city, c => new { c.zip, c.city }, z => new { z.zip, z.city }, (c, z) => new { c, z }).Select(id = x.c.id);
var ids = new HashSet<int>(qry);           
foreach (var con in ctx.contact)
{
    if (ids.Contains(con.id))
    {
        con.status = "P/O";
    }
    else
    {
        con.status = "???";
    }
}
ctx.SaveChanges();

Original code 2, it takes 67 seconds.

var ctx = new DbContext();
var zc = ctx.zip_city.ToList();
foreach (var con in ctx.contact)
{
    if (zc.Any(x => x.zip == con.zip && x.city == con.city))
    {
        con.status = "P/O";
    }
    else
    {
        con.status = "???";
    }
}
ctx.SaveChanges();

Based on Panagiotis Kanavos, Using any ORMs (like here DbContext SaveChanges() ) for this Data-UPDATE is really not a fit solution.

I tested the code from alex, nvoigt, it takes less than 2 seconds.

var ctx = new DbContext();
string sql = "update contact set status = '???'; " + 
    "update contact c inner join zip_city z on c.plz = z.plz and c.ort = z.ort set c.status = 'P/O'; ";
ctx.Database.ExecuteSqlCommand(sql);