Linq query runs in a fraction of a second, but .ToList() takes 3.5 seconds

Question

I'm running a Linq query that returns about 25 records, each with 10 numeric columns. According to my code profiler, the query itself is taking a fraction of a second - but the call to .ToList() is taking about 3.5 seconds. As noted, the volume of data return from SQL is trivial, so the time taken to copy it into a List should not be burdensome.

Why is .ToList() taking so long? And how could it be improved?

EDIT: With appreciation to all the rapid answers, let me state more clearly: I am completely aware of the fact that the query is lazy loaded. The phenomenon I am seeing is that both SQL Server Profiler and ANTS Performance Profiler are reporting that the actual query execution time was a fraction of a second.

Here's a screen shot from ANTS:

Notice that the calling method is taking 4.3 seconds, while none of the actual SQL queries is taking longer than .05 seconds. Could it be other code in that method, and not SQL? Let's look at how ANTS breaks down the code profile here:

Smoking gun proof: .ToList() is taking 3.36 seconds, of which maybe 0.05 sec can be attributed to actual query execution time, leaving 3.31 sec unaccounted for.

Where's that time going to?

EDIT 2: Okay, you asked for it, so here's my code:

public static Expression<Func<Student, Chart>> GetStudentAssessmentQuestionResultByStudentIdNew(MyDataEntities db)
{
    return s => new Chart
        {
            studentID = s.ID,
            Lines =
                    db.StudentAssessmentAnswers
                        .Where(
                                saa =>
                                saa.StudentAssessment.BorrowedBook.StudentID == s.ID && saa.PointsAwarded != null &&
                                saa.Question.PointValue > 0 &&
                                (saa.Question.QuestionType == QuestionType.MultipleChoice ||
                                 saa.Question.QuestionType == QuestionType.OpenEnded))
                        .GroupBy(
                                saa =>
                                new
                                {
                                    saa.StudentAssessment.AssessmentYear,
                                    saa.StudentAssessment.AssessmentMonth,
                                    saa.Question.CommonCoreStandard
                                },
                                saa => saa)
                        .Select(x => new
                                     {
                                         x.Key.AssessmentYear,
                                         x.Key.AssessmentMonth,
                                         x.Key.CommonCoreStandard,
                                         PercentagePointValue =
                                         (float)(x.Sum(a => a.PointsAwarded) * 100) / (x.Sum(a => a.Question.PointValue))
                                     })
                        .OrderByDescending(x => x.CommonCoreStandard)
                        .GroupBy(r1 => (byte)r1.CommonCoreStandard)
                        .Select(g => new ChartLine
                                     {
                                         ChartType = ((ChartType)g.Key),
                                         //type = g.Key.ToString(),
                                         type = g.Key,
                                         Points = g.Select(grp => new ChartPoint
                                                {
                                                    Year = grp.AssessmentYear.Value,
                                                    Month = grp.AssessmentMonth.Value,
                                                    yValue = grp.PercentagePointValue
                                                })
                                     })
        };
}

This is called by:

var students =
    db.ClassEnrollments
        .Where(ce => ce.SchoolClass.HomeRoomTeacherID == teacherID)
        .Select(s => s.Student);
var charts = CCProgressChart.GetStudentAssessmentQuestionResultByStudentIdNew(db);
var chartList = students.Select(charts).ToList();

Does that help any?

Answer 1

.ToList() is actually executing the query. So your query is taking 2.5 seconds to run.

Read more about Deferred Execution here.

Without posting your actual LINQ query, we have no means to help you with it's performance (if you post it, I'll update my answer).

Answer 2

LINQ to SQL is lazy loaded. Nothing actually happens until you call the ToList().

Edit:

Since you've updated your answer there's a few things to note. ToList() is taking 73.4% of the time in the constructor. This is the place the SQL statment is actually executed. Below is the actual ToList method:

public static List<TSource> ToList<TSource>(this IEnumerable<TSource> source)
{
    if (source != null)
    {
        return new List<TSource>(source);
    }
    else
    {
        throw Error.ArgumentNull("source");
    }
}

Your SQL is not actually executed until the following line is called:

return new List<TSource>(source);

So yes ToList is taking forever but that's because its started to process the source parameter. Antz is a very useful tool but it can sometimes lead your down the wrong path. The issue is most likely going to be one of these:

1. The T-SQL generated is hideous and slow.
2. Opening the connection to the SQL server is slow
3. Your lambda expression tree is complicated and entity framework is taking a long time to process it and generate the T-SQL

I would start by first opening up SQL profiler and looking at the SQL. Even what looks like the simplest statement can be doing something crazy, the only way to know for sure is to look at the actual T-SQL.

If the T-SQL is ok your going to have to play around with your Lambda expression to make it simpler, and easier to process.

Answer 3

Linq2SQL uses something called Lazy Loading. Basically until you look in the container you asked for, it doesn't go near the data source. You can keep building up your query, but as soon as you toList it, or look at the First, it builds up your query, sends it to the database and waits for the result.... hence it takes so long

Answer 4

Without more detail, it's hard to know for sure. But, what type is your List of? What does that class do in the constructor/setters? Does it implement INotifyPropertyChanged, or fire any other events?

Bear in mind the query will only be executed when you call ToList. Up until that point, it's just a queryable object. Look at it with a SQL profiler, and you'll see that that's when it does the DB access.