Eager loading VS Lazy loading in a n+1 situation

Question

How to deal to have a good performance with complex data query like this :

In my Data access layer :

public IEnumerable<Serie> Search(SearchCriteria searchCriteria)
{
    //Operation to have my predicate...
    return ListAll().Where(predicate);
}

In my SerieFicheViewModel Adapter in order to adapt my entity to a ViewModel :

public static List<SerieFicheViewModel> ToViewModel(int utilisateurProfilId, IEnumerable<Serie> series, int utilisateurId, List<string> roles)
{
    IEnumerable<SerieFicheViewModel> seriesFiches =
                from s in series
                select new SerieFicheViewModel
                {
                    serie = SerieAdapter.ToViewModel(s, utilisateurId, SerieAdapter.TypeReponseEnum.Small),
                    serieUtilisateur = SerieUtilisateurAdapter.ToViewModel(s, utilisateurId, roles)
                };

    return seriesFiches.ToList();
}

My SerieAdapter :

public static SerieViewModel ToViewModel(Serie serie, int utilisateurId, TypeReponseEnum typeReponse)
{
    SaisonBusiness _saisonBusiness = (SaisonBusiness)UnityHelper.BusinessResolve<Saison>();
    EpisodeBusiness _episodeBusiness = (EpisodeBusiness)UnityHelper.BusinessResolve<Episode>();

    int nombreSaisonsValides = _saisonBusiness.GetNombreSaisonsValides(serie.serie_id);

    SerieViewModel SerieViewModel = new SerieViewModel
    {
        id = serie.serie_id,
        dateAjout = serie.serie_dateajout.ToString("dd/MM/yyyy HH:mm"),
        nomVf = serie.Prestation.prestation_nom,
        nomOriginal = serie.Prestation.prestation_nom2,
        nom = serie.Prestation.prestation_nom,
        imageThumbUrl = !string.IsNullOrEmpty(serie.Prestation.prestation_image_thumb) ? ConfigurationManager.AppSettings["AddictLive_BaseUrl"] + serie.Prestation.prestation_image_thumb.Replace("~", "").Substring(1) : string.Empty,
        imageUrl = !string.IsNullOrEmpty(serie.Prestation.prestation_image) ? ConfigurationManager.AppSettings["AddictLive_BaseUrl"] + serie.Prestation.prestation_image.Replace("~", "").Substring(1) : string.Empty,
        noteMoyenne = serie.Prestation.PrestationNotes.Count() > 0 ? serie.Prestation.PrestationNotes.Select(pn => pn.note).Average() : 0,
        synopsis = serie.Prestation.prestation_description,
        format = serie.serie_format,
        nombreDeNotes = serie.Prestation.PrestationNotes.Count(),
        remerciement = serie.UserAuth != null ? serie.UserAuth.DisplayName : string.Empty,
        statusProduction = serie.StatusProduction != null ? serie.StatusProduction.statusproduction_nom : string.Empty,
        duree = RenseignerDuree(serie),
        nombreSaisons = nombreSaisonsValides,
        nombreEpisodes = _episodeBusiness.GetNombreEpisodesValides(serie.serie_id),
        videoPrincipale = serie.serie_videoprincipale
    };

    SerieViewModel.ficheUrl = UrlTool.GetSerieFicheUrl(serie.serie_id, serie.Prestation.prestation_nom);

    PrestationNoteBusiness _prestationNoteBusiness = (PrestationNoteBusiness)UnityHelper.BusinessResolve<PrestationNote>();
    SerieViewModel.noteMoyenneEpisodes = _prestationNoteBusiness.GetMoyenneDeTousLesEpisodes(serie.serie_id);

    //Dispo tout le temps pour afficher un lien vers la premiere saison une fois qu'on a mis une série comme vue
    Saison saison = nombreSaisonsValides > 0 ? _saisonBusiness.GetPremiereSaisonValide(serie.serie_id) : null;
    if(saison != null)
        SerieViewModel.saisonUrl = UrlTool.GetSaisonUrl(saison.saison_id, saison.saison_numero.ToString(), serie.Prestation.prestation_nom);

    return SerieViewModel;
}

I do not put all the code to try not to pollute the request but you can see my adapters are complex and I don't know how tu customize them.

In the above example I use lazy loading and more than 215 queries are executed in my database

If I try using eager loading like recommanded in the Entity Framework Profiler software :

public IEnumerable<Serie> Search(SearchCriteria searchCriteria)
{
    //Operation to have my predicate...
    return ListAll().Include(s => s.Prestation.PrestationNotes)
    .Include(s => s.UtilisateurSerieEtats)
    .Include(s => s.UtilisateurSerieSuivies)
    .Include(s => s.Saisons.Select(sai => sai.Episodes))
    .Where(predicate);
}

Entity framework Profiler said this time there is too much joins... obviously my request is too big and too much data recovered but how to cut my code and improve performance ? I have this situation with almost all my adapters and I need your help.

Thank you in advance to all who take the time to read and help me

score 0 · Answer 1 · answered Nov 12 '13 at 19:32

0

One way to improve your performance would be to create a view (or several views). Your EF query could then query a much simpler structure and pull all the data back i one call.

Depending on the version of SQL Server you have available you could use Indexed Views, this can have a dramatic effect on performance.

answered Nov 12 '13 at 19:32

Shiraz Bhaiji

64,065
34
143
252

When you talk about Indexed View do you want to say transact sql view or an other kind of .net view ? I am using SQL Server 2008 R2 Standard Edition. Is it easy to map a tranct sql view from database to entity framework entities ? – Vincenzo Nov 12 '13 at 20:31
An Indexed view is the same as a transact sql view, except that it stores a physical copy of the data, so it is much faster when there is a complex query behind the view (on one project we reduced query time from 7 mins to 2 secs). But it is not included in standard edition. After creating your view, run the wizard to add the view to the model, then you can query it the same way as you query tables. – Shiraz Bhaiji Nov 12 '13 at 21:07

Eager loading VS Lazy loading in a n+1 situation

1 Answers1