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

I suspect your query takes so long to generate becuase you are treating Entity Framework like it is a SQL Query, which is not correct. You have many joins and akward calls in your linq syntax. Generally, your syntax should be similar to the following fictitious modeling query:

var result = (from appointment in appointments
              from operation in appointment.Operations
              where appointment.Id == 12
              select new Model {
                 Id = appointment.Id,
                 Name = appointment.Name,
                 // etc, etc
              }).ToList();

There is no use of joins above, the navigation property between Appointment and Operations takes care of the neccessary plumbing. Remember, this is an ORM, there is no concept of a join, only a concept of relationships.

The call to Distinct at the end, also indicates the structure of the db schema may be problematic if it returns too many duplicate results.

If after refactoring the entity model and correctly constructing the query still leaves with underperformance, it is advisable to use a stored procedure and map the result with EF's built in methods for doing so.

Answer 3

The problem is that you are probably passing it to a data source that is running all sorts of additional queries based on you open result set.

Try this instead:

IEnumerable<SigmaTEK.Web.Models.SchedulerGridModel> tasks = (from appointment in _appointmentRep.Value.Get().Where(a => (a.Start < DbContext.MaxTime && DbContext.MinTime < a.Expiration))
                                                                    join timeApplink in _timelineAppointmentLink.Value.Get().Where(a => a.AppointmentId != Guid.Empty)
                                                                    on appointment.Id equals timeApplink.AppointmentId
                                                                    join timeline in timelineRep.Value.Get().Where(i => timelines.Contains(i.Id))
                                                                    on timeApplink.TimelineId equals timeline.Id
                                                                    join repeater in _appointmentRepeaterRep.Value.Get().Where(repeater => (repeater.Start < DbContext.MaxTime && DbContext.MinTime < repeater.Expiration))
                                                                    on appointment.Id equals repeater.Appointment
                                                                    into repeaters

                                                                    from repeater in repeaters.DefaultIfEmpty()
                                                                    join aInstance in _appointmentInstanceRep.Value.Get()
                                                                    on appointment.Id equals aInstance.Appointment
                                                                    into instances

                                                                    from instance in instances.DefaultIfEmpty()
                                                                    join opRes in opResRep.Get()
                                                                    on instance.ResourceOwner equals opRes.Id
                                                                    into opResources

                                                                    from op in opResources.DefaultIfEmpty()
                                                                    join itemResource in _opDocItemResourcelinkRep.Value.Get()
                                                                    on op.Id equals itemResource.Resource
                                                                    into itemsResources

                                                                    from itemresource in itemsResources.DefaultIfEmpty()
                                                                    join opDocItem in opDocItemRep.Get()
                                                                    on itemresource.OpDocItem equals opDocItem.Id
                                                                    into opDocItems

                                                                    from opdocitem in opDocItems.DefaultIfEmpty()
                                                                    join opDocSection in opDocOpSecRep.Get()
                                                                    on opdocitem.SectionId equals opDocSection.Id
                                                                    into sections

                                                                    from section in sections.DefaultIfEmpty()
                                                                    join opDoc in opDocRep.Get()
                                                                    on section.InternalOperationalDocument equals opDoc.Id
                                                                    into opdocs

                                                                    from opdocitem2 in opDocItems.DefaultIfEmpty()
                                                                    join opDocItemLink in opDocItemStrRep.Get()
                                                                    on opdocitem2.Id equals opDocItemLink.Parent
                                                                    into opDocItemLinks

                                                                    from link in opDocItemLinks.DefaultIfEmpty()
                                                                    join finItem in finItemsRep.Get()
                                                                    on link.Child equals finItem.Id
                                                                    into temp1

                                                                    from rd1 in temp1.DefaultIfEmpty()
                                                                    join sec in finSectionRep.Get()
                                                                    on rd1.SectionId equals sec.Id
                                                                    into opdocsections

                                                                    from finopdocsec in opdocsections.DefaultIfEmpty()
                                                                    join finopdoc in opDocRep.Get().Where(i => i.DocumentType == "Quote")
                                                                     on finopdocsec.InternalOperationalDocument equals finopdoc.Id
                                                                     into finOpdocs

                                                                    from finOpDoc in finOpdocs.DefaultIfEmpty()
                                                                    join entry in entryRep.Get()
                                                                    on rd1.Transaction equals entry.Transaction
                                                                    into entries

                                                                    from entry2 in entries.DefaultIfEmpty()
                                                                    join resproduct in resprosductRep.Get()
                                                                     on entry2.Id equals resproduct.Entry
                                                                     into resproductlinks

                                                                    from resprlink in resproductlinks.DefaultIfEmpty()
                                                                    join res in resRep.Get()
                                                                    on resprlink.Resource equals res.Id
                                                                    into rootResource

                                                                    from finopdoc in finOpdocs.DefaultIfEmpty()
                                                                    join rel in orgDocIndRep.Get().Where(i => (i.Relationship == "OrderedBy"))
                                                                    on finopdoc.Id equals rel.OperationalDocument
                                                                    into orgDocIndLinks

                                                                    from orgopdoclink in orgDocIndLinks.DefaultIfEmpty()
                                                                    join org in orgRep.Get()
                                                                    on orgopdoclink.Organization equals org.Id
                                                                    into toorgs

                                                                    from opdoc in opdocs.DefaultIfEmpty()
                                                                    from rootresource in rootResource.DefaultIfEmpty()
                                                                    from toorg in toorgs.DefaultIfEmpty()
                                                                    select new SigmaTEK.Web.Models.SchedulerGridModel()
                                                                    {

                                                                        Id = appointment.Id,
                                                                        Description = appointment.Description,
                                                                        End = appointment.Expiration,
                                                                        Start = appointment.Start,
                                                                        OperationDisplayId = op.DisplayId,
                                                                        OperationName = op.Name,
                                                                        AppContextId = _appContext.Id,
                                                                        TimelineId = timeline.Id,
                                                                        AssemblyDisplayId = rootresource.DisplayId,
                                                                        //Duration = SigmaTEK.Models.App.Utils.StringHelpers.TimeSpanToString((appointment.Expiration - appointment.Start)),
                                                                        WorkOrder = opdoc.DisplayId,
                                                                        Organization = toorg.Name
                                                                    }).Distinct().ToList();

//In your UI
MyGrid.DataSource = tasks;
MyGrid.DataBind();

//Do not use an ObjectDataSource! It makes too many extra calls

Answer 4

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)

Answer 5

It is hard to tell what is going wrong here without seeing how you are using linq, but I suspect this will fix your problem:

var myResult = dataContext.table.Where(x == "Your joins and otherstuff").ToList();
//after converting it to a list use it how you need, but not before.

If this does not help please post your code.