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