1

I'm loading ServiceTrips for a scheduling calendar and wondering if there is fastest approach for eagerly loading the related data from many tables.

Here is the model that needs to be loaded (the mapping is table per concrete type)

public class ServiceTrip : BaseEntity
{
    public ICollection<Employee> Crew { get; set; }
    public ICollection<ServiceAssignment> Assignments { get; set; }
}

public class ServiceAssignment : BaseEntity
{
    public ServiceOrder ServiceOrder { get; set; }
    public DeliveryOrder DeliveryOrder { get; set; }
}

public class ServiceOrder : OrderBase { }
public class DeliveryOrder : OrderBase { }

public abstract class OrderBase : BaseEntity
{
    public ICollection<ServiceAssignment> Assignments { get; set; }
    public Sale Sale { get; set; }
}

public class Sale : BaseEntity
{
    public Employee Manager { get; set; }
    public Customer Customer { get; set; }
    public ICollection<ServiceOrder> ServiceOrders { get; set; }
    public ICollection<DeliveryOrder> DeliveryOrders { get; set; }
}

public class Employee : BaseEntity { }
public class Customer : BaseEntity { }

public abstract class BaseEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
}

I've basically tried things like this and don't know where to start.

var tripsQuery = db.ServiceTrips

.Where(x => x.StartDate >= FirstDay && x.StartDate <= LastDay)

.Include(x => x.Crew)
.Include(x => x.ServiceAssignments)

.Include(x => x.ServiceAssignments.Select(y => y.DeliveryOrder))
.Include(x => x.ServiceAssignments.Select(y => y.ServiceOrder))

.Include(x => x.ServiceAssignments.Select(y => y.DeliveryOrder.Sale))
.Include(x => x.ServiceAssignments.Select(y => y.ServiceOrder.Sale))

.Include(x => x.ServiceAssignments.Select(y => y.DeliveryOrder.Sale.Customer))
.Include(x => x.ServiceAssignments.Select(y => y.ServiceOrder.Sale.Customer))

.Include(x => x.ServiceAssignments.Select(y => y.DeliveryOrder.Sale.Manager))
.Include(x => x.ServiceAssignments.Select(y => y.ServiceOrder.Sale.Manager))
;

The model is simplified for the question. In production I'm pulling from about 20 tables. It takes about 10-15 seconds to load. I tried loading each day asynchronously but that increased the total time to load.

3
  • 1
    Performance depends on a whole lot of factors, the size of the result set, if you have indexes on the columns you are scanning ect. My recommendation would be to reproduce what you are trying to do in raw SQL, and that will give you an idea of if your query is currently unreasonable. Commented Jun 21, 2013 at 5:23
  • 1
    As none of the collections are virtual I would assert that the data is eager loading without any of the Include's Commented Jun 21, 2013 at 8:10
  • @qujck ah yes that is right. That is a mistake I made when simplifying the model for the question. They are virtual in my production model but I am explicitly disabling lazy loading. Commented Jun 21, 2013 at 8:26

1 Answer 1

1

In my opinion when you are issuing a complex query that joins across twenty tables you might want to start of with inspecting the database itself

Here are some guidelines (some points are pertinent to SQL Server, which I have taken the liberty to assume is the database you are using)

  1. Check the query execution plan on the raw SQL - If it is time consuming to reproduce the entire EF code in SQL just maybe parts of it - for instance the ServiceTrips, ServiceAssignments and DeliveryOrder tables. This will give you an idea of the bottlenecks with regards to indexes etc

  2. Inspect if, because of the size of the data, network latency is the bottleneck and not the query itself

  3. Consider using indexed views which might improve your performance

  4. Architecture - To speed up such complex queries use caching of some sort maybe to store pertinent data - in your case maybe the names of the Managers, Crew etc.

Sign up to request clarification or add additional context in comments.

1 Comment

Thanks. It turned out to be a combination of a few things.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.