如何使用20多个Includes()提高EF查询性能 [英] How to improve EF query performance with 20+ Includes()

问题描述

我有一个Activity模型，可以从7种左右不同类型的模型中提取模型.这些模型也具有它们的关系，活动提要需要这些关系才能显示我想要的信息.这意味着我的查询中大约有20个包含项.我只做了8个月左右的时间，就读过有关编译查询，存储过程以及所有这些内容如何使我丧命的信息.我还读过我可以将代码优先模型更改为virtual，以便它可以进行延迟加载，但是我担心如果有大量用户，所有这些数据库调用都会杀死我的站点. /p>

首先是模型

public class Activity
{
    public int ActivityID { get; set; }
    public int ActivityTypeID { get; set; }
    public int ContributorID { get; set; }
    public int UserID { get; set; }
    public int? ProjectID { get; set; }
    public int? ProjectDocID { get; set; }
    public int? CommentID { get; set; }
    public int? BookID { get; set; }
    public int? BookReviewID { get; set; }
    public DateTime DateCreated { get; set; }
    public Comment Comment { get; set; }
    public ProjectDoc ProjectDoc { get; set; }
    public Project Project { get; set; }
    public Book Book { get; set; }
    public BookReview BookReview { get; set; }
    public ActivityType ActivityType { get; set; }
    [ForeignKey("ContributorID")]
    public User Contributor { get; set; }
    [ForeignKey("UserID")]
    public User User { get; set; }
    public ICollection<ActivityLike> ActivityLike { get; set; }
    public ICollection<ActivityComment> ActivityComment { get; set; }
}

现在查询

 var activity = db.Activities
                .Include(i => i.Contributor.BookStatus)
                .Include(i => i.ActivityType)
                .Include(i => i.ActivityLike.Select(y => y.User))
                .Include(i => i.ActivityComment.Select(y => y.User))
                .Include(i => i.Project.ProjectFollower)
                .Include(i => i.Project.View)
                .Include(i => i.Project.ProjectType)
                .Include(i => i.Project.User)
                .Include(i => i.Project.ProjectTag.Select(v => v.Tag))
                .Include(i => i.Project.ProjectCategory.Select(v => v.Category))
                .Include(i => i.Project.ProjectCharacteristic.Select(v => v.Characteristic))
                .Include(i => i.Project.ProjectDoc.Select(v => v.ProjectDocVote))
                .Include(i => i.Project.ProjectDoc.Select(v => v.User))
                .Include(i => i.Comment.User)
                .Include(i => i.Book.Author)
                .Include(i => i.Book.BookReview.Select(v => v.User))
                .Include(i => i.Book.BookReview.Select(v => v.BookReviewVote))
                .Include(i => i.Book.BookCharacteristic.Select(v => v.Characteristic))
                .Include(i => i.Contributor.Followers)
                .Where(u =>
                    u.Contributor.Followers.FirstOrDefault(x => x.FollowerID == WebSecurity.CurrentUserId) != null
                )
                .OrderByDescending(d => d.DateCreated)
                .Skip(offset)
                .Take(results)
                .ToList();

这最终就像是6600行SQL(或它的任何内容).

第一次运行需要10到16秒.而且因为我在jQuery中使用Skip()和无限滚动，所以每次单个ajax调用都需要10-12秒的时间.因此，如果我每次获得10个结果，而获得100个结果，那么等待时间超过100秒，这真是太糟糕了.现在，下一次用户在短时间内访问该页面的速度非常快.

那么，我应该如何改进此查询，您能否提供具体的解决方法，并解释解决方案，因为我在理解编译查询时遇到了困难，而在其他方面则很难.我之所以能够做到这一点，唯一的原因是因为EF使它变得容易...这显然是有代价的.

请原谅和误用了行话.

解决方案

我选择了延迟加载，这将加载时间减少了70％.我意识到由于数据库往返次数增加，这不是理想的选择，但是如果站点增长，那么我会担心的.这并不理想，但最初我更关心性能.所以总而言之，我做了以下

已将virtual添加到导航属性. 删除了超重查询中的几乎所有包含内容

结果是2.5秒的加载时间，而不是15秒(基于浏览器时间轴而不是sql profiler).

I have an Activity model that pulls in models from 7 or so different types of models. Those models have their relationships too, which the activity feed needs to be able to display the information I want. This means I have roughly 20 includes in my query. I've only been doing this for 8 months or so, and I've read about compiled queries, stored procedures, and how all those includes are probably killing me. I've also read that I could change my code first models to be virtual so that it can do lazy loading, but I'm concerned that all those database calls would kill my site if I got a large volume of users.

First the model

public class Activity
{
    public int ActivityID { get; set; }
    public int ActivityTypeID { get; set; }
    public int ContributorID { get; set; }
    public int UserID { get; set; }
    public int? ProjectID { get; set; }
    public int? ProjectDocID { get; set; }
    public int? CommentID { get; set; }
    public int? BookID { get; set; }
    public int? BookReviewID { get; set; }
    public DateTime DateCreated { get; set; }
    public Comment Comment { get; set; }
    public ProjectDoc ProjectDoc { get; set; }
    public Project Project { get; set; }
    public Book Book { get; set; }
    public BookReview BookReview { get; set; }
    public ActivityType ActivityType { get; set; }
    [ForeignKey("ContributorID")]
    public User Contributor { get; set; }
    [ForeignKey("UserID")]
    public User User { get; set; }
    public ICollection<ActivityLike> ActivityLike { get; set; }
    public ICollection<ActivityComment> ActivityComment { get; set; }
}

Now the query

 var activity = db.Activities
                .Include(i => i.Contributor.BookStatus)
                .Include(i => i.ActivityType)
                .Include(i => i.ActivityLike.Select(y => y.User))
                .Include(i => i.ActivityComment.Select(y => y.User))
                .Include(i => i.Project.ProjectFollower)
                .Include(i => i.Project.View)
                .Include(i => i.Project.ProjectType)
                .Include(i => i.Project.User)
                .Include(i => i.Project.ProjectTag.Select(v => v.Tag))
                .Include(i => i.Project.ProjectCategory.Select(v => v.Category))
                .Include(i => i.Project.ProjectCharacteristic.Select(v => v.Characteristic))
                .Include(i => i.Project.ProjectDoc.Select(v => v.ProjectDocVote))
                .Include(i => i.Project.ProjectDoc.Select(v => v.User))
                .Include(i => i.Comment.User)
                .Include(i => i.Book.Author)
                .Include(i => i.Book.BookReview.Select(v => v.User))
                .Include(i => i.Book.BookReview.Select(v => v.BookReviewVote))
                .Include(i => i.Book.BookCharacteristic.Select(v => v.Characteristic))
                .Include(i => i.Contributor.Followers)
                .Where(u =>
                    u.Contributor.Followers.FirstOrDefault(x => x.FollowerID == WebSecurity.CurrentUserId) != null
                )
                .OrderByDescending(d => d.DateCreated)
                .Skip(offset)
                .Take(results)
                .ToList();

This winds up being like 6600 lines of SQL (or whatever it is).

The first time this runs it takes 10-16 seconds. And because I am using Skip() and infinite scroll with jquery, every single ajax call takes 10-12 seconds the first time. So if I'm getting 10 results per go and there's 100 results then that's over 100 seconds of wait time, which is awful. Now the next time the user visits that page in a short time period it's very quick.

So how should I improve this query and can you provide specifically how to do so and explain the solution because I've had difficulty understanding compiled queries and what not. The only reason I've been able to get this far is because EF makes it easy... which obviously has a cost.

Please pardon and misused lingo.

解决方案

I chose to switch to lazy loading, which reduced load time by 70%. I realize this isn't ideal due more database round trips, but if the site grows then I'll worry about it then. Not ideal, but I'm more concerned about performance initially. So in summary I did the following

Added virtual to the navigation properties. Removed almost all the includes on the super weighty queries

Result was a 2.5 second load time instead of 15 second (based on browser timeline not sql profiler).

这篇关于如何使用20多个Includes()提高EF查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！