How can I run the above code in the fastest way. What is the best practice?

public ActionResult ExampleAction()
        {
            // 200K items
            var results = dbContext.Results.ToList();

            foreach (var result in results)
            {
                // 10 - 40 items
                result.Kazanim = JsonConvert.SerializeObject(
                    dbContext.SubTables // 2,5M items
                    .Where(x => x.FooId == result.FooId)
                    .Select(select => new
                    {
                        BarId = select.BarId,
                        State = select.State,
                    }).ToList());

                dbContext.Entry(result).State = EntityState.Modified;
                dbContext.SaveChanges();
            }

            return Json(true, JsonRequestBehavior.AllowGet);
        }

This process takes an average of 500 ms as sync. I have about 2M records. The process is done 200K times.

How should I code asynchronously?

How can I do it faster and easier with an async method.

CodePudding user response：

There's nothing you can do with that code asynchronously for improving its performance. But there's something that can certainly make it faster.

If you call dbContext.SaveChanges() inside the loop, EF will write back the changes to the database for every single entity as a separate transaction. Move your dbContext.SaveChanges() after the loop. This way EF will write back all your changes at once after in one single transaction.

Always try to have as few calls to .SaveChanges() as possible. One call with 50 changes is much better, faster and more efficient than 50 calls for 1 change each.

CodePudding user response：

Here are two suggestions that can improve the performance multiple orders of magnitude:

1. Do work in batches:

   1. Make the client send a page of data to process; and/or
   2. In the web server code add items to a queue and process them separately.

2. Use SQL instead of EF:

   1. Write an efficient SQL; and/or
   2. Use the stored proc to do the work inside the db rather than move data between the db and the code.