OutOfMemoryException when iterating large dataset

Question

I have a table with around 100,000 rows (and it is going to be getting much larger).

My code now is throwing an OutOfMemoryException when I get up to around the 80,000 record in my loop (even though my system has over 10gb free at the time, it looks like visual studio is limited to around 1.5gb).

The code is intended to loop over all records and just check for certain conditions. I took out my code that actually processes the record and the memory still fills up.

using (var db = new PlaceDBContext())
{
    Messages.Output("Total: " + db.Companies.Count());
    int count = 0;
    foreach (var company in db.Companies)
    {

        // I am not actually doing anything here,
        // I took out my code and the memory still fills up
        // CheckMatchConditions(company);

        count++;

        Console.SetCursorPosition(0, Console.CursorTop);
        Console.Write(count.ToString() + "          ");

    }

}

I thought it might be to do with keeping the context open so I refactored the code to just take 1,000 records at a time and enumerate them all to a list first. This is what I came up with:

int count = 0;
int total = 0;
using (var db = new PlaceDBContext())
{
    Messages.Output("Total: " + db.Companies.Count());
    total = db.Companies.Count();
}
while (count < total)
{
    List<Company> toMatch = new List<Company>();
    using (var db = new PlaceDBContext())
    {
        toMatch = db.Companies.Include(x => x.CompaniesHouseRecords).OrderBy(x => x.ID).Skip(count).Take(1000).ToList();
    }

    foreach (var company in toMatch)
    {

        // CheckMatchConditions(company);

        count++;

        Console.SetCursorPosition(0, Console.CursorTop);
        Console.Write(count.ToString() + "          ");

    }
}

This works a lot slower but still fills up the memory at about the same rate of records looped.

As I commented out my actual method that does anything it must just be these toMatch lists lingering in memory.

I am at a loss here, can someone shed some light on how I should be managing the memory?

Can you convert your match condition into where condition ? Something Like db.Companies.Where(CheckMatchConditions).. Also do you want to process objects somehow ? If so, how ? — DolphinSX
– DolphinSX, Commented Oct 10, 2016 at 13:22

Igor · Accepted Answer · 2016-10-10 13:00:50Z

3

Add .AsNoTracking() which ensures that the DbContext does not keep track of the entities.

using (var db = new PlaceDBContext())
{
    Messages.Output("Total: " + db.Companies.Count());
    int count = 0;
    foreach (var company in db.Companies.AsNoTracking())
    {
        count++;
        Console.SetCursorPosition(0, Console.CursorTop);
        Console.Write(count.ToString() + ". company : " + company.someProp);
    }
}

answered Oct 10, 2016 at 13:00

Igor

62.5k10 gold badges111 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Guerrilla Over a year ago

Perfect! memory doesn't go above 60mb now

Konstantin Ershov · Accepted Answer · 2016-10-10 13:02:05Z

0

Do not iterate through the objects collection, because EF tracking changes since you select entity into an object. Iterate through some DTO.

db.Companies.Select(c => new CompanyDto{ Name = c.Name});

But it's really better to not iterate through your whole data collection. Just try to write your check condition method in LINQ to allow SQL server do filtering.

answered Oct 10, 2016 at 13:02

Konstantin Ershov

7504 silver badges13 bronze badges

Collectives™ on Stack Overflow

OutOfMemoryException when iterating large dataset

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related