Entity Framework (EF) OutOfMemoryException

Question

I have > 67000 records coming to my system from another source. After applying business rules to those records I have to store them to the database. I use following code for doing that:

        using (var context = new MyEntities())
        {
            var importDataInfo = context.ImportDataInfoes.First(x => x.ID == importId);

            importedRecords.ForEach(importDataInfo.ValuationEventFulls.Add);

            context.SaveChanges();
        }

After executing code I am getting following error (OutOfMemoryException)

    Error in executing code|Exception of type 'System.OutOfMemoryException' was thrown.*   at System.Data.Mapping.Update.Internal.KeyManager.<WalkGraph>d__5.MoveNext()
   at System.Data.Mapping.Update.Internal.KeyManager.GetPrincipalValue(PropagatorResult result)
   at System.Data.Mapping.Update.Internal.UpdateCompiler.GenerateValueExpression(EdmProperty property, PropagatorResult value)
   at System.Data.Mapping.Update.Internal.UpdateCompiler.BuildSetClauses(DbExpressionBinding target, PropagatorResult row, PropagatorResult originalRow, TableChangeProcessor processor, Boolean insertMode, Dictionary`2& outputIdentifiers, DbExpression& returning, Boolean& rowMustBeTouched)
   at System.Data.Mapping.Update.Internal.UpdateCompiler.BuildInsertCommand(PropagatorResult newRow, TableChangeProcessor processor)
   at System.Data.Mapping.Update.Internal.TableChangeProcessor.CompileCommands(ChangeNode changeNode, UpdateCompiler compiler)
   at System.Data.Mapping.Update.Internal.UpdateTranslator.<ProduceDynamicCommands>d__0.MoveNext()
   at System.Linq.Enumerable.<ConcatIterator>d__71`1.MoveNext()
   at System.Data.Mapping.Update.Internal.UpdateCommandOrderer..ctor(IEnumerable`1 commands, UpdateTranslator translator)
   at System.Data.Mapping.Update.Internal.UpdateTranslator.ProduceCommands()
   at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
   at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)
   at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)

I am using EF 4.0.

My question is there is limitation on number of records to save? What is the best practice to save big number of records (save them in chunks? what about transactions?).

Thanks everybody in advance.

Answer 1

You would probably want to send that data down in batches of probably 1024 records at a time.

You could wrap the loop that batches the records in a transaction so you could rollback the entire sequence if desired. Do note that this transaction will most likely escalate to a distributed transaction.

Distributed transactions can only be applied against a server that has the Microsoft Distributed Transaction Coordinator (MS-DTC) service running. There is a notable performance penalty when working distributed transactions.

Answer 2

In general, .NET is limited to addressing 2GB of memory in a single collection or other object. This is because even in a 64-bit environment, indexers use 32-bit integers (with a max value of 2 billion and change). Even for simple data types like integers, the size of a single int means that only 500 million ints can be stored in a single array. For larger value types like structures, the maximum number of elements of a collection gets really small.

If you're in a 32-bit environment like Windows XP, there are even lower limits; the maximum memory space for the ENTIRE PROGRAM cannot be greater than 2GB. This places some pretty high restrictions on an ETL like yours, and I would not be surprised at all that your program is running out of memory trying to process 67k in-memory records at once.

The solution is to handle records in smaller batches if you can. Try constructing a statement based on the ID, where you return the top 100 records where the ID (hopefully auto-generated) is greater than the largest ID you've already retrieved. Once a record is processed, dispose of it (or just orphan it and let the GC do its job).