Entity Framework .include Performance Issue

Question

I have the below query which is tremendously slow. I am new to Entity Framework and I believe it has got to do something with Eager Loading, Lazy Loading or Explicit Loading. Need help optimize the below C# statement.

var queryResult = CurrentSession.Set<SomeType_T>().Include(a => a.SomeType1_T)
                                .Include(a => a.SomeType1_T.Catalog_Type_T)
                                .Include(a => a.SomeType1_T.SomeType4_T)
                                .Include(a => a.SomeType1_T.SomeType2_T)
                                .Include("SomeType1_T.SomeType2_T.SomeType3_T")
                                .Include(a => a.SomeType1_T.SomeType4_T.SomeType5_T)
                                .Include(a => a.SomeType1_T.SomeType5_T)
                                .Include(a => a.SomeType1_T.Questions_T)
                                .Include(a => a.SomeType1_T.Questions_T.Question_Type_T)
                                .Include(a => a.SomeType1_T.Members_T)
                                .Include(b => b.SomeMasterType_T)
                                .Include(b => b.SomeMasterType_T.SomeMasterType1_T)
                                .Include(c => c.SomeType6_T)
                                .Include(d => d.SomeType7_T)
                                .Include(d => d.SomeType8_T)
                                .Include(d => d.SomeType8_T1)
                                .Where(t => t.SomeType9_T == _MatchThisKey);

Do you have to load all entities at once? Do you use them all? Have you tried `Lazy loading`? — Adil Mammadov, Aug 23 '16 at 08:14
@AdilMammadov have not tried lazy loading. if i did work with lazy loading then what should i change in the code above? — Kashif Khan, Aug 23 '16 at 08:52
You are using *eager loading* now, with *lazy loading* you get the main data, and entity framework queries database each time you access property. Have a look at to [this example](http://stackoverflow.com/documentation/entity-framework/4678/loading-related-entities/16445/lazy-loading#t=201608230908490087621). — Adil Mammadov, Aug 23 '16 at 09:10

score 6 · Answer 1 · edited Oct 20 '20 at 07:39

You can improve the performance of many includes by creating 2 or more small data request from the database as shown below.According to my experience,you can give maximum 2 includes per query.More than that will give really bad performance.Yes,this is Ugly. But it will give very good performance improvement.You too can try and feel that :)

Note : This is just an example.

var userData = from u in db.Users
                        .Include(p=>p.UserSkills)
                        .Include(p=>p.UserIdeas)
                        .FirstOrDefault();

 userData = from u in db.Users
                    .Include(p=>p.UserFriends)
                    .Include(p=>p.UserFriends1)
                    .FirstOrDefault();

Above will bring small data set from database by using more than one traverse to the database.

score 1 · Answer 2 · answered Aug 23 '16 at 08:09

1

Any Include() call translates to SQL join operator and the number of joins in your example is very serious. If you really need to perform all the joins I'd optimize indexes by looking at the DB engine execution plan.

answered Aug 23 '16 at 08:09

UserControl

13,469
15
88
171

Wurd · Answer 3 · 2016-08-23T10:19:30.917

Another option is to use asynchronous loading of your collections if you don't need all your data up front.

For example:

var initialResult = db.Person.Include(c=>c.FirstCollection).First();
var load1 = db.Entry(initialResult).Collection(c=>c.SecondCollection).LoadAsync();    
//do all the work you can
await load1;
//continue with more work

You should also consider .AsNoTracking() if you don't plan on editing and saving the entities back to the db. It gives a small performance boost but won't cache entities for future queries.

If you are going to explicitly handle all the collection loading eagerly or in code later on then use these too as they also give a small performance boost.

db.Configuration.LazyLoadingEnabled = false;
db.Configuration.ProxyCreationEnabled = false;

Entity Framework .include Performance Issue

3 Answers3