Slicing Entity Framework's transaction in order to reduce memory usage

Question

So my problem is I have to run a ASP .NET application running in 32 bit. But I have to process a bunch of records and it makes a huge change in the database. In case of something fails I have to put the whole process in a transaction. I thought that if I use the DBContext.SaveChanges() after every f.e. 10000 processed records and get rid of the references etc. so the GC can do it's job can solves my issue but it's not. The problem is that every process using the DB (I mean CRUD operations) are slowing down significantly (even 10 times slower or so).

So my code is something like this, for better understanding:

public void StartProcess()
{
    localScopeCtx = cont.Resolve<IApplicationDbContext>();
    IDbContextTransaction trans = localScopeCtx.BeginTransaction();

    int take = 10000;
    int index = 1;
    while (index <= needToProcess)
    {
        var records = GetRecords(take)
        List<obj> recordsToDelete;
        ProcessRecords(take, out recordsToDelete);

        localScopeCtx.Records.RemoveRange(recordsToDelete);

        localScopeCtx.SaveChanges();

        DetachAllEntities(localScopeCtx);
    }

    trans.Commit();
}

public void DetachAllEntities(IApplicationDbContext dbContext)
{
    var changedEntriesCopy = dbContext.ChangeTracker.Entries()
        .Where(e => e.State == EntityState.Added ||
                    e.State == EntityState.Modified ||
                    e.State == EntityState.Deleted ||
                    e.State == EntityState.Unchanged)
        .ToList();

    foreach (var entry in changedEntriesCopy)
        entry.State = EntityState.Detached;
}

In the first loop everything is fine. It is fast and it is doing it's job like charm. But after one localScopeCtx.SaveChanges() everything slows down as I said. I think that is because the EF doesn't really send the modifications to the database but stores it in the memory (according to the memory usage), but in a more clever way that it never run out of it (maybe virtual memory usage or so).

Any suggestions what can be the solution?

EDIT: Adding the DetachAllEntities method to the process;

The entities are all still attached in the Change Tracker. See eg https://stackoverflow.com/questions/27423059/how-do-i-clear-tracked-entities-in-entity-framework — David Browne - Microsoft, Jan 15 '20 at 17:59
@davidbrowne If I want to make a change one of the entity I detached earlier it will get the object from the DB again? Otherwise i can do anything with that object it wont be saved on the next SaveChanges() call. Correct me if i am wrong. — turanszkik, Jan 15 '20 at 18:17
When an object is detached it's no longer tracked by the DbContext. It's just a normal .NET object. You can re-attach, or re-fetch the entity later if you need to. — David Browne - Microsoft, Jan 15 '20 at 20:49
@DavidBrowne-Microsoft Sadly it didn't solve my problem. With detaching all the entities the further process stays as slow as it was before the detaching. Maybe another solution? Or I'm doing something wrong? (see the edit I have made in the question) — turanszkik, Jan 16 '20 at 07:11

score 0 · Accepted Answer · answered Jan 16 '20 at 11:37

Okay, so I have found a solution and if anyone looking for something like it here it is:

I have recognized that without creating a new DbContext it will never "let go" any of the object that is previously loaded in. So the solution is to change the Transaction creating and the DbContext creating's sequence and creating a new Context after each "slice".

My code looks like something like this:

public void StartProcess()
{
    /*This part isn't exacly the same in my program but the point is the same: create a SqlConnection*/
    string providerName = "System.Data.SqlClient";
    string serverName = ".";
    string databaseName = "SchoolDB";

    // Initialize the connection string builder for the SQL Server provider.
    SqlConnectionStringBuilder sqlBuilder =
        new SqlConnectionStringBuilder();

    // Set the properties for the data source.
    sqlBuilder.DataSource = serverName;
    sqlBuilder.InitialCatalog = databaseName;
    sqlBuilder.IntegratedSecurity = true;

    using (SqlConnection con = new SqlConnection(sqlBuilder.ToString()))
    {
    /*From this point my tested code has exactly the same construction*/
        con.Open();
        using (SqlTransaction transaction = con.BeginTransaction())
        {
            int take = 10000;
            int index = 1;
            while (index <= needToProcess)
            {
                localScopeCtx =  new ApplicationDbContext(trans, false);

                var records = GetRecords(take)
                List<obj> recordsToDelete;
                ProcessRecords(take, out recordsToDelete);

                localScopeCtx.Records.RemoveRange(recordsToDelete);

                localScopeCtx.SaveChanges();
                localScopeCtx.Dispose()
            }
            trans.Commit();
        }
    }
}

Yes, the recommended solution involves closing and reopening (recreating) the DBContext. Also, you can try [Tanneryd.BulkOperations.EF6](https://github.com/mtanneryd/ef6-bulk-operations) NuGet package. I have used it in the past for bulk insert operations with impressive performance. It has also bulk update and bulk delete operations. — Pepelui360, Jan 16 '20 at 12:12
@Pepelui360 Yes, that can be another solution, sadly not in my case, because i can't gather all the data before deleting or modifying it. I could use bulk operations instead of `localScopeCtx.Records.RemoveRange(recordsToDelete);` but I don't really want to mix Bulk mode and EF mode within one transaction. — turanszkik, Jan 20 '20 at 10:09

Slicing Entity Framework's transaction in order to reduce memory usage

1 Answers1