Read the huge table with LINQ to SQL: Running from memory versus slow paging

Question

I have a huge table that I need to read for a specific order and calculate some statistics. The table already has a clustered index for the correct order, so retrieving the records themselves is pretty fast. I am trying to use LINQ to SQL to simplify the code I need to write. The problem is that I don’t want to load all the objects into memory, since the DataContext seems to support them, but trying to page them leads to terrible performance problems.

Here is a breakdown. Initial Attempt:

var logs = 
    (from record in dataContext.someTable 
     where [index is appropriate]
     select record);

foreach( linqEntity l in logs )
{
    // Do stuff with data from l
}

It is pretty fast and flows at a good speed, but the problem is that the memory usage in the application continues to grow, never stops. I assume that LINQ to SQL objects are stored in memory and not located properly. Therefore, after reading from memory, when creating a large number of C # objects, I tried the following approach. This is apparently the common paradigm Skip/ Takethat many people use, with the added function of preserving memory.

Note that it _connis created in advance, and a temporary data context is created for each request, as a result of which related objects are collected in garbage.

int skipAmount = 0;
bool finished = false;

while (!finished)
{
    // Trick to allow for automatic garbage collection while iterating through the DB
    using (var tempDataContext = new MyDataContext(_conn) {CommandTimeout = 600})
    {               
        var query =
            (from record in tempDataContext.someTable
             where [index is appropriate]
             select record);

        List<workerLog> logs = query.Skip(skipAmount).Take(BatchSize).ToList();
        if (logs.Count == 0)
        {
            finished = true;
            continue;
        }

        foreach( linqEntity l in logs )
        {
            // Do stuff with data from l
        }

        skipAmount += logs.Count;
    }
}

, , . , : Skip , , -, . , , . :

• LINQ Skip()
• LINQ2SQL /

LINQ, . ? , - DataContext , , .

Answer 1

, DataContext ObjectTrackingEnabled = false , . , , .

using (var readOnlyDataContext = 
    new MyDataContext(_conn) {CommandTimeout = really_long, ObjectTrackingEnabled = false})
{                                                 
    var logs =
        (from record in readOnlyDataContext.someTable
         where [index is appropriate]
         select record);

    foreach( linqEntity l in logs )
    {
        // Do stuff with data from l   
    }                
}

. DataContext, , , , . SQL-, , , , , , .

. , DataContext There is already an open DataReader associated with this Command which must be closed first. 100% . , # .