I have code more or less like this:

// The Parent entity
public Class Parent
{
    public int Id { get; set; }
    public string SomeProperty { get; set; }

    public List<Child> Children { get; set; }  // Navigation to Children
}

// The Child entity
public Class Parent
{
    public int Id { get; set; }
    public string SomeProperty { get; set; }

    public int ParentId { get; set; } // FK to the Parent
    public Parent Parent { get; set; }
}

// The code that does saves data to the database
public Class MyClass
{
    public void DoSomething(List<Parent> newItems)
    {
        var iterationNumber = 0;

        // newItems contains 100,000 Parent/Child objects!
        // Each newItem consists of a Parent and one Child.
        foreach (var newItem in newItems) {
            // Commit the changes every 500 iterations
            if (iterationNumber   % 500 == 0) {
                await _db.SaveChangesAsync();
            }

            _db.Set<Parent>().Add(newItem);
            existingItems.Add(newItem);
        }

        await _db.SaveChangesAsync();
    }
}

So, I'm adding 100,000 new entities to my database. I'm committing the changes every 500 iterations.

I've noticed that the performance of my loop degrades significantly as it proceeds. I'm looking for suggestions for how to improve the performance of this code.

EDIT:

I had assumed the performance degrades because EF is tracking more and more objects in the newItems list. I tried adding _db.ChangeTracker.Clear() after both of the _db.SaveChangesAsync() calls, but that had no obvious effect on the poor performance.

CodePudding user response：

I think it's a good practice not to use database calls in the loop if you can avoid it.

You can use AddRange but you'll have to write custom code for batch-wise processing.

context.Parent.AddRange(newItems);

//batch-wise processing
const int batchSize = 5000;
var totalCount = newItems.Count();
var batches = Math.Ceiling(totalCount / (double)batchSize);

//disable tracking
context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

for (var i = 0; i < batches; i  )
{
    var batch = newItems.Skip(i * batchSize).Take(batchSize);
    context.Parents.AddRange(batch);
    context.SaveChanges();
}

Or Microsoft.EntityFrameworkCore.BulkExtensions. In BulkExtensions you can perform the batch-wise insertion. No need to write custom code.

context.BulkInsert(newItems, options =>
{
        options.BatchSize = 5000;
        // disable tracking
        options.TrackingBehavior = TrackingBehavior.NoTracking;
});

BulkInsert has a default value of 30-sec timeout. you can increase this in options.Timeout

CodePudding user response：

I had similar issue in past and what I did was on every few iteration I was creating new instance for db context. I utilized using () {} scope so that it will automatically dispose old db context instances.

I assume that you are using dependency injection to instantiate db context & your db context class is DatabaseContext. You need to get IServiceScopeFactory instance from from constructor. And use it to instantiate db context. Explanation is given in comment. You can update your code like below.

Note : If you are not using dependency injection to retrieve db context then you can simply use using (var _db = new DatabaseContext()) { } and remove IServiceScopeFactory & respective using blocks from below code.

public Class MyClass
{
    // serviceScopeFactory will be needed to instantiate db context from dependency injection.
    private readonly IServiceScopeFactory serviceScopeFactory;
    
    // retrieve serviceScopeFactory from constructor
    public CustomerCriteriaRepository(IServiceScopeFactory serviceScopeFactory)
    {
        this.serviceScopeFactory = serviceScopeFactory;
    }

    public void DoSomething(List<Parent> newItems)
    {
        // set batch count to save at single go.
        var batchCount = 500;

        // loop over list and save. Do not use increment condition (i  ) in for.
        for (var i = 0; i < newItems.Count;)
        {
            // create scope which can instantiate db context
            using (var scope = this.serviceScopeFactory.CreateScope())
            {
                // instantiate db context using scope object
                using (var _db = scope.ServiceProvider.GetService<DatabaseContext>())
                {
                    // increase i with batchCount value
                    i  = batchCount;

                    // skip already processed values and take next batch and add them all into _db using AddRange.
                    _db.Set<Parent>().AddRange(newItems.Skip(i).Take(batchCount));
                    
                    // save all newly added objects
                    await _db.SaveChangesAsync();                   
                }
            }
        }
    }
}