EF Query Tuning

  • Articolo

Continuing on the topic of application performance tuning, let’s turn our attention specifically to the EF and take a look at some of what’s going on under the covers with EF queries.

Question #1: Where is the hot path?

The first thing to think about in broad terms is where is the time really going.  Often it’s relatively easy to introduce a few counters or make some other small hack to find the worst offender in your application.  My first bit of advice to you on this front is: Don’t be afraid to make temporary changes to your code.   You can sometimes get great results by making a fresh enlistment in your source control system, checking out a bunch of files and hacking on the code in ways that you would never ship but which help to isolate things so you can figure out what’s really going on.

My next suggestion is: Get a profiler and figure out how to use it.   Visual studio has a great profiler if you have a sufficiently advanced version, and that’s what I use, but there are other products on the market.  There are even some profilers specifically aimed at working with applications built on top of the EF.  In any case, if you care about perf, a profiler is hugely important.  I’m no expert on any of these, but you can find some great resources on the web.  Some of the the articles I’ve found useful for working with the VS profiler are: msdn.microsoft.com/en-us/magazine/cc337887.aspx and <www.wintellect.com/CS/blogs/jrobbins/archive/2009/10/19/vs-2010-beta-2-sampling-and-instrumentation-profiling-in-depth-first-look.aspx>.

However you decide to measure things, the first level of investigation which I’d recommend when you are looking at an EF-based application is to determine where your biggest bottleneck is:

  • your application
  • EF one-time startup
  • EF query code
  • network transmission
  • or the database

If you can figure this out, then you can much more quickly home in on where to investigate further and what experiments you might want to try to improve performance.

What happens during the execution of an EF query?

In order to get to the next level of your investigation, it can help a lot to have a general idea of what all is happening and in what order when you execute an EF query.  To that end, here’s a rough list:

  • Startup: When you first create an ObjectContext, it will load metadata describing your model.  The first time a context is created in an app domain, that metadata is loaded either from resources in your assemblies or from files on disk.  On subsequent context creations the data is loaded from an in-memory cache making it much faster.
     
  • Startup: The first time you execute a query on the first ObjectContext instance for an app-domain, the EF will generate an in-memory structure (called the query views) which it uses for query translation.  For every query after that, it will simply retrieve the views from an in-memory cache.  This view generation step can be very time consuming—especially for large, complex models.
     
  • Query: Next the query is translated from LINQ or ESQL into your backend database command (TSQL in the case of SQL Server) and the results assembly plan is created.
     
  • Query: The connection is opened if it isn’t already.  By default the EF will open the connection before each operation and close it when the operation is complete.  If you open the connection manually, though, the EF will leave it alone so it can be kept open if you are performing multiple operations in a row, and this can sometimes be a significant perf win.
     
  • Query: If a TransactionScope is active, then the connection is enlisted in the transaction.  The effect of this depends on the ado.net provider—for sql server 2008 this is usually cheap, but for earlier versions of sql server if the connection has been previously opened and closed and was just opened again all within the same transaction (or for any provider if more than one transaction participant is involved), then the transaction is promoted to a distributed transaction which can be very expensive.
     
  • Network: Send query to the database.  Typically this part of the process is inexpensive because the amount of data required to send the query to the database is usually small.  Network time is more of a factor when results come back later.
     
  • Database: Execute the query.  What this entails obviously depends on the complexity of the query, the indexes that are configured and the particular database server involved among other things.  Most modern databases have their own fairly sophisticated query optimization system as well as execution plan caches and other mechanisms to help here.  In the case of sql server, the complexity of the query being executed is only one of the tools available for tuning, indexes and statistics can be added to the database and even plan guides can be used to give the database hints about how to execute the query efficiently.
     
  • Network: Return results from the database.  This can become a significant factor if your query returns extra data which isn’t actually needed by the app.  In addition, things like eager loading can help perf by reducing the number of network round trips, but they can also hurt performance by causing redundant data to be returned since the way entities from different tables are returned is through the use of a join which often causes some data to be repeated—this repeated data is accounted for during the result assembly process so you end up with accurate results, but there is still overhead in the data returned over the network.
     
  • Query: Check the ObjectStateManager by key to see if the entity in question is already present (unless MergeOption is NoTracking).
     
  • Startup: The first time a particular entity type / query result shape is returned, dynamic code is generated which extracts the relevant data from the DataReader, constructs the object and initializes it with data (called materialization).
     
  • Query: Objects are actually materialized.
     
  • Query: Objects are added to the ObjectStateManager (unless MergeOption is NoTracking).
     
  • Query: Relationships between entities are fixed up.
     
  • Application: Results are processed potentially causing additional queries (especially through lazy loading, etc.)

OK.  Now you know what goes on under the covers, and you’ve found something that seems to be taking more time than necessary.  That brings us to…

What can be done to improve EF query perf?

Here’s a general rule-of-thumb checklist for EF query perf tuning in the order I would tend to investigate (since this is usually the order of highest bang-for-the-buck).

DANGER!  DANGER!  DANGER!  If you read my post about perf tuning philosophy, all kinds of bells should be going off in your head about now.  You should be saying something like, “I thought perf was a science and I should measure what exactly is the issue, create my own prioritization and then do a series of carefully controlled experiments.”  Of course all those things are true.  You SHOULD measure the reality in your particular situation.  That said, I get asked these kinds of questions quite often, and some of these issues show up frequently enough that it if it’s not too expensive to try one of these fixes, you might just try them and measure the results—this could be cheaper to try than deep profiling or the like.

  1. Pre-compiled views.   Usually a very easy to implement change is to turn on pre-compiled views.  This will move the time of generating views from the first query in your app-domain to design time.
     
  2. Avoid distributed transactions.   If your app is triggering distributed transactions, getting rid of that can be a MAJOR perf win.  So even though this can be more involved, it’s worth looking for.  If you are using SQL Server 2000 or 2005, this can be caused just by opening and closing the connection more than once inside the same transaction, and if that’s the case the fix is as simple as explicitly opening the connection before the first operation inside the transaction and then either explicitly closing it when the transaction is done or letting it be closed when the context is disposed.
     
  3. Avoid extra connection opens.   Even if you aren’t getting distributed transactions, if you have a number of operations all in a row, just opening the connection can be expensive.  As with the best case of distributed transactions, it can be easily addressed by explicitly opening the connection before the first operation.
     
  4. Reduce database round trips.   Eager loading through the Include method can cause graphs of related entities to be retrieved in a single round trip rather than several.  This can be an especially big win if you are using lazy loading to retrieve a graph of related entities across a 1-many relationship like a set of customers and their orders.  With lazy loading this will take one round trip for the customers + one round trip for each order, while lazy loading could return the entire set in a single round trip.  Be careful, though, to balance the cost of round trips against the cost of extra network traffic and query complexity.  If you have a large graph of related entities (across several relationships), using eager loading to retrieve the whole thing may not be the most efficient.  Often the best answer is a combination with some eager loading and some lazy loading or some other combination.  The only way to tell is to try things and measure.
     
  5. Retrieve less data.   Often this is the first thing your DBA will tell you: Quit retrieving data that you don’t actually use in your app.  If you only want to display a list of customers and their zip codes, then retrieving the entire customer object might be a bad idea.  On the other hand, it may be that querying for the customer object up front will allow you to avoid later queries for the rest of the data, so it’s a balance and you have to try things and measure.
     
  6. Reduce post DB-query client processing costs.   Often the simplest way to do this is to set the MergeOption of the query to NoTracking.  When you do this the EF will no longer check the ObjectStateManager to see if the entity is already present there, and after the objects are constructed, it will not add the entity to the ObjectStateManager.  This can often produce a significant perf improvement, but you have to keep in mind the side effects this will cause since entity change tracking and identity resolution will no longer happen, etc.
     
  7. Reduce pre DB-query client processing costs.   If you only execute this particular query once, then there’s really not much you can do, but if you execute the same query multiple times and you use ESQL, then the EF will automatically cache the translated query.  For LINQ, though, the EF doesn’t automatically cache the query which is where LINQ compiled queries come in.  They allow you to explicitly do the translation ahead of time, hold onto a token representing the query and then use that token to execute it each time.
     
  8. Reduce complexity of the DB query.   You may find opportunities to do this by adjusting the shape of the LINQ or ESQL that you write in your application.  More often, though, you should look for ways to adjust the model shape.  Deep inheritance hierarchies with lots of relationships, and especially if they use TPC-style inheritance tend to create complex queries.  If you can reduce the depth of your inheritance hierarchy, write queries that return fewer different types in a single query, or switch from TPC to TPH or something like that, the result will tend to be less complex.  While the EF often does a great job in the database queries that it generates, occasionally it will produce a query which is less than ideal.  If you determine this is your bottleneck and you know how to hand-write a better query, then you can consider writing the query by hand as a stored procedure and calling that in order to take complete control over the query rather than letting the EF generate it.

- Danny

Comments

  • Anonymous
    July 23, 2010
    The comment has been removed

  • Anonymous
    July 26, 2010
    Thanks for such a great post and the review, I am totally impressed! Keep stuff like this coming.

  • Anonymous
    August 02, 2012
    Great Post!!! Thanks.

  • Anonymous
    November 04, 2012
    I am adding a sql logging system to my programs. I want to open a new connection in every form with connection string below with only changing "Application Name" for example Application Name=FocusEngine-Invoice , Application Name=FocusEngine-Order. It makes all first query very slow for all new connection(10s). I know first initialization very slow for entity but if not change string there is no problem. How can I solve this problem any bodyhave an idea? Invoice metadata=res:///FocusLocalDB.csdl|res:///FocusLocalDB.ssdl|res:///FocusLocalDB.msl;provider=System.Data.SqlClient;provider connection string= "Data Source=localhost;Initial Catalog=FocusLocalDB;User ID=sa;Password=as;pooling=true; max pool size=10000;Application Name=FocusEngine-Invoice;MultipleActiveResultSets=True"; Order metadata=res:///FocusLocalDB.csdl|res:///FocusLocalDB.ssdl|res:///FocusLocalDB.msl;provider=System.Data.SqlClient;provider connection string= "Data Source=localhost;Initial Catalog=FocusLocalDB;User ID=sa;Password=as;pooling=true; max pool size=10000;Application Name=FocusEngine-Order;MultipleActiveResultSets=True";