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

Answer 1

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

Answer 2

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.

Answer 3

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)