I have been looking at Entity Framework performance, particularly around the use of Includes and the time taken to both generate and execute the various queries.
I am going to detail changes I have made, but please correct me if you think any of these assumptions are wrong.
Firstly we have around 10,000 items (not many) in a db and the database is significantly normalised (which results in a significant number of navigation properties). Currently the approach is to lazy load everything and given that requesting one item can spool off tens of db requests, the performance is quite poor, particularly for larger sets of data.
(This is an inherited project and step one is trying to improve performance without significant restructuring)
So my first step was to take the results of a query and then apply the Includes for the navigation properties only to those results.
I know this technically performs 2 queries, but if we have 10,000 items stored, but only want to return 10 items, it makes more sense to only include the navigation properties on those 10 items.
Secondly, where multiple includes are used on a query result and that result set size is quite large, it still suffered from poor performance. I have been pragmatic about when to eager load and when to leave the lazy loading in place.
My next change was to load query includes in batches, so performing:
query.Include(q => q.MyInclude).Load();
var query = ctx.Filters.Where(x => x.SessionId == id)
.Join(ctx.Items, i => i.ItemId, fs => fs.Id, (f, fs) => fs);
.Include(x => x.ItemNav1)
.Include(x => x.ItemNav2).Load();
.Include(x => x.ItemNav3)
.Include(x => x.ItemNav4).Load();
.Include(x => x.ItemNav5)
.Include(x => x.ItemNav6).Load();
Your second approach relies on the EF navigation property fixup process. The problem is though that every
query.Include(q => q.ItemNavN).Load();
statement will also include all the master record data along with the related entity data.
Using the same basic idea, one potential improvement could be to execute one
Load per each navigation property, replacing the
Include with either
Select (for references) or
SelectMany (for collections) - something similar to how EF Core processes the
Taking your second approach example, you could try the following and compare the performance:
var query = ctx.Filters.Where(x => x.SessionId == id) .Join(ctx.Items, i => i.ItemId, fs => fs.Id, (f, fs) => fs); query.Select(x => x.ItemNav1).Load(); query.Select(x => x.ItemNav2).Load(); query.Select(x => x.ItemNav3).Load(); query.Select(x => x.ItemNav4).Load(); query.Select(x => x.ItemNav5).Load(); query.Select(x => x.ItemNav6).Load(); var result = query.ToList(); // here all the navigation properties should be populated