Reputation: 838
I have this SQL query:
select
sum(h.nbHeures)
from
Heures h
join
HeuresProjets hp on h.HpGuid=hp.HPId
join
ActivityCodes ac on h.Code=ac.ActivityId
join
MainDoeuvre mdo on ac.ActivityId=mdo.CodeGuid
where
hp.ExtraGuid = '61E931C8-3268-4C9C-9FF5-ED0213D348D0'
and mdo.NoType = 1
It runs in less than a second, which is good. My project uses LINQ to entities to get data. This (very similar to the sql) query is terribly slow, taking more than a minute.
var result = (from hp in this.HeuresProjets
join h in ctx.Heures on hp.HPId equals h.HpGuid
join ac in ctx.ActivityCodes on h.Code equals ac.ActivityId
join mdo in ctx.MainDoeuvre on ac.ActivityId equals mdo.CodeGuid
where hp.ExtraGuid == this.EntityGuid && mdo.NoType == (int)spType
select h.NbHeures).Sum();
total = result;
I tried using nested loops instead. It's faster but still slow (~15 seconds).
foreach (HeuresProjets item in this.HeuresProjets)
{
foreach (Heures h in ctx.Heures.Where(x => x.HpGuid == item.HPId))
{
if (h.ActivityCodes != null && h.ActivityCodes.MainDoeuvre.FirstOrDefault() != null && h.ActivityCodes.MainDoeuvre.First().NoType == (int)type)
{
total += h.NbHeures;
}
}
}
Am I doing something obviously wrong? If there's no way to optimize this I'll just call a stored procedure but I would really like the keep the logic in the code.
EDIT
I modified my query according to IronMan84's advice.
decimal total = 0;
var result = (from hp in ctx.HeuresProjets
join h in ctx.Heures on hp.HPId equals h.HpGuid
join ac in ctx.ActivityCodes on h.Code equals ac.ActivityId
join mdo in ctx.MainDoeuvre on ac.ActivityId equals mdo.CodeGuid
where hp.ExtraGuid == this.EntityGuid && mdo.NoType == (int)spType
select h);
if(result.Any())
total = result.Sum(x=>x.NbHeures);
This almost works. It runs fast and gives back a decimal but:
1. It's not the right value
2. The result is clearly cached because it returns the exact same value with different parameters.
Upvotes: 3
Views: 126
Reputation: 16149
From looking at your code I'm thinking that your query is grabbing every single record from those tables that you're joining on (hence the long amount of time). I'm seeing you using this.HeuresProjets
, which I'm assuming is a collection of database objects that you already had grabbed from the database (and that's why you're not using ctx.HeuresProjets
). That collection, then, has probably already been hydrated by the time you get to your join query. In which case it becomes a LINQ-To-Objects query, necessitating that EF go and grab all of the other tables' records in order to complete the join.
Assuming I'm correct in my assumption (and let me know if I'm wrong), you might want to try this out:
var result = (from hp in ctx.HeuresProjets
join h in ctx.Heures on hp.HPId equals h.HpGuid
join ac in ctx.ActivityCodes on h.Code equals ac.ActivityId
join mdo in ctx.MainDoeuvre on ac.ActivityId equals mdo.CodeGuid
where hp.ExtraGuid == this.EntityGuid && mdo.NoType == (int)spType
select h).Sum(h => h.NbHeures);
total = result;
Also, if this.HeuresProjets
is a filtered list of only specific objects, you can then just add to the where
clause of the query to make sure that the IDs are in this.HeuresProjets.Select(hp => hp.HPId)
Upvotes: 3