Some performance issues to note when using the Entity Framework

Ever since I used EF, I've been concerned about potential performance issues every time. So every time I write a LINQ query, I use SQL Profiler to look at the actual generated SQL statements to uncover potential performance problems. It is also strongly recommended that you do so to avoid the future of the software is a problem difficult to check.

One, select only a column or some column

There are times when writing LINQ in C # while looking comfortable, but performance is not necessarily good, so it is necessary to make some adjustments. For example, this situation:

I need to know the number of clicks on an article, that's all, I might write:

Context. Post.firstordefault (p = p.id = = PostID). Hits;
Or:

Context. Post.find (PostID). Hits;
I expect them to just go to the database to filter the data in the Hits column, however, the SQL Profiler will find that the two statements actually give all the columns to select, and the operation to access hits is actually in memory.

Although the small table does not see the performance problem, but in case you have a column in your table is stored in the file byte stream (byte), then such operations may be slow, and consume additional network transmission, so can not ignore this problem.

In fact, I can avoid this problem with just a few tweaks, but the LINQ statement is ugly:

Context. Post.where (p = p.id = = PostID). Select (p = = p.hits). FirstOrDefault ();
The final native SQL generated by LINQ to SQL is this:

exec sp_executesql N ' SELECT TOP (1)
[Extent1]. [Hits] As [Hits]
from [dbo]. [Post] As [Extent1]
WHERE [Extent1]. [Id] = @p__linq__0 ', N ' @p__linq__0 uniqueidentifier ', @p__linq__0 = ' 850c3a86-6c3d-408b-8099-61eda559f804 '
The real only select hits a field.

Ii. Problems of ToList ()

In fact, many times the EF performance problems are related to the timing of query execution. Our usual intention is to create a query expression first, just build, not execute. The time to execute is when the result of the expression is used.

In the company code program, I saw a lot of colleagues with EF, write the query like directly call ToList () method. Sometimes this can cause a lot of performance problems. Because simply declaring a LINQ expression does not immediately execute the SQL query, but once the tolist () is added later, it is executed immediately. If you just want to select some of these data, rather than all of them, by the criteria, then the ToList () will be filtered out of memory, and it's not going to be executed by converting your condition into a SQL where statement.

var query = from ...//create a query, but do not execute
...
var result = query. ToList (); Execute Query Now
So, you should try to avoid finding the elements you want from the results of ToList ().

SAN, IQueryable, IEnumerable

On the choice of these two interfaces, I prefer to use IQueryable. Most of the time the two interfaces are consistent in their use, but if you are going to do an indeterminate query, meaning that the query expression is not a one-time determination, the result of which may be chosen by other classes to choose exactly what to select, it is necessary to use IQueryable.

For example, I have a data-tier approach:

Public ienumerable<ediblog.core.entities.post> Getallpost ()
{
Return to context. Post;
}
It is obvious that it will be called by other methods in the system, and these callers want to get different results. The usual action is to spell a where statement again:

var myresult = Postda.getallpost (). Where (...)
However, unfortunately, the conditions in the where statement are not converted to native SQL to execute, it is filtered in memory. This is a relatively cloudy performance issue. So at the beginning of the article I suggested that you use SQL Profiler to see how your LINQ is executed.

If you change the return type to IQueryable, then your where statement can be converted to SQL execution.

Public iqueryable<ediblog.core.entities.post> Getallpost ()
{
Return to context. Post;
}
About these two interfaces, in the Cock Wire forum StackOverflow has a better post, you can see for yourself:

Http://stackoverflow.com/questions/252785/what-is-the-difference-between-iqueryablet-and-ienumerablet

"Ienumerable:ienumerable is the best suitable for working with In-memory collection. IEnumerable doesn ' t move between items, it's forward only collection.

Iqueryable:iqueryable best suits for remote data source, like a database or Web service. IQueryable is a very powerful feature, enables a variety of interesting deferred execution scenarios (like paging and Composition based queries). "

There is also a more intuitive answer on the MSDN forum:

IQueryable returns a "queryable" that's a query you could still being enriched before really sending it to the server.

IEnumerable returns a list that's the actual querying took place and you get the results. ToList is isued to force running the query and returning these enumerable results ...

So in short:
-Use IQueryable If you want to return a base query, could be further enhanced before running it server side (by Enume Rating its items).
-Use Ienumerable/tolist if your want to return a list of that have been retrieved from the DB

Iv. number of calculations, count (), and Count

This is one of the most prone to pits and also a very serious performance problem. When we need to count the number of records that meet a certain condition, we want the SQL statement to be Select COUNT (*) ... In this form. However, the following seemingly natural wording can lead to unwanted results:

Context. Category.firstordefault (p = p.name = = CategoryName). Posts.count;
This is my blog used to count the number of articles in a classification of the statement, of course, because the discovery of performance problems, now is not the case. It produces SQL that is not a select COUNT, but is divided into 2 pieces. Here's what SQL Profiler catches:

exec sp_executesql N ' SELECT TOP (1)
[Extent1]. [ID] as [ID],
[Extent1]. [Name] As [Name],
[Extent1]. [DisplayName] As [DisplayName]
from [dbo]. [Category] As [Extent1]
WHERE [Extent1]. [Name] = @p__linq__0 ', N ' @p__linq__0 nvarchar (4000) ', @p__linq__0 =n ' ASPNET '

exec sp_executesql N ' SELECT
[Extent2]. [ID] as [ID],
[Extent2]. [Title] As [Title],
[Extent2]. [Slug] As [Slug],
[Extent2]. [PubDate] As [PubDate],
[Extent2]. [Postcontent] As [Postcontent],
[Extent2]. [Author] As [Author],
[Extent2]. [Commentenabled] As [commentenabled],
[Extent2]. [Ispublished] As [ispublished],
[Extent2]. [Hits] As [Hits],
[Extent2]. [Rators] As [Rators],
[Extent2]. [Rating] As [Rating],
[Extent2]. [Exposedtositemap] As [Exposedtositemap],
[Extent2]. [Displayfrom] As [Displayfrom],
[Extent2]. [Displaytill] As [Displaytill],
[Extent2]. [Lastmodifyon] As [Lastmodifyon],
[Extent2]. [Publishtorss] As [Publishtorss]
from [dbo]. [Postcategory] As [Extent1]
INNER JOIN [dbo]. [Post] As [Extent2] on [Extent1]. [PostID] = [Extent2]. [Id]
WHERE [Extent1]. [CategoryId] = @EntityKeyValue1 ', N ' @EntityKeyValue1 uniqueidentifier ', @EntityKeyValue1 = ' 3feb11a2-6e36-4dce-8c02-614bef7acc62 '
As you can see, EF does two things, the first thing is to find the category named "ASPNET", and then use the category ID to find all of its post, and finally do count is actually. NET in memory. This obviously gives the select the information we don't need. We just need a count, is it so complicated for Mao?

Review the first article I have said. Not hard to find. In FirstOrDefault (...) The properties that are then accessed are in memory. So, when we visited category.firstordefault (p = = P.name = = CategoryName), the first SQL statement was generated. Followed by ". Posts "is the navigation property of the Category object, and EF loads all of this category's post with lazy load, so a second SQL statement is generated. The count is then automatically carried out in memory immediately thereafter.

If you want your code to generate LINQ to SQL as much as possible, it's a simple rule to use LINQ, lambda expressions as much as possible so that EF can help us translate. There are two kinds of count in C #. Enumerable.count () is a method, List.count is a property. Once a thing becomes a list, you go to count, it must be in memory.

So, in EF, to do count, this should be the case:

Context. Post.count (p = p.categories.any (q = q.name = = CategoryName));
At this point, Count () accepts a lambda expression, and LINQ to SQL translates exactly to "select Count":

SELECT [GroupBy1]. [A1] As [C1]
From (
SELECT COUNT (1) as [A1]
from [dbo]. [Post] As [Extent1]
WHERE EXISTS (
SELECT 1 as [C1]
from [dbo]. [Postcategory] As [Extent2]
INNER JOIN [dbo]. [Category] As [Extent3]
On [Extent3]. [Id] = [Extent2]. [CategoryId]
WHERE ([extent1].[ ID] = [Extent2]. [PostID])
and ([extent3].[ Name] = ' ASPNET ')
)
) as [GroupBy1]
Now the performance is obviously much better ~

Some performance issues to note when using the Entity Framework