In my Asp.Net Web Api project, there's a GET method to fetch all processes. Process in my app is an entity to log various maintenence activities, it has many properties, e.g. beginning and end. Each processes can be handled by many users and so single Process can have many Handlings associated with it. Each handling also has begining and end timestamps. Recently I added "HandlingsLength" property to calculate for each process its handlings length. Unfortunately, adding this property made all requests targeting the method run almost twice as long..
Now, I'm looking for a way to improve the execution time of my method, either by complete rewrite, or by using some smart workaround. There are requests which doesn't mind waiting long for response, but there are also requests that don't need this property calculated and need to be fast too. My first idea was passing handlingsLength argument to my method and depending on its value HandlingsLength would be calculated or just set to null.
HandlingsLength = handlingsLength == null || handlingsLength == false ? null : grp.Where(ph => ph.ha.HandlingId > 0).Sum(h => DbFunctions.DiffMinutes(h.ha.StartedOn, h.ha.FinishedOn)) == null ? grp.Where(ph => ph.ha.HandlingId > 0).Sum(h => DbFunctions.DiffMinutes(h.ha.StartedOn, DateTime.Now)) : grp.Where(ph => ph.ha.HandlingId > 0).Sum(h => DbFunctions.DiffMinutes(h.ha.StartedOn, h.ha.FinishedOn))
Unfortunately it doesn't work, even if I pass handlingsLenght=null to indicate this property shouldn't be calculated, run time is more-less the same. Probably my linq statement is compiled to SQL at compilation so it doesn't know the value parameter provided on runtime. If there's a way to effectively 'turn off' this property at runtime, it's very welcome.
Here's complete body of my linq to entity statement:
items = (from p in db.JDE_Processes
join uuu in db.JDE_Users on p.FinishedBy equals uuu.UserId into finished
from fin in finished.DefaultIfEmpty()
join t in db.JDE_Tenants on p.TenantId equals t.TenantId
join u in db.JDE_Users on p.CreatedBy equals u.UserId
join at in db.JDE_ActionTypes on p.ActionTypeId equals at.ActionTypeId
join uu in db.JDE_Users on p.StartedBy equals uu.UserId into started
from star in started.DefaultIfEmpty()
join lsu in db.JDE_Users on p.LastStatusBy equals lsu.UserId into lastStatus
from lStat in lastStatus.DefaultIfEmpty()
join pl in db.JDE_Places on p.PlaceId equals pl.PlaceId
join comp in db.JDE_Components on p.ComponentId equals comp.ComponentId into comps
from components in comps.DefaultIfEmpty()
join s in db.JDE_Sets on pl.SetId equals s.SetId
join a in db.JDE_Areas on pl.AreaId equals a.AreaId
join h in db.JDE_Handlings on p.ProcessId equals h.ProcessId into hans
from ha in hans.DefaultIfEmpty()
where p.TenantId == TenantId && p.CreatedOn >= dFrom && p.CreatedOn <= dTo
group new { p, fin, t, u, at, started, lastStatus, lStat, pl, s, a, ha }
by new
{
p.ProcessId,
p.Description,
p.StartedOn,
p.StartedBy,
p.FinishedOn,
p.FinishedBy,
p.PlannedFinish,
p.PlannedStart,
p.PlaceId,
pl.SetId,
SetName = s.Name,
pl.AreaId,
AreaName = a.Name,
pl.Image,
p.Reason,
p.CreatedBy,
CreatedByName = u.Name + " " + u.Surname,
p.CreatedOn,
p.ActionTypeId,
p.Output,
p.InitialDiagnosis,
p.RepairActions,
p.TenantId,
p.MesId,
p.MesDate,
p.Comment,
TenantName = t.TenantName,
p.IsActive,
p.IsCompleted,
p.IsFrozen,
p.IsSuccessfull,
p.IsResurrected,
ActionTypeName = at.Name,
FinishedByName = fin.Name + " " + fin.Surname,
StartedByName = star.Name + " " + star.Surname,
PlaceName = pl.Name,
ComponentId = p.ComponentId,
ComponentName = components.Name,
LastStatus = p.LastStatus == null ? (ProcessStatus?)null : (ProcessStatus)p.LastStatus, // Nullable enums handled
p.LastStatusBy,
LastStatusByName = lStat.Name + " " + lStat.Surname,
p.LastStatusOn
} into grp
orderby grp.Key.CreatedOn descending
select new Process
{
ProcessId = grp.Key.ProcessId,
Description = grp.Key.Description,
StartedOn = grp.Key.StartedOn,
StartedBy = grp.Key.StartedBy,
StartedByName = grp.Key.StartedByName,
FinishedOn = grp.Key.FinishedOn,
FinishedBy = grp.Key.FinishedBy,
FinishedByName = grp.Key.FinishedByName,
ActionTypeId = grp.Key.ActionTypeId,
ActionTypeName = grp.Key.ActionTypeName,
IsActive = grp.Key.IsActive,
IsFrozen = grp.Key.IsFrozen,
IsCompleted = grp.Key.IsCompleted,
IsSuccessfull = grp.Key.IsSuccessfull,
PlaceId = grp.Key.PlaceId,
PlaceName = grp.Key.PlaceName,
PlaceImage = grp.Key.Image,
SetId = grp.Key.SetId,
SetName = grp.Key.SetName,
AreaId = grp.Key.AreaId,
AreaName = grp.Key.AreaName,
Output = grp.Key.Output,
TenantId = grp.Key.TenantId,
TenantName = grp.Key.TenantName,
CreatedOn = grp.Key.CreatedOn,
CreatedBy = grp.Key.CreatedBy,
CreatedByName = grp.Key.CreatedByName,
MesId = grp.Key.MesId,
InitialDiagnosis = grp.Key.InitialDiagnosis,
RepairActions = grp.Key.RepairActions,
Reason = grp.Key.Reason,
MesDate = grp.Key.MesDate,
Comment = grp.Key.Comment,
ComponentId = grp.Key.ComponentId,
ComponentName = grp.Key.ComponentName,
PlannedStart = grp.Key.PlannedStart,
PlannedFinish = grp.Key.PlannedFinish,
LastStatus = grp.Key.LastStatus,
LastStatusBy = grp.Key.LastStatusBy,
LastStatusByName = grp.Key.LastStatusByName,
LastStatusOn = grp.Key.LastStatusOn,
IsResurrected = grp.Key.IsResurrected,
OpenHandlings = grp.Where(ph => ph.ha.HandlingId > 0 && (ph.ha.IsCompleted == null || ph.ha.IsCompleted == false)).Count(),
AllHandlings = grp.Where(ph => ph.ha.HandlingId > 0).Count(),
AssignedUsers = (from pras in db.JDE_ProcessAssigns
join uu in db.JDE_Users on pras.UserId equals uu.UserId
where pras.ProcessId == grp.Key.ProcessId
select uu.Name + " " + uu.Surname),
GivenTime = givenTime == null || givenTime == false ? 0 : (from prac in db.JDE_ProcessActions
join a in db.JDE_Actions on prac.ActionId equals a.ActionId
where prac.ProcessId == grp.Key.ProcessId
select a.GivenTime).Sum(),
FinishRate = finishRate == null || finishRate == false ? 0 : db.JDE_ProcessActions.Count(i => i.ProcessId == grp.Key.ProcessId)==0
? 100 : (((float)db.JDE_ProcessActions.Count(i => i.ProcessId == grp.Key.ProcessId && i.IsChecked == true)
/ (float)db.JDE_ProcessActions.Count(i => i.ProcessId == grp.Key.ProcessId))*100),
HasAttachments = db.JDE_FileAssigns.Any(f => f.ProcessId == grp.Key.ProcessId),
HandlingsLength = handlingsLength == null || handlingsLength == false ? null : grp.Where(ph => ph.ha.HandlingId > 0).Sum(h => DbFunctions.DiffMinutes(h.ha.StartedOn, h.ha.FinishedOn)) == null ? grp.Where(ph => ph.ha.HandlingId > 0).Sum(h => DbFunctions.DiffMinutes(h.ha.StartedOn, DateTime.Now)) : grp.Where(ph => ph.ha.HandlingId > 0).Sum(h => DbFunctions.DiffMinutes(h.ha.StartedOn, h.ha.FinishedOn))
});
Please note that this question has also been asked at StackOverflow.com
