Introduction to the Entity Framework (EF) = & gt; 6. Performance Considerations

This is really complicated. I looked at Microsoft's documents and some of them could not be understood at all, but they would not be used by cainiao like me.

I can't help it. I 've put a few links in front. Let's try one by one.

1. explicitly open the connection

I have tested this. Sometimes, we need to show that the connection is opened and sometimes not used.

1. SaveChanges ()

Not wrong, hey.

This requires no attention to the connection issue, because no matter whether you modify, delete, or add a connection, there is only one SaveChanges (), it must be a single link, and the system will automatically use transactions.

2. Query

Add, delete, and modify a SaveChanges (). You can query it.

It is not uncommon to query multiple data items at the same time. For example, in the EasyUI DataGrid, data is used and the total number of rows is required. In this case, two queries are required, although you can write two queries, the actual situation is:

Open connection

Query

Close connection

Open connection

Query

Close connection

It is unclear about the specific performance impact. Although a connection is in the connection pool, it should be set up once, which consumes some performance.

In this case, the connection is opened:

If you use using or try, do you need to disable the connection after all? Then:

. Open ()

Query

Query

. Close () or. Dis... ()

Ii. Delayed Loading

Do not use delayed loading. Read the required data at a time. Especially when delayed loading is used in a loop, the database is read every time, which has a great impact on the performance.

Use. Include for foreign key relationships, and use. Join for none.

Iii. Try to use. Select

I remember that when I was studying the database, the teacher always said, "select what fields are used?" I think the same should be true here. I only need to query the required fields, but there are few query fields, it should be faster when the database executes the query, and the same should be true in EF. There should be fewer items to be assigned with less fields, and it should be faster.

As for select to the new entity class or var, we should look at the degree of reuse and the degree of correlation with other functions (such as interface generation). If it is used only once, var is very good.

Of course, if you need to pass, you cannot use var. You must use dynamic.

4. Application Server query or Database Server Query

Straightforward, that is, first. toList () or back. toList (), so here is just an example, just want to explain. toList () is used to query the database, which is not required in some cases. toList ().

In my opinion, the query should still be performed on the database server, which makes it better to standardize the actions of developers. Because some data volumes are large, you cannot query the select/where operations on the application server, this may cause worse performance. If your application server is idle, can you move it to one of the databases. Of course, there are some old applications. If there is a shelf, it depends on the situation.

Reference others' articles to illustrate this problem:

Http://www.cnblogs.com/jake1/archive/2013/04/25/3043664.html

QueryToList (). Skip (pageInfo. PageIndex-1) * pageInfo. PageSize). Take (pageInfo. PageSize );

Like the above statement, he will first find the data from the database before paging.

The correct statement should be as follows:

Query. Skip (pageInfo. PageIndex-1) * pageInfo. PageSize). Take (pageInfo. PageSize). ToList.

The above example is very typical. In any case, all pages should be stored in the database. You read a lot of data into the memory of the application server and then retrieve 10 records.

5. Use stored procedures for complex queries

If you have joined multiple tables, it is best to use the stored procedure. Although EF can also be implemented, the SQL generated by EF is probably far from the expectation.

6. Primary Foreign keys

If you can use the primary foreign key, try to use the primary foreign key as much as possible. In this way, both EF and database performance will be improved. Of course, it is too complicated to use stored procedures.

VII. Pre-generated View

This operation is still very troublesome. Of course, it will only affect the performance during the first access, but it is not necessary for a few visits. After all, after you release it, it is possible that you will access it for the first time.

8. NoTracking

Var linq = (from data1 in GetUser (). where (I => I. userID <= 50) select data1.UserID ). toList (); var linq2 = GetUser_StudyRecord (). where (I => linq. contains (I. userID. value )). toList ();

SQL statement

Correct

Var linq = (from data1 in GetUser (). where (I => I. userID <= 50) select data1.UserID); // IQueryable <int>, a query plan var linq2 = GetUser_StudyRecord (). where (I => linq. contains (I. userID. value )). toList ();

SQL statement

 

For my scattered repairs, I did not find more practical ones. The passing boss saw an error and gave me some advice.