Performance linq group by with count

Question

I have a method using linq and a group by expression :

public List<SerieVu> ListSeriesLesPlusVuesSemaine()
{
    DateTime dateIlYaSeptJours = DateTime.Now.AddDays(-7);

    var resultat =
    from uev in Query(uev => uev.userepivu_date > dateIlYaSeptJours)
    group uev by uev.Episode.Saison.Serie into pgroup
    let count = pgroup.Count()
    orderby count descending
    select new SerieVu() { nombreDeVus = count, Serie = pgroup.Key };

    return resultat.Take(7).ToList();
}

This linq query generate this SQL Query :

SELECT TOP (7) [Project1].[C2]                        AS [C1],
               [Project1].[C1]                        AS [C2],
               [Project1].[serie_id]                  AS [serie_id],
               [Project1].[serie_image]               AS [serie_image],
               [Project1].[serie_image_thumb]         AS [serie_image_thumb],
               [Project1].[serie_format]              AS [serie_format],
               [Project1].[serie_motsclefs]           AS [serie_motsclefs],
               [Project1].[serie_statusproduction_id] AS [serie_statusproduction_id],
               [Project1].[serie_nom]                 AS [serie_nom],
               [Project1].[serie_nomvf]               AS [serie_nomvf],
               [Project1].[serie_synopsis]            AS [serie_synopsis],
               [Project1].[serie_syncthetvdb]         AS [serie_syncthetvdb],
               [Project1].[serie_dateajout]           AS [serie_dateajout],
               [Project1].[serie_datemiseajour]       AS [serie_datemiseajour],
               [Project1].[serie_actif]               AS [serie_actif],
               [Project1].[utilisateur_id]            AS [utilisateur_id],
               [Project1].[serie_francais]            AS [serie_francais]
FROM   (SELECT [GroupBy1].[A1]  AS [C1],
               [GroupBy1].[K1]  AS [serie_id],
               [GroupBy1].[K2]  AS [serie_image],
               [GroupBy1].[K3]  AS [serie_image_thumb],
               [GroupBy1].[K4]  AS [serie_format],
               [GroupBy1].[K5]  AS [serie_motsclefs],
               [GroupBy1].[K6]  AS [serie_statusproduction_id],
               [GroupBy1].[K7]  AS [serie_nom],
               [GroupBy1].[K8]  AS [serie_nomvf],
               [GroupBy1].[K9]  AS [serie_synopsis],
               [GroupBy1].[K10] AS [serie_syncthetvdb],
               [GroupBy1].[K11] AS [serie_dateajout],
               [GroupBy1].[K12] AS [serie_datemiseajour],
               [GroupBy1].[K13] AS [serie_actif],
               [GroupBy1].[K14] AS [utilisateur_id],
               [GroupBy1].[K15] AS [serie_francais],
               1                AS [C2]
        FROM   (SELECT [Extent4].[serie_id]                  AS [K1],
                       [Extent4].[serie_image]               AS [K2],
                       [Extent4].[serie_image_thumb]         AS [K3],
                       [Extent4].[serie_format]              AS [K4],
                       [Extent4].[serie_motsclefs]           AS [K5],
                       [Extent4].[serie_statusproduction_id] AS [K6],
                       [Extent4].[serie_nom]                 AS [K7],
                       [Extent4].[serie_nomvf]               AS [K8],
                       [Extent4].[serie_synopsis]            AS [K9],
                       [Extent4].[serie_syncthetvdb]         AS [K10],
                       [Extent4].[serie_dateajout]           AS [K11],
                       [Extent4].[serie_datemiseajour]       AS [K12],
                       [Extent4].[serie_actif]               AS [K13],
                       [Extent4].[utilisateur_id]            AS [K14],
                       [Extent4].[serie_francais]            AS [K15],
                       COUNT(1)                              AS [A1]
                FROM   [dbo].[UtilisateurEpisodeVu] AS [Extent1]
                       INNER JOIN [dbo].[Episode] AS [Extent2]
                         ON [Extent1].[episode_id] = [Extent2].[episode_id]
                       INNER JOIN [dbo].[Saison] AS [Extent3]
                         ON [Extent2].[saison_id] = [Extent3].[saison_id]
                       LEFT OUTER JOIN [dbo].[Serie] AS [Extent4]
                         ON [Extent3].[serie_id] = [Extent4].[serie_id]
                WHERE  [Extent1].[userepivu_date] > '2013-01-11T09:53:26' /* @p__linq__0 */
                GROUP  BY [Extent4].[serie_id],
                          [Extent4].[serie_image],
                          [Extent4].[serie_image_thumb],
                          [Extent4].[serie_format],
                          [Extent4].[serie_motsclefs],
                          [Extent4].[serie_statusproduction_id],
                          [Extent4].[serie_nom],
                          [Extent4].[serie_nomvf],
                          [Extent4].[serie_synopsis],
                          [Extent4].[serie_syncthetvdb],
                          [Extent4].[serie_dateajout],
                          [Extent4].[serie_datemiseajour],
                          [Extent4].[serie_actif],
                          [Extent4].[utilisateur_id],
                          [Extent4].[serie_francais]) AS [GroupBy1]) AS [Project1]
ORDER  BY [Project1].[C1] DESC

My question is how to improve performance of this query ? It take about 6 secondes on a very important page of my website.

Thank you for your help

score 1 · Answer 1 · edited Jan 18 '13 at 10:03

1

You can start by making some indexes in your database you should put the field form where, order by and group by in the indexes you will probably get the most performance improvement by creating an index on the userepivu_date because this WHERE [Extent1].[userepivu_date] > '2013-01-11T09:53:26' will scan your whole table

Creating an index on SQL Server:

CREATE INDEX [indexname] ON Extent1 (userepivu_date)

edited Jan 18 '13 at 10:03

Nilesh

20,521
16
92
148

answered Jan 18 '13 at 09:45

BramCrombach

11
1

Thank you for your answer. Unfortunatly, I already have index on concerned tables – Vincenzo Jan 18 '13 at 11:00

score 1 · Answer 2 · answered Jan 18 '13 at 10:17

1

First advice I should give is to take a look at the execution plan and search for bottlenecks

then, why a LEFT OUTER JOIN there ?

LEFT OUTER JOIN [dbo].[Serie] AS [Extent4]

Shouldn't it be an inner join (if so, might be some issue with your mapping) ? I guess it might screw up query optimization by preventing the where clause to be performed early in the join process.

Maybe you can try running the query replacing the left outer join with an inner join and see if it makes a difference. Also, agree with @user1802430 , if there is no index on userepivu_date, you sure need to place one.

answered Jan 18 '13 at 10:17

jbl

15,179
3
34
101

Thank you very much for your answer. I have try to user INNER JOIN instead of LEFT OUTER JOIN and the performance is really great : 300 ms. Now my question is : How to do this with linq ? Because this SQL Query is generated by Linq – Vincenzo Jan 18 '13 at 10:59
@toregua You'll have to code the joins manually. Apparently `Saison.Serie` is not a required field so EF creates an outer join. – Gert Arnold Jan 18 '13 at 12:19
@toregua Not familiar with EF : you might have to change your model or code mapping, to have a foreign key between Saison and Serie making it a 1,* relationship instead of 0,*. or adding a Required attribute, as suggested by GertArnold Bon courage – jbl Jan 18 '13 at 12:33
I already have a 1, * relationship between Saison and Serie.The foreign key between Saison and Serie exists too :( – Vincenzo Jan 18 '13 at 12:55
Seems to be a Entity framework bug ? http://stackoverflow.com/questions/7484249/entity-framework-generating-left-outer-joins-when-it-should-be-doing-inner-joins or again http://stackoverflow.com/questions/2916830/too-many-left-outer-joins-in-entity-framework-4 – Vincenzo Jan 18 '13 at 13:26
@toregua you might give a try to this comment : http://stackoverflow.com/questions/2916830/too-many-left-outer-joins-in-entity-framework-4#comment2976865_2916830 eager loading your entities (with .Include(...)?) might be of some help. Which Ef Version do you use ? – jbl Jan 18 '13 at 14:34
@jbl I've just move from EF4 to EF5 but the generated query is always the same with "LEFT OUTER JOIN" ... I am going to try eager loading thanks – Vincenzo Jan 18 '13 at 14:46
@jbl Nothing change with eager loading : _query.Include("Episode.Saison.Serie") ... The generated query is the same with "LEFT OUTER JOIN"... :( I've migrated to EF 5 because some topics said that recent version correct this "bug" but without success (query generated is always and always the same) – Vincenzo Jan 18 '13 at 14:59

score 1 · Answer 3 · answered Jan 18 '13 at 17:47

I've found a solution. I have forced inner join using explicit join :

public List<SerieVu> ListSeriesLesPlusVuesSemaine()
{
    DateTime dateIlYaSeptJours = DateTime.Now.AddDays(-7);

    ObjectSet<UtilisateurEpisodeVu> UtilisateurEpisodeVuSet = _context.CreateObjectSet<UtilisateurEpisodeVu>();
    ObjectSet<Episode> EpisodeSet = _context.CreateObjectSet<Episode>();
    ObjectSet<Saison> SaisonSet = _context.CreateObjectSet<Saison>();
    ObjectSet<Serie> SerieSet = _context.CreateObjectSet<Serie>();

    var resultat =
    from uev in UtilisateurEpisodeVuSet.Include("Episode.Saison.Serie").Where(uev => uev.userepivu_date > dateIlYaSeptJours)
    join e in EpisodeSet on uev.episode_id equals e.episode_id
    join sai in SaisonSet on e.saison_id equals sai.saison_id
    join s in SerieSet on sai.serie_id equals s.serie_id
    group uev by s into pgroup
    let count = pgroup.Count()
    orderby count descending
    select new SerieVu() { nombreDeVus = count, Serie = pgroup.Key };

    return resultat.Take(7).ToList();
}

Using this method the linq query generate only inner join ;) Thank everyone who tried to help me

thx for the follow-up. If you can, you should accept your own answer. Also was wondering if, provided you have the reverse relationships, you could start the other way round : from se in Series from sa in se.saisons ... where uev.userepivu_date > dateIlYaSeptJours group by uev by se into pgroup ... — jbl, Jan 21 '13 at 10:00

Performance linq group by with count

3 Answers3