Exploring the Performance of the ADO.NET Entity Framework – Part 2
Query Performance
During this post I’ll show a few patterns on how to improve the query performance.
A major design element for performance is the query cache. Once a query is executed, parts of the query are maintained in a global cache. Because of query and metadata caching, the second run always completes faster than the first run. For example, consider a query using Entity SQL query (don’t worry, we’ll discuss LINQ in just a second) with the following code.
|
|
On the first run, the query completes in 179 milliseconds. The next time, the query completes in 15 milliseconds. The difference between the two is the cost of building the command tree that gets passed down to the provider for execution. All subsequent queries complete in or around the same time of 15 milliseconds.
LINQ Queries
LINQ queries utilize some of the same logic as Entity SQL queries, except that not all parts of the query are cached and some parts are rebuilt each time the query is executed. Take a look at the query below.
|
|
Executing this query takes 202 milliseconds on the first execution and only 18 milliseconds on subsequent executions, which is still slower than using Entity SQL. Now, let’s take a look at using compiled LINQ queries to improve performance further. The advantage of compiling a LINQ query is that the expression tree is built when the query is compiled and doesn’t need to be rebuilt on subsequent executions.
Here’s the code for a compiled LINQ query that uses a PerformanceArticleContext delegate.
|
|
The times for this compiled LINQ query are 305 milliseconds on the first execution and 15 milliseconds on subsequent executions.
Here’s the standard LINQ.
|
|
Now here is the compiled LINQ query.
|
|
In this query the result set is only 33 items. For the non-compiled query, the execution time is 207 milliseconds on the first execution and 17 milliseconds on subsequent executions. By comparison, the result for the compiled query is 268 milliseconds on the first execution and only 3 milliseconds on subsequent executions.
No Tracking/Tracking
|
|
|
|
|
Based on these numbers, the NoTracking option provides a big reduction in the amount of time, where most of this gain comes when we stop tracking changes and managing relationships. For a NoTracking query, the compiled LINQ query outperforms the standard LINQ query both in first execution and in subsequent executions. Note that the second execution of the compiled LINQ query is equivalent to the second execution of the Entity SQL query.
Below are the single parameter filtered queries for both tracking and no tracking.
|
|
|
|
|
Summary
When optimizing query performance in the Entity Framework, you should consider what works best for your particular programming scenario. Here are a few key takeaways:
Program Manager, ADO.NET
Comments
Anonymous
February 11, 2008
Do you have any comparison between doing LINQ like 'from x in y select x;' vs. 'x.Select()' More complex queries would be more useful but I am interested in which type is actually fasterAnonymous
February 11, 2008
I assume you took into consideration the database buffer cache?Anonymous
February 11, 2008
I'm glad to finally see some EF performance information. It would be helpful to see a comparison against the similar process with a DataReader, DataSet, LINQ to SQL and other OR Mappers to see just what the performance trade-offs are. In this regard, I point to Rico Mariani's series on performance with LINQ to SQL as an example. In the case of the examples you've presented so far, the queries are identical to the kind of 1-1 table mapping that is possible with LINQ to SQL. It would be nice to also see some performance on more complex object mapping schemes (multiple tables to single object for example).Anonymous
February 11, 2008
Is there any way that we can reduce the time when the first query is run? I thought that using compiled queries would reduce that time. Thank you.Anonymous
February 12, 2008
ADO.NET Entity FrameWork PerformanceAnonymous
February 12, 2008
Brian Thank you so much for going into this detail. I look forward to more.Anonymous
February 13, 2008
J'ai quelque peu délaissé mon blog ces derniers temps mais maintenant que les techdays sont passés (duAnonymous
February 13, 2008
En este post comento una serie buenas prácticas para mejorar la eficiencia de ADO.NET Entity FrameworkAnonymous
February 15, 2008
What is the ability to persist this global cache? Given that the abstraction layer probably doesn't change a heck of a lot, there's no need to rebuild it unless it is invalidated. I'd hate to put the user through unneeded work if its not quick.Anonymous
February 18, 2008
I'm very curious about best-practices for an asp.net environment. Obviously, keeping around the compiled query would be great for the performance benefits of subsequent queries, yet in a web scenario, I'm generally creating a context, executing a query, and then destroying both. Is there some advice out there about how to make this more efficient so that I don't pay the 200ms penalty each time?Anonymous
February 18, 2008
In the stats above it appears that there is a decrease in performance for the "No Tracking" in several scenarios for the first run. For example, in the LINQ no parameters scenario the Tracking time is 202, whereas the No Tracking time is 282. Is it true that No Tracking is actually slower in some cases, and if so why? Many ThanksAnonymous
February 20, 2008
概述春节后的第一期推荐系列文章，共有10篇文章：1.ASP.NETMVCExampleApplicationoverNorthwindwiththeEntityFramework...Anonymous
February 20, 2008
Como hace unos días apuntaba Unai , en el blog del equipo de desarrollo de ADO.NET están publicando unaAnonymous
February 20, 2008
概述 春节后的第一期推荐系列文章，共有10篇文章： 1.ASP.NETMVCExampleApplicationoverNorthwindwiththeEntityFr...Anonymous
February 21, 2008
The first post, Exploring the Performance of the ADO.NET Entity Framework - Part 1 , began: PerformanceAnonymous
March 10, 2008
Do you have any performance statistics relating to using SQL Server stored procedures?Anonymous
March 12, 2008
I love all the amazing work that has been done with the ADO.NET Entity Framework and Data Services. There is one piece that I can't find any information on, however: Support for multi domain models or multiple databases in the Entity Framework (and thus Data Services). We have 10+ large enterprise databases all hosted on the same server/instance of sql server and these databases are somewhat used as "schema" conatiners, i.e. we have many stored procedures that make calls to stored procedures/tables/views in another database and there are logical relationships between tables in different databases. This requires our Entity Model to have entity relationships across databases and thus across Entity Models (since, AFAIK, currently the Entity Framework design surface can only include tables from one database). I believe that this is a big weakness and needs to be addressed as soon as possible. The other issue I have is how much the Entity Framework design surface slows down (and the entity boxes become unmanageble from a visual real-estate perspective) after about 20-30 tables on it... Any comments on this from the Entity Framework team would be very much appreciated...Anonymous
March 17, 2008
The comment has been removedAnonymous
March 19, 2008
Thanks for all the comments and questions! It's great to see the interest in the Entity Framework. I'm working on another post about performance that I'll try to post soon. It addresses some of the questions. -BrianAnonymous
March 19, 2008
Is there any news on the Release Date of Entity Framework?Anonymous
March 27, 2008
There have been a few questions from the last performance blog post about how the Entity Framework comparesAnonymous
March 27, 2008
L'ADO .Net Team vient de poster deux nouveaux posts : le premier concerne l'utilisation des procéduresAnonymous
May 24, 2008
For everybody who is interested on internals of Entity Framework (ER) in relation to referential integrityAnonymous
June 01, 2008
Query Performance During this post I’ll show a few patterns on how to improve the query performance. A major design element for performance is the query cache. Once a query is executed, parts of the query are maintained in a global cache. Because of querAnonymous
June 05, 2008
Query Performance During this post I’ll show a few patterns on how to improve the query performance. A major design element for performance is the query cache. Once a query is executed, parts of the query are maintained in a global cache. Because of querAnonymous
July 16, 2008
The comment has been removedAnonymous
August 20, 2008
Do you have comparsion data for EF with stored procedures and standalone stored procedures? Does it have some perfomance difference?Anonymous
January 11, 2009
If we’ll skip exact details, we can say, that internal behavior of whole modeling and mapping is basedAnonymous
May 24, 2011
Great article!Anonymous
November 14, 2011
I am using MergeOption.NoTracking. But i am unable to access navigation properties whenever my linq query ends up with AsEnumerable(). But if i make it as ToList() it works fine. Using AsEnumerable is must for me and I wanna use MergeOption.NoTracking also. Do you people have any idea about this. Thanks in advance.