查询很慢,我该如何改进

本文关键字:何改进 查询 | 更新日期: 2023-09-27 17:57:58

我有以下抽象类:

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; }
}

有几个类继承自它,例如ProfileViewNotificationNewMessageNotification:

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

我有以下方法来查询我的数据库中的所有Notification,其中给定的ApplicationUserrecipient:

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方法都没有查询DB,这就是为什么我在原始查询中有所有这些Include。此外,为了简洁起见,上面只包括2种类型的通知,但我总共有十几种。

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

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

查询很慢,我该如何改进

您正在为十几个查询中的每一个对DB进行调用,因此您可以将它们组合为一个单独的查询,如下所示:

  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;
        }

希望这能帮助。。。

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