Entity Framework Lazy loading performance comparison

Question

The following code takes about 20 seconds to run with about 200,000 records in the TaskLogs table:

using (var db = new DAL.JobManagerEntities())
    {
        return db.TaskLogs.Select(taskLog => new TaskLog()
        {
            TaskLogID = taskLog.TaskLogID,
            TaskID = taskLog.TaskID,
            TaskDescription = taskLog.Task.TaskDescription,
            TaskType = (TaskType)taskLog.Task.TaskTypeID,
            RunID = taskLog.RunID,
            ProcessID = taskLog.ProcessID,
            JobID = taskLog.JobID,
            JobName = taskLog.Job.JobName,
            Result = taskLog.Result,
            StartTime = taskLog.StartTime,
            TimeTaken = taskLog.TimeTaken
        }).OrderByDescending(t => t.RunID).ThenByDescending(t => t.RunID).ThenByDescending(t => t.StartTime).ToList();
    }

I tweaked it until I got something that runs faster. Here's where I got to:

            using (var db = new DAL.JobManagerEntities())
    {
        db.Configuration.LazyLoadingEnabled = false;
        var tasks = db.Tasks.ToList();
        var jobs = db.Jobs.ToList();
        var result = db.TaskLogs.Select(x => new TaskLog()
        {
            TaskLogID = x.TaskLogID,
            TaskID = x.TaskID,
            RunID = x.RunID,
            ProcessID = x.ProcessID,
            JobID = x.JobID,
            Result = x.Result,
            StartTime = x.StartTime,
            TimeTaken = x.TimeTaken
        }).OrderByDescending(t => t.RunID).ThenByDescending(t => t.StartTime).ToList();

        foreach (var r in result)
        {
            r.TaskDescription = tasks.Single(t => t.TaskID == r.TaskID).TaskDescription;
            r.TaskType = (TaskType)tasks.Single(t => t.TaskID == r.TaskID).TaskTypeID;
            r.JobName = jobs.Single(j => j.JobID == r.JobID).JobName;
        }
        return result;}

Which runs in less than 6 seconds for the same number of records.

The TaskLog table is linked to the Job and Task tables as follows:

The Job and Task tables will have 100s and 1000s of records respectively.

Is there anything else I could do in order to further improve the efficiency of the code?

Answer 1

1) Not sure if it impacts performance, but your first code fragment has one redunant order by:

.OrderByDescending(t => t.RunID).ThenByDescending(t => t.RunID)

2) You could improve performance of your seconds code with "client side indexing" (using a dictionary):

var tasksMap = tasks.ToDictionary(t => t.TaskID);
var jobsMap = jobs.ToDictionary(t => t.JobID);

foreach (var r in result)
{
    var task = tasksMap[r.TaskID];
    r.TaskDescription = task.TaskDescription;
    r.TaskType = (TaskType)task.TaskTypeID;
    r.JobName = jobsMap[r.JobID].JobName;
}

Answer 2

You can do two things to speed this query up:

• use joins so that the entire query runs on the server and you don't have to run over the results again with the foreach loop
• add .AsNoTracking() to each table so that EF does not have to create proxy objects for change-tracking.

Here's an example of how it would look like when we apply both suggestions.

using (var db = new DAL.JobManagerEntities())
{
    db.Configuration.LazyLoadingEnabled = false;

    var result = 
        from taskLog in db.TaskLogs.AsNoTracking()
        join task in db.Tasks.AsNoTracking() on taskLog.TaskID equals task.TaskID
        join job in db.Jobs.AsNoTracking() on taskLog.JobID equals job.JobID
        orderby taskLog.RunID descending, taskLog.StartTime descending
        select new TaskLog
        {
            TaskLogID = taskLog.TaskLogID,
            TaskID = taskLog.TaskID,
            RunID = taskLog.RunID,
            ProcessID = taskLog.ProcessID,
            JobID = taskLog.JobID,
            Result = taskLog.Result,
            StartTime = taskLog.StartTime,
            TimeTaken = taskLog.TimeTaken,
            TaskDescription = task.TaskDescription,
            TaskType = (TaskType)task.TaskTypeID,
            JobName = job.JobName
        };


    return result.ToList();
}