How much Include in EntityFramework can I use on an ObjectSet to maintain performance?

asp.net-mvc-2 entity-framework linq linq-to-entities performance

Question

I am using the following LINQ query for my profile page:

var userData = from u in db.Users
                        .Include("UserSkills.Skill")
                        .Include("UserIdeas.IdeaThings")
                        .Include("UserInterests.Interest")
                        .Include("UserMessengers.Messenger")
                        .Include("UserFriends.User.UserSkills.Skill")
                        .Include("UserFriends1.User1.UserSkills.Skill")
                        .Include("UserFriends.User.UserIdeas")
                        .Include("UserFriends1.User1.UserIdeas")
                               where u.UserId == userId
                               select u;

It has a long object graph and uses many Includes. It is running perfect right now, but when the site has many users, will it impact performance much?

Should I do it in some other way?

1
47
12/6/2015 6:19:57 AM

Accepted Answer

A query with includes returns a single result set and the number of includes affect how big data set is transfered from the database server to the web server. Example:

Suppose we have an entity Customer (Id, Name, Address) and an entity Order (Id, CustomerId, Date). Now we want to query a customer with her orders:

var customer = context.Customers
                      .Include("Orders")
                      .SingleOrDefault(c => c.Id == 1);

The resulting data set will have the following structure:

 Id | Name | Address | OrderId | CustomerId | Date 
---------------------------------------------------
  1 |  A   |   XYZ   |    1    |     1      | 1.1.
  1 |  A   |   XYZ   |    2    |     1      | 2.1.

It means that Cutomers data are repeated for each Order. Now lets extend the example with another entities - 'OrderLine (Id, OrderId, ProductId, Quantity)andProduct (Id, Name)`. Now we want to query a customer with her orders, order lines and products:

var customer = context.Customers
                      .Include("Orders.OrderLines.Product")
                      .SingleOrDefault(c => c.Id == 1);

The resulting data set will have the following structure:

 Id | Name | Address | OrderId | CustomerId | Date | OrderLineId | LOrderId | LProductId | Quantity | ProductId | ProductName
------------------------------------------------------------------------------------------------------------------------------
  1 |  A   |   XYZ   |    1    |     1      | 1.1. |     1       |    1     |     1      |    5     |    1      |     AA
  1 |  A   |   XYZ   |    1    |     1      | 1.1. |     2       |    1     |     2      |    2     |    2      |     BB
  1 |  A   |   XYZ   |    2    |     1      | 2.1. |     3       |    2     |     1      |    4     |    1      |     AA
  1 |  A   |   XYZ   |    2    |     1      | 2.1. |     4       |    2     |     3      |    6     |    3      |     CC

As you can see data become quite a lot duplicated. Generaly each include to a reference navigation propery (Product in the example) will add new columns and each include to a collection navigation property (Orders and OrderLines in the example) will add new columns and duplicate already created rows for each row in the included collection.

It means that your example can easily have hundreds of columns and thousands of rows which is a lot of data to transfer. The correct approach is creating performance tests and if the result will not satisfy your expectations, you can modify your query and load navigation properties separately by their own queries or by LoadProperty method.

Example of separate queries:

var customer = context.Customers
                      .Include("Orders")
                      .SingleOrDefault(c => c.Id == 1);
var orderLines = context.OrderLines
                        .Include("Product")
                        .Where(l => l.Order.Customer.Id == 1)
                        .ToList();

Example of LoadProperty:

var customer = context.Customers
                      .SingleOrDefault(c => c.Id == 1);
context.LoadProperty(customer, c => c.Orders);

Also you should always load only data you really need.

Edit: I just created proposal on Data UserVoice to support additional eager loading strategy where eager loaded data would be passed in additional result set (created by separate query within the same database roundtrip). If you find this improvement interesting don't forget to vote for the proposal.

84
5/8/2015 10:21:33 AM

Popular Answer

You can improve performance of many includes by creating 2 or more small data request from data base like below.

According to my experience,Only can give maximum 2 includes per query like below.More than that will give really bad performance.

var userData = from u in db.Users
                        .Include("UserSkills.Skill")
                        .Include("UserIdeas.IdeaThings")
                        .FirstOrDefault();

 userData = from u in db.Users
                    .Include("UserFriends.User.UserSkills.Skill")
                    .Include("UserFriends1.User1.UserSkills.Skill")
                    .FirstOrDefault();

Above will bring small data set from database by using more travels to the database.

I have written blog post above this by using my own experience.It's Here

I hope this will help to you.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow