Processing large datasets using LINQ

Question

Every time I write a program of the form below using LINQ to SQL, I end up with a program that just grabs more and more memory as it runs and falls over in a heap consuming 2GB after perhaps as little as 25,000 records. I always end up rewriting it using ADO.NET. What am I doing wrong?

Clarification: This question is not about speed of processing; answers about making it go faster are of no relevance.

foreach (int i=0; i<some_big_number; i++)
{
    using (myDC dc = new myDC())  // my DataContext
    {
        myRecord record = (from r in dc.myTable where r.Code == i select r).Single();

        // do some LINQ queries using various tables from the data context
        // and the fields from this 'record'.  i carefully avoid referencing
        // any other data context than 'dc' in here because I want any cached
        // records to get disposed of when 'dc' gets disposed at the end of 
        // each iteration.

        record.someField = newValueJustCalculatedAbove;
        dc.SubmitChanges();
    }
}

Answer 1

you could try putting the creation of the datacontext outside of the for loop, not sure how much memory that would save you though.

Maybe you can call GC.Collect() after each loop, see if you can cause garbage collection manually.

Answer 2

You are going to the database twice for every loop iteration - once to retrieve the row, then again to update the row. This isn't very efficient.

You should operate in batches:

• Get a set of rows up front by selecting on a range rather than a single value, ie 0-100 for the first batch, 101-200 for the next batch, and so on. This will be fastest if you have a clustered index defined on the Code column.

• Create the data context prior to entering the loop

• Inside the loop, just update the objects

• Call SubmitChanges() AFTER the loop has finished, this will send all updates to the database in a single connection / transaction

• Repeat for the next batch

You should make the batch size configurable, as you can't be sure what batch size will yield the best performance - don't hardcode it into the application.

Also, I would use SingleOrDefault() with null-checking instead of Single(), unless you can guarantee that there will always be a row for any value of i.

EDIT:

In terms of memory usage, that's much harder to control, but it's not peculiar to LINQ to SQL, any batch processing algorithm has to deal with this. While I don't recommend using GC.Collect() in practice, it is usually sufficient as a workaround after processing a large batch.

You could also look at reducing the amount of data that you retrieve per row (depending on how much this is to begin with). You can create a new entity that maps to a much smaller set of columns from the same table, potentially just one or two, so that when you select that entity you are only retrieving the columns you intend to work with to begin with. This would improve both speed AND memory footprint as less data travels over the wire, and objects are much smaller.

Answer 3

You are putting pressure on the data context to generate the query from scratch every time.

Try using a compiled query instead.

Answer 4

I wasn't able to replicate the problem. Memory useage was flat. Slow performance, but constant memory.

Are you sure you're not leaking elsewhere? Can you produce a minimal code sample that reproduces the problem?

Edit:

I used the virtually the same sample code:

for (int ii = 1; ii < 200000; ii++)
{
    using (var dc = new PlayDataContext())
    {
        var record = 
            (from r in dc.T1s where r.Id == ii select r).SingleOrDefault();
        if (record != null)
        {
            record.Name = "S";
            dc.SubmitChanges();
        }
    }
}

without issue.

So things to rule out:

• Framework version. I'm on latest.
• DataContext/entity complexity. My test table is just two fields, and Id (int) and a Name (nvarchar(max)).

Can you reproduce the problem with the latest FW, with a small sample DataContext?