EntityFramework Performance Optimization

Before talking about the performance optimization of EF, please allow me a few words of nonsense. Although the author has previously contacted EF, but the real sense of contact EF is this year's August bar! At that time there was a product in the company modular project needs to use ORM. There were two options at the time of 1. Ef,2.nhibernate. Honestly, both of the word-of-mouth are not good ... Finally I supported the next Microsoft EF, after all. NET development with your own will be a lot easier to put. Some of the blogs that I looked at at the beginning of the web would say that EF has bad performance! (PS: Today with the customer to talk about the project was sprayed again) but I still silly to keep optimistic attitude, performance is not good to try to make it better!!! Do not do how to know it! Below I have some optimization of the scheme listed (currently only summed up the query), write inaccurate places, but also hope that we can point out.

When it comes to EF performance optimizations you have to say a tool Miniprofiler, (but you can also use SQL Server directly Profiler) Miniprofiler is a performance analysis applet designed by the StackOverflow team for. Net. Here we can use the Miniprofiler embedded page to view the cycle of page processing and the period of execution of SQL statements and SQL statements .

You can use it with the following steps: (Environment. NET4.0 + EntityFramewrok4.4)

1. Download Miniprofiler and Miniprofiler.ef (MINIPROFILER.EF5 compatible EntityFramework5.0 versions) via NuGet

2. Modify the Global.asax file to add the following code

protected void Application_Start () {    //SQL Trace     StackExchange.Profiling.MiniProfilerEF.Initialize ();} protected void Application_BeginRequest () {    miniprofiler.start ();} protected void application_endrequest () {    miniprofiler.stop ();}

3. To use it in a page that needs to be debugged, you need to add a Miniprofiler reference to _layout.cshtml (I'm using ASP. NET MVC)

Reference @using Stackexchange.profiling//body@miniprofiler.renderincludes ()

If the following screen appears on the page to indicate that the Miniprofiler has been configured successfully, you can enter the formal optimization of the link!!!




To solve the problem I summed up the following several scenarios:

1. Use EntityFramework5 and above in place of EF4.0 and the following versions (use. Net4.0 can skip)
2. Disable lazy Loading

By default, lazy loading is supported, and if you want to disable it, you must explicitly declare that the best location is in the DbContext constructor.

 Public Mycontext () {     thisfalse

Because if we use lazy loading, we will have to access the data every time we need to access the property, so the overhead is huge.

3. Use greedy load (also known as pre-loading is the database of multi-table query)
This is actually the same as the above. A principle to minimize the number of accesses to a database,

Articles = DbContext.Articles.Include ("Category");

4. Try to ToList () or Traverse IQUERYABLE<T> when the SQL statement is pieced together, because once the iqueryable<t> is called, the database query
Error wording:

DbContext.Articles.ToList (). Where (u=>u.id==1);

Correct wording:

DbContext.Articles.Where (u=>u.id==1). ToList ();

The meaning of these words is to put the data together and then access the database. or else it would be. Retrieve all data from the database and then filter, if the database has tens of thousands of or even hundreds of thousands of data. That's going to be a killer!
Here is a filter + pagination (very useful)

 var  model = = u.createtime). Skip ((Pageinfo.pageindex-1 ) * pageinfo.pagesize). Take (pageinfo.pagesize).    Select (U = new  {u.id, U.name, U.version, U.taglist, U.userid, U.downloadtimes, u.createtime}). ToList ();  

5. Cache data
Try to load frequently used data into memory one at a time, adapt to some frequently call the database to calculate the function (as if with the 4th tune some conflict, but also reduce the number of database access is better. Concrete analysis of the specific problem)
I have a navigation menu in the project is the frequent access to the database resulting in poor performance (start with a Level 1 menu, and then through Level 1 to obtain a Level 2 menu, 2 levels to get 3 levels ....) ）
The solution is to load all the data into memory at once and then retrieve it through memory, and the time will drop by about 3s. Compared to this situation is actually more!


Said so much, summed up on the two points:
1. Guaranteed high performance for pieced together SQL statements
2. Reduce the number of database accesses

Maybe Daniel thinks it's all nonsense! But these methods are still quite suitable for the middle and lower class.
Insufficient to replenish later!

EntityFramework Performance Optimization