查询速度非常慢，我该如何改进它？

Question

查询速度非常慢，我该如何改进它？

3

我有以下的抽象类：

public abstract class Notification
{
    public Notification()
    {
        this.receivedDate = DateTime.Now.ToUniversalTime();
    }

    public int Id { get; set; }

    public DateTime receivedDate { get; set; }

    public bool unread { get; set; }

    public virtual ApplicationUser recipient { get; set; }
}

有几个类继承它，例如ProfileViewNotification和NewMessageNotification：

public class ProfileViewNotification: Notification
{
    public virtual ApplicationUser Viewer { get; set; }
}

public class NewMessageNotification: Notification
{
    public virtual Message Message { get; set; }
}

我有以下方法来查询我的数据库，获取所有以给定的 ApplicationUser 为 recipient 的 Notification ：

public static List<NotificationApiViewModel> GetNotificationsForUser(string idOfUser)
    {
        List<NotificationApiViewModel> resultAsApiViewModel = new List<NotificationApiViewModel>();

        List<ProfileViewNotification> ofProfileViewNotificationType = null;
        List<NewMessageNotification> ofNewMessageNotificationType = null;

        try
        {
            using (var context = new ApplicationDbContext())
            {
                var query = context.Notifications.Where(c => c.recipient.Id == idOfUser);

                ofNewMessageNotificationType = query.OfType<NewMessageNotification>().Any() ? 
                    query.OfType<NewMessageNotification>()
                    .Include(n => n.recipient)
                    .Include(n => n.recipient.MyProfile)
                    .Include(n => n.recipient.MyProfile.ProfileImages)
                    .Include(n => n.Message)
                    .Include(n => n.Message.Author)
                    .Include(n => n.Message.Author.MyProfile)
                    .Include(n => n.Message.Author.MyProfile.ProfileImages)
                    .Include(n => n.Message.Recipient)
                    .Include(n => n.Message.Recipient.MyProfile)
                    .Include(n => n.Message.Recipient.MyProfile.ProfileImages)
                    .ToList() 
                    : null;

                ofProfileViewNotificationType = query.OfType<ProfileViewNotification>().Any() ? 
                    query.OfType<ProfileViewNotification>()
                    .Include(n => n.recipient)
                    .Include(n => n.recipient.MyProfile)
                    .Include(n => n.recipient.MyProfile.ProfileImages)
                    .Include(n => n.Viewer)
                    .Include(n => n.Viewer.MyProfile)
                    .Include(n => n.Viewer.MyProfile.ProfileImages)
                    .ToList() 
                    : null;

                }

        }
        catch (Exception ex)
        {
            //Log issue
        }

        if (ofNewMessageNotificationType != null)
        {
            foreach (var n in ofNewMessageNotificationType)
            {
                resultAsApiViewModel.Add(NotificationApiViewModel.ConvertToApiViewModel(n));
            }
        }

        if (ofProfileViewNotificationType != null)
        {
            foreach (var n in ofProfileViewNotificationType)
            {
                resultAsApiViewModel.Add(NotificationApiViewModel.ConvertToApiViewModel(n));
            }
        }

        return resultAsApiViewModel;
    }

重要提示，我的所有ConvertToApiViewModel方法都不会查询数据库，这就是为什么在原始查询中我使用了这些Include。另外，上面的内容仅包括两种通知类型，出于简洁考虑，但实际上我有十几种通知。

我的问题是我的方法非常慢。对于一个只有20个通知的用户，它需要超过一分钟的时间才能完成!

有人能告诉我我做错了什么吗？

- aBertrand

我猜你应该查看生成的SQL查询并对其进行分析，以查看是否缺少一些索引。 - Kinetic

对于像这样复杂的查询（包含许多包含项），我建议您使用LINQ，因为您可以轻松选择所需字段...您在这里做的实质上是从数据库中获取所有内容，这远非最佳选择。 - Rosdi Kasim

问题在于嵌套包含的数量太多，在SQL查询中生成了太多的“连接”。我认为没有EF解决方法，所以你应该使用SQL查询。请参考https://dev59.com/23I95IYBdhLWcg3w7SpZ - Taher Rahgooy

如何从EF6获取生成的查询：https://dev59.com/P3M_5IYBdhLWcg3wWRyV - Kinetic

就SQL优化和查询而言，它实际上取决于您使用的数据库类型。 - Kinetic

显示剩余2条评论

2个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- aBertrand · Answer 1

感谢大家的评论和回复。为了记录，我加快查询速度的方法是使用query.Select(n => new DTO{})和数据传输对象（DTO），而不是我的多个Include。仅凭这一点，我就将性能提高了一个数量级。我还将查询异步化，进一步提高了性能。

- Shai Aharoni · Answer 2

你正在为每个查询都调用数据库，而你可以将它们合并为一个单一的查询，像这样：

  try
            {
                using (var context = new ApplicationDbContext())
                {
                    //Here you execute the single query
                    var query = context.Notifications.Where(c => c.recipient.Id == idOfUser)
 .Include(n => n.recipient)
                        .Include(n => n.recipient.MyProfile)
                        .Include(n => n.recipient.MyProfile.ProfileImages)
                        .Include(n => n.Message)
                        .Include(n => n.Message.Author)
                        .Include(n => n.Message.Author.MyProfile)
                        .Include(n => n.Message.Author.MyProfile.ProfileImages)
                        .Include(n => n.Message.Recipient)
                        .Include(n => n.Message.Recipient.MyProfile)
                        .Include(n => n.Message.Recipient.MyProfile.ProfileImages)
.Include(n => n.Viewer)
                        .Include(n => n.Viewer.MyProfile)
                        .Include(n => n.Viewer.MyProfile.ProfileImages)
.ToList(); 

                    ofNewMessageNotificationType = query.OfType<NewMessageNotification>().Any() ? 
                        query.OfType<NewMessageNotification>(): null;

                    ofProfileViewNotificationType = query.OfType<ProfileViewNotification>().Any() ? 
                        query.OfType<ProfileViewNotification>() : null;

                    }

            }
            catch (Exception ex)
            {
                //Log issue
            }

            if (ofNewMessageNotificationType != null)
            {
                foreach (var n in ofNewMessageNotificationType)
                {
                    resultAsApiViewModel.Add(NotificationApiViewModel.ConvertToApiViewModel(n));
                }
            }

            if (ofProfileViewNotificationType != null)
            {
                foreach (var n in ofProfileViewNotificationType)
                {
                    resultAsApiViewModel.Add(NotificationApiViewModel.ConvertToApiViewModel(n));
                }
            }

            return resultAsApiViewModel;
        }

希望这能有所帮助...