Slow performance retrieving many nested entities using Entity Framework

Question

I'm using Entity Framework 6 to retrieve an entity which has several navigation properties to deep nested entities. I'm facing a huge performance impact which has been highlited by logging Entity Framework's requests. I have hundred of records in my deepest entity (Geocoordinate) and retrieving a Session entity takes up to 8 seconds (I'd like less than 1 sec).

Here's a Gist sample of the log: https://gist.github.com/bbougot/837419c682a75d4741a5

Here's my code:

        using (var context = new ModelContainer())
        {
            context.Database.Log = msg => Trace.WriteLine(msg);
            var session= await
                context.SessionSet.FirstOrDefaultAsync(a => a.Identifier == sessionIdentifier);
            var result = await Json(session).ExecuteAsync(new CancellationToken());
            return ResponseMessage(result);
        }

I'm retrieving the red entities of my model (notice the only one-to-many relation between Route and Geoposition):

(Link to large size image: https://i.sstatic.net/vDNkH.jpg)

I've tried disabling lazy loading and use Include to eager load my entities but it's even worse, I've reached the SQL Server timeout.

Answer 1

The reason that the entity is taking so long to load is that you're suffering from a classic select N+1 problem.

As you already seem to be aware Entity Framework by default lazy-loads all navigation properties by creating a dynamic proxy object around them. If it detects that the proxy is accessed (i.e. you follow the navigation property) then it issues a query to the database to actually go off and fetch the data. The reason that this has a performance impact is that each database query that gets executed has a certain overhead associated with it (e.g. the latency to the database server), and therefore the best option is to execute a single query that brings back all of the data you need in one go rather than separate queries for each individual entity.

When it comes to collection properties (e.g. your Route -> Geoposition relation) then things get even worse because Entity Framework will generate a separate select for each item in the collection. This is why you're seeing so many hundreds of queries in your log.

I've tried disabling lazy loading and use Include to eager load my entities but it's even worse, I've reached the SQL Server timeout.

Using the Include method is the correct approach to eagerly-load a navigation property as part of the initial query. If after Includeing the relevant navigation properties you still find your query is taking too long to execute you should look at the execution plan of the query to see where the performance problem lies. Post another question if you need help analyzing it.