"Mvc+mysql+entityframework" Query performance tuning notes

By setting the relationship between tables in DbContext , the query is passed through item in View . Projectoverhour to display the associated table data.

            Modelbuilder.entity<projectoverhour>()                = e.dailyreports)                and E. Projectoverhour)                new  {e.projectindex, E.employeeid, e.reportdate})                . Willcascadeondelete (false);

After the development of the discovery page only show 10 records time is more than 1s.

The following reasons were found:

1. In entityframework, the associated table data is not loaded at the time of query, but is invoked when it is used (that is, the item is called in View ) . Projectoverhour ) will not go to DB query.

After changing the screen display bar number to 20, the page load time is increased in a positive proportion.

Consider not allowing EntityFramework to execute multiple queries, using the Select method to query the required associated data at a time.

New Dailyreportsearchresultviewmodel ()                {                   ...                    = D.projectoverhour.overhour,                   ...                });

In this way, the resulting SQL is associated with the required tables, and only one query is executed.

Because a foreign key relationship is defined in DbContext, the resulting SQL text is a innerjoin relationship, not a leftjoin relationship that you want to set.

The survey did not discover how to define a left join relationship in DbContext. (There are withoptional methods, but always error when executing)

Later, the following wording was used to achieve the effect of the left JOIN, the query results are also correct.

 fromDinchDailyreportsqueryjoin ot1inchdb. Projectoverhours onNew{Projectindex = D.projectindex, EmployeeId = D.employeeid, reportdate =d.reportdate} equalsNew{Projectindex = ot1. Projectindex, EmployeeId = ot1. EmployeeId, reportdate =ot1. Workdate} into Ottemp fromOtinchottemp. DefaultIfEmpty ()Select Newdailyreportsearchresultviewmodel{... Approvaloverhour=ot. Approvaloverhour, ...};

However, the page load time is still very long (around 4s).

Later found that the correlation table in the two view algorithm changed to template, the data query a lot faster.

This feeling is very unreasonable, query should meger query faster.

When using the generated SQL test, it was found that the creation index took up 99% of the time.

The original business key of the view is not the primary key in the original table.

After appending the original table to the corresponding index, the query is much faster.

The associated tables are indexed one by one.

After the above changes, the data page load page is very fast (around 0.1s).

However, when the 2w data is appended to the test, it is found that the page load is slow again.

It is also a problem with the SQL text generated by entityframework.

The resulting SQL becomes the following form, because it is sorted by order by, and the paging is used.

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 ' ... 'DESCLIMIT0,Ten

Query results are queried as subqueries, and then sorted and paged.

The query takes 1.1s, where sending data accounts for 99% of the time.

Still more than the initial page load time.

Finally, instead of innerjoin the table with the select method to remove the relevant data, leftjoin the table data, by looping the select result set, the data is obtained separately.

After the modified development environment page load time is 0.2~0.3s, the server environment is 0.4~0.5s.

Although not ideal, performance is acceptable for small systems within the community.

2, MySQL DB Server is placed on a virtual machine, performance has been bad, and there are other systems in use.

Because you want to use data from another database on the DB server, consider setting up the data on a new machine, and then setting up a remote connection table to fetch the data.

However, it is very inefficient to find all the data from the remote server and then query each query.

Finally, it had to be placed on the server.

CREATE TABLE IF  not EXISTS ' tablename ' (...    ) ENGINE=DEFAULT CHARSET=utf8connection='  Mysql://user:[email protected]:3306/schema/tablename';

If the DB can change the performance of the server, it should be able to bring a lot of improvement.

"Mvc+mysql+entityframework" Query performance tuning notes