Entityframework performance optimization, entityframework

Entityframework performance optimization, entityframework

Before talking about EF performance optimization, let me talk nonsense. Although I have been in contact with EF before, the actual contact with EF is in February this year! At that time, the company had a modular product project that needed to use ORM. At that time, two options were available: 1.EF, 2. nhib.pdf. To be honest, they both have poor reputation... at last, I still supported Microsoft's EF. After all, it will be much more convenient to do. Net development with your own. Some blog posts I found online at the beginning will say EF has poor performance! (PS: I was sprayed again when I talked to the customer about the project today.) But I am still stupid and remain optimistic. If the performance is poor, I will try to make it better !!! How can I know it! The following are some of my optimization solutions (only queries are summarized at present). I hope you can point out the inaccuracy in writing them.

Speaking of EF performance optimization, I have to say that MiniProfiler is a tool (but SQL Server profiler can be used directly). MiniProfiler is a small program for. net performance analysis designed by the StackOverFlow team. Here, we can use MiniProfiler to embed the page to view the page's processing cycle and SQL statement execution cycle and SQL statement.

You can use it by following these steps: (environment. NET4.0 + EntityFramewrok4.4)

1. Download MiniProfiler and MiniProfiler. EF (MiniProfiler. EF5 is compatible with versions earlier than EntityFramework5.0) through Nuget)

2. Modify the Global. asax file and 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 on the page to be debugged, add a reference to MiniProfiler in _ Layout. cshtml (ASP. net mvc is used here)

// Reference @ using StackExchange. Profiling // Body@MiniProfiler.RenderIncludes ()

If the following screen appears on the page, it indicates that MiniProfiler has been configured successfully. Now, you can enter the optimization stage !!!




I have summarized several solutions to solve the problem:

1. Use EntityFramework5 or a later version to replace EF4.0 and earlier versions (use. Net4.0 for skipping)
2. Disable delayed Loading

By default, delayed loading is supported. If you want to disable it, it must be explicitly declared. The best position is in the DbContext constructor.

public MyContext() {     this.Configuration.LazyLoadingEnabled = false; } 

If delayed loading is used, the overhead of accessing data is huge every time we need to access the attribute.

3. Use greedy loading (pre-loading is the multi-Table query of the database)
This actually responds to the same principle as above to minimize the number of database accesses,

articles = dbContext.Articles.Include("Category");

4. Try ToList () or traverse IQueryable <T> when SQL statements are pieced together, because once IQueryable <T> is called, it is equivalent to querying the database.
Incorrect syntax:

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

Correct syntax:

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

These statements mean to piece together the data and then access the database. Otherwise, the system filters all the data from the database, if the database has tens of thousands or even hundreds of thousands of data. That's all!
Here we provide a filtering + paging method (useful)

var model =dbContext.MyModules.OrderByDescending(u => 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 the memory at a time to adapt to some frequently called database computing functions (it seems that there are some conflicts with 4th calls, but it is better to reduce the number of database accesses. Specific issues are analyzed)
One navigation menu in my project is that frequent access to the database leads to poor performance (level 1 menu is obtained at the beginning, and level 2 menu is obtained through level 1 at the beginning, level 2 get level 3 ....)
The solution is to load all the data into the memory at a time, and then retrieve the data through the memory. The time is immediately reduced by about 3 seconds. This is actually quite a case!


To sum up, there are two points:
1. Ensure high performance of the assembled SQL statements
2. Reduce database access times

Maybe Daniel thinks this is all nonsense! However, these methods are quite suitable for the lower-and middle-class.
I will try again later ~!