咨询区
Moutia AYED:
我的项目需要构建几个图表,产生图表的 数据源
相对比较复杂,参考如下代码:
var resultdb = await _dbContext.TimeSheetElements
.Where(t => t.UserId == userId &&
t.Date.Month == month &&
t.Date.Year == year)
.GroupBy(t => t.Date)
.Select(pc =>
new ShowPointingChartViewModel
{
Day = pc.First().Date.DayOfWeek.ToString(),
Date = pc.First().Date.ToString(),
NormalHours = pc.Where(p => p.IsGuard == false && !taskType.Contains(p.UserTask.Type)).Sum(p => p.Duration),
OutOfBuisnessHours = pc.Where(p => p.IsGuard == true).Sum(p => p.Duration),
Holidays = pc.Where(p => p.UserTask.Type == "Holiday").Sum(p => p.Duration),
PublicHolidays = pc.Where(p => p.UserTask.Type == "Public holiday").Sum(p => p.Duration),
Illness = pc.Where(p => p.UserTask.Type == "Illness").Sum(p => p.Duration),
GuardFees = pc.Count(p => p.UserTask.Type == "Night Fees"),
Total = pc.Where(p=>p.UserTask.Type != "Night Fees").Sum(p => p.Duration)
})
.ToList();
实际跑起来后,我发现这个 EF 查询语句需要耗费相当长的时间,虽然结果是正确的,但我是一个性能追求者,我想怎么尽可能的对它进行优化,缩短 response 的时间 ?
回答区
Eriawan Kusumawardhono:
从你的代码看:这个 query 非常复杂,这么重的 query 查询性能必然会低效。
我给出的建议是:你可以先根据一些 where 条件从数据库中预先提取数据,提取完之后,接下来在内存中对这些业务逻辑进行复杂的运算,比如各种 Where,Count 然后再映射到 ShowPointingChartViewModel,参考如下代码:
var resultList = await _dbContext.TimeSheetElements
.Where(t => t.UserId == userId && t.Date.Month == month && t.Date.Year == year)
.GroupBy(t => t.Date).ToList();
var chartViewModel = resultList.Select(pc =>
new ShowPointingChartViewModel
{
Day = pc.First().Date.DayOfWeek.ToString(),
Date = pc.First().Date.ToString(),
NormalHours = pc.Where(p => p.IsGuard == false && !taskType.Contains(p.UserTask.Type)).Sum(p => p.Duration),
OutOfBuisnessHours = pc.Where(p => p.IsGuard == true).Sum(p => p.Duration),
Holidays = pc.Where(p => p.UserTask.Type == "Holiday").Sum(p => p.Duration),
PublicHolidays = pc.Where(p => p.UserTask.Type == "Public holiday").Sum(p => p.Duration),
Illness = pc.Where(p => p.UserTask.Type == "Illness").Sum(p => p.Duration),
GuardFees = pc.Count(p => p.UserTask.Type == "Night Fees"),
Total = pc.Where(p=>p.UserTask.Type != "Night Fees").Sum(p => p.Duration)
})
.ToList();
点评区
说实话,这个问题的优化建议还是值得学习一下的,我个人建议用 SQL Server Profile 或者 Entity Framework 自定的监控方法看看 sql 长啥样,有了这个原始sql,我们就可以进行针对性的优化。