Entity Framework gets progressively slow with extra join added even though SQL generated is fast

Question

We have 18 table join which is typical for ERP systems. The join is done via LINQ over Entity Framework.

The join gets progressively slower as more joins are added. The return result set is small(15 records). The LINQ generated query is captured via SQL Profiler and when we run this via Microsoft Management Console it is very fast : 10ms. When we run it via our C# LINQ-over-EntityFramework it takes 4 seconds.

What i guess is happening: The time it takes to compile expression tree into SQL is 2 seconds out of total 4 seconds, and another 2 seconds i guess is spent internally to convert SQL result set into actually C# classes. Also it is not connected to initialization of entity framework because we run some queries before and repetitive calls to this join produce same 4 seconds.

Is there a way to speed this up. Otherwise we are considering abandoning Entity Framework for being absolutely inefficient...

Answer 1

In case it helps, I had a nasty performance issue, whereby a simple query that took 1-2 seconds in raw SQL took about 11 seconds via EF.

I went from using...

List<GeographicalLocation> geographicalLocations = new SalesTrackerCRMEntities()
  .CreateObjectSet<GeographicalLocation>()
  .Where(g => g.Active)
  .ToList();

which took about 11 seconds via EF, to using...

var geographicalLocations = getContext().CreateObjectSet<GeographicalLocation>()
    .AsNoTracking()
    .Where(g => g.Active).ToList();

which took less than 200 milliseconds.

The disadvantage to this is that it won't load related entities, so you have to load them manually afterwards, but it gives such an immense performance boost that it was well worth it (in this case at least).

You would have to assess each case individually to see if the extra speed is worth the extra code.

Answer 2

You correctly identified bottlenecks. If you have quite complex queries, I would suggest you to use compiled queries to overcome expression tree to sql query conversion.

You can refer Compiled Queries in EF from here.

Fo second part if EF is using two much time materialize your object graph then I would suggest to use some other means to retrieve data apart from EF.

One option can be Dapper.NET, You can have your concise sql query and you can directly retrieve its result in concrete model objects using Dapper (or any other tiny ORM)