CODEWITHSUNDEEP
sqllinqlinq-to-entities
0xFF
0xFF

Reputation: 838

What would be a reasonably fast way to code this sql query in c#?

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

Answers (1)

Corey Adler
Corey Adler

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

Related Questions