【MVC+MySQL+EntityFramework】查询性能优化笔记

通过在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能换台性能好些的服务器，应该能带来不少的提升。

已添加到【MVC5】ASP.NET MVC 项目笔记汇总

巴特西

【MVC+MySQL+EntityFramework】查询性能优化笔记

最新文章

热门文章