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
– Adil Mammadov, Commented Aug 23, 2016 at 8: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
– Kashif Khan, Commented Aug 23, 2016 at 8: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. — Adil Mammadov
– Adil Mammadov, Commented Aug 23, 2016 at 9:10

Martijn Pieters · Accepted Answer · 2020-10-20 07:39:00Z

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.

Wurd · Accepted Answer · 2016-08-23 10:19:30Z

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;

UserControl · Accepted Answer · 2016-08-23 08:09:43Z

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.

Collectives™ on Stack Overflow

Entity Framework .include Performance Issue

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related