通过在DbContext中定了表之间的关系,查询后在View中通过item.ProjectOverHour来显示关联表数据。
modelBuilder.Entity<ProjectOverHour>()
.HasMany(e => e.DailyReports)
.WithRequired(e => e.ProjectOverHour)
.HasForeignKey(e => new { e.ProjectIndex, e.EmployeeId, e.ReportDate })
.WillCascadeOnDelete(false);
开发完后发现页面仅仅显示10条记录耗时已经超过1s了。
调查后发现如下原因:
1、EntityFramework中,关联表数据不是在查询时加载的,而是在用到时(即View中调用item.ProjectOverHour时)才会去DB查询。
将画面显示条数改为20条后,页面加载时间成正比例上升。
考虑不让EntityFramework执行多次查询,使用Select方法使其一次查询出所需要的关联数据。
Select<DailyReport, DailyReportSearchResultViewModel>(d => new DailyReportSearchResultViewModel()
{
...
OverHour = d.ProjectOverHour.OverHour,
...
});
这样修改后生成的SQL中关联了需要的表,并且只执行了一次查询。
因为在DbContext中定义的是外键关系,导致生成的SQL文是InnerJoin的关系,而不是想定的LeftJoin关系。
调查后没有发现如何在DbContext中定义Left Join关系。(有WithOptional方法,但执行时总是出错)
后来改用如下写法,实现了Left Join的效果,查询结果也是正确的。
from d in dailyReportsQuery
join ot1 in db.ProjectOverHours
on new { ProjectIndex = d.ProjectIndex, EmployeeId = d.EmployeeId, ReportDate = d.ReportDate }
equals new { ProjectIndex = ot1.ProjectIndex, EmployeeId = ot1.EmployeeId, ReportDate = ot1.WorkDate } into ottemp
from ot in ottemp.DefaultIfEmpty()
select new DailyReportSearchResultViewModel
{
...
ApprovalOverHour = ot.ApprovalOverHour,
...
};
但是页面加载时间仍然很长(4s左右)。
后来发现把关联表中的两个View的算法改为Template后,数据查询快了很多。
这个感觉很没道理,查询应该Meger查询更快才对。
在用生成的SQL测试时,发现创建索引占据了99%的时间。
原来关联View的业务主键,在原来的表里并不是主键。
将原来的表里追加相应的索引后,查询快了很多。
逐个把关联的表都加了索引。
上述修改后,数据少时页面加载页很快(0.1s左右)。
但是在追加了2w条数据后再测试时,发现页面加载又变慢了。
还是EntityFramework生成的SQL文的问题。
因为使用OrderBy来排序,并且使用了分页,生成的SQL文变成了如下的形式。
SELECT
...
FROM (SELECT
...
FROM `dailyreport` AS `Extent1`
LEFT OUTER JOIN ...
INNER JOIN ...
WHERE (0 = `Extent1`.`DeleteFlag`) ... ) AS `Project1`
ORDER BY
`Project1`.`...` DESC,
`Project1`.`...` ASC,
`Project1`.`...` DESC
LIMIT 0,10
把查询结果作为子查询,然后再排序和分页。
查询用时1.1s,其中Sending data占了99%的时间。
仍然比最初的页面加载时间要多。
最后改为将InnerJoin的表用Select方法取出相关数据,LeftJoin的表的数据,通过循环Select结果集,单独取得这些数据。
修改后开发环境页面加载时间在0.2~0.3s,服务器环境在0.4~0.5s。
虽然不是很理想,但是对于社内用的小系统来说,性能已经可以接受了。
2、MySql的DB服务器是放在一台虚拟机上的,性能一直不好,并且已经有别的系统在用。
因为要用到该DB服务器上的另一个数据库的数据,所以考虑把本系统的数据建立在一台新机器上,然后建立远程连接表来取数据。
但发现每次查询都要从远程服务器取得所有的数据然后再查询,效率极差。
最后还是只好放在该服务器上了。
CREATE TABLE IF NOT EXISTS `tablename`` (
......
) ENGINE=FEDERATED DEFAULT CHARSET=utf8
CONNECTION='mysql://user:password@server:3306/schema/tablename';
如果DB能换台性能好些的服务器,应该能带来不少的提升。