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.

using (PerformanceArticleContext ne = new PerformanceArticleContext()) {     ObjectQuery<Orders> orders      = ne.CreateQuery<Orders>(“Select value o from Orders as o”);     foreach (Orders o in orders)     {         int i = o.OrderID;     } }

 

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.

using (PerformanceArticleContext ne = new PerformanceArticleContext()) {     var orders = from order in ne.Orders                  select order;     foreach (Orders o in orders)     {         int i = o.OrderID;     } }

 

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.

public static Func<PerformanceArticleContext, IQueryable<Orders>>            compiledQuery = CompiledQuery.Compile(                                (PerformanceArticleContext ne) =>                                            (from o in ne.Orders                                             select o)                                );   using (PerformanceArticleContext ne = new PerformanceArticleContext()) {     foreach (Orders o in compiledQuery(ne))     {         int i = o.OrderID;     } }

 

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.

using (PerformanceArticleContext ne = new PerformanceArticleContext()) {     ne.Orders.MergeOption = MergeOption.NoTracking;     var orders = from order in ne.Orders                  where order.ShipCity == “London”                  select order;     foreach (Orders o in orders)     {         int i = o.OrderID;     } }

 

Now here is the compiled LINQ query.

public static Func<PerformanceArticleContext, IQueryable<Orders>>            compiledQuery = CompiledQuery.Compile(                                (PerformanceArticleContext ne) =>                                    (from o in ne.Orders                                     where o.ShipCity == “London”                                     select o)                                );   using (PerformanceArticleContext ne = new PerformanceArticleContext()) {     foreach (Orders o in compiledQuery(ne))     {         int i = o.OrderID;     } }

 

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

In the previous examples, all the queries result in the creation of an object that gets added to the ObjectStateManager so that we can track updates. When it is not important to track updates or deletes to objects, then executing queries using the NoTracking merge option may be a better option. For example, NoTracking may be a good option in an ASP.NET web application that queries for a specific category name but doesn’t make updates to the returned data. In a case like this, there is a performance benefit to using NoTracking queries. Let’s demonstrate this by taking the same queries that we used previously and making them all into NoTracking queries. The following table demonstrates the cost of tracking objects in the query examples:

 

Tracking v. NoTracking on full query without parameters

Query Type	Tracking First Run (milliseconds)	Tracking Second Run (milliseconds)	NoTracking First Run (milliseconds)	NoTracking Second Run (milliseconds)
Entity SQL	179	15	149	6
LINQ	202	18	282	8
Compiled LINQ	305	15	276	6

 

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.

Query Type	Tracking First Run (milliseconds)	Tracking Second Run (milliseconds)	NoTracking First Run (milliseconds)	NoTracking Second Run (milliseconds)
Entity SQL	126	3	140	2
LINQ	207	17	275	18
Compiled LINQ	268	3	277	2

 

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:

·         Initial creation of the ObjectContext includes the cost of loading and validating the metadata.

·         Initial execution of any query includes the costs of building up a query cache to enable faster execution of subsequent queries.

·         Compiled LINQ queries are faster than Non-compiled LINQ queries.

·         Queries executed with a NoTracking merge option work well for streaming large data objects or when changes and relationships do not need to be tracked.

 

Brian Dawson,

Program Manager, ADO.NET