查询很慢,我该如何改进
本文关键字:何改进 查询 | 更新日期: 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; }
}
有几个类继承自它,例如ProfileViewNotification
和NewMessageNotification
:
public class ProfileViewNotification: Notification
{
public virtual ApplicationUser Viewer { get; set; }
}
public class NewMessageNotification: Notification
{
public virtual Message Message { get; set; }
}
我有以下方法来查询我的数据库中的所有Notification
,其中给定的ApplicationUser
是recipient
:
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
。正是因为这样,我才将性能提高了一个数量级。我还使查询异步化,这进一步提高了性能。