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?
taskLog.Task.TaskDescription
will be handled just fine without lazy loading. Can you disable LazyLoading and Proxy creation for the first sample as well as usingAsNoTracking
? Would be interesting whether this boosts performance. \$\endgroup\$Select(taskLog => new { ... })
instead ofnew TaskLog() ...
) and don't cast theTaskTypeID
but take it as the returned type? Below you commented that the query runs in like 15ms, rest being theToList
... how did you test this, it sounds a bit fast? \$\endgroup\$db.Database.Log = x => WriteSomewhere(x)
and check query performance with some dedicated SQL tool for comparison) \$\endgroup\$JobName
andTaskDescription
sum up to 1000 characters per record, which is more than all the other properties combined. Since you mention there will be a lot less entries (1000s vs 200000 records), the solution from @JanDotNet makes perfect sense, it will just transfer a lot less duplicated strings from server to client. \$\endgroup\$