Entity Framework 6 - Query Performance

Question

I use Entity Framework 6 and i currently have a query with many includes which loads about 1200 entities into the dbContext. Loading the entities seems to be quite slow as the query takes almost a minute. Is there anything I can do about the performance? I have 4 such queries that take 2.5 minutes to load? LazyLoading is enabled but for performance reasons i preload the entities.

var report = DbContext.REPORT.Single(r => r.ID == reportId);

//this query takes a bit less than 1 minute
DbContext.REPORT_ELEMENT
    .Include(re => re.LAYOUT)
    .Include(re => re.PAGEMASTER)
    .Include(re => re.REPORT_ELEMENTS)
    .Include(re => re.SUBTITLE_CONTENT)
    .Include(re => re.REPORT_ELEMENT_NOTE)
    .Include("SUBTITLE_CONTENT.CONTENT_ELEMENT.LANGUAGE")
    .Include("TITLE_CONTENT.CONTENT_ELEMENT.LANGUAGE")
    .Where(re => re.REPORT_ID == report.ID)
    .Load();

Answer 1

Performance suggestions:

• Prevent tracking. Query in read-only mode.
• Prevent getting too much data in one query. Try to page it.
• Prevent include. The query has too many Includes which makes performance bad.

Prevent tracking

Consider adding AsNoTracking for this makes query performance better.

Reference: https://learn.microsoft.com/en-us/ef/core/querying/tracking#no-tracking-queries

Only get the data you need

And the key reason for your slow query is it outputs too much data. Consider adding: Take(200), Skip() to take only the data you need or the current page requires. Use a pager to generate the report. This might helps a lot.

Prevent Include

Include generates SQL to select multiple tables. Which greatly increased complexity. You can only select the data you need and prevent writing the Include function.

For example, if you only want to get the last ball in the box, consider writing like this:

public class Box
{
    public int Id { get; set; }
    public IEnumerable<Ball> Balls { get; set; }
}

public class Ball
{
    public int Id { get; set; }

    public int BoxId { get; set; }
    public Box Box { get; set; }
}

var boxes = await Boxes
            // DO NOT Call Include(t => t.Balls) here!
            .Where(somecondition)
            .Select(t => new Box(){
              Id = t.Id,
              Balls = t.Balls.OrderByDescending(x => x.CreationTime)
                         .Take(1) // Only get what you need
            })               
            .ToListAsync()

Also when we use Select we can remove .Include because it won’t have any effect here.

Answer 2

Additionally to the advices from Anduin, I would like to add the advice to split up the Includes() in several distinct queries. EF will be able to keep track of the references between the entities within the same DBContext. As a general rule of thumb - do not use more then three Includes() in the same query. Also ensure, that you have an index in the DB for every resulting JOIN.

To be able to do so, you must expose your FK fields in the entities additionally to the navigation properties.

Your initial query would become something like this:

    DbContext.LAYOUT
        .Where(re => re.LAYOUT_ID == report.LAYOUT_FK)
        .Load();
    DbContext.PAGEMASTER
        .Where(re => re.PAGEMASTERT_ID == report.PAGEMASTER_FK)
        .Load();

Answer 3

Disclaimer: I'm the owner of the project Entity Framework Plus

The Query IncludeOptimized feature allows to filter with include and optimize the query performance at the same time.

It usually improves the performance (split the query in smaller queries)

DbContext.REPORT_ELEMENT
    .IncludeOptimized(re => re.LAYOUT)
    .IncludeOptimized(re => re.PAGEMASTER)
    .IncludeOptimized(re => re.REPORT_ELEMENTS)
    .IncludeOptimized(re => re.SUBTITLE_CONTENT)
    .IncludeOptimized(re => re.REPORT_ELEMENT_NOTE)
    .IncludeOptimized(re => re.SUBTITLE_CONTENT.Select(sc => sc.CONTENT_ELEMENT))  // SelectMany?
    .IncludeOptimized(re => re.SUBTITLE_CONTENT.Select(sc => sc.CONTENT_ELEMENT).Select(ce => ce.LANGUAGE)) // SelectMany?
    .IncludeOptimized(re => re.TITLE_CONTENT)
    .IncludeOptimized(re => re.SUBTITLE_CONTENT.Select(sc => sc.CONTENT_ELEMENT)) // SelectMany?
    .IncludeOptimized(re => re.SUBTITLE_CONTENT.Select(sc => sc.CONTENT_ELEMENT).Select(ce => ce.LANGUAGE)) // SelectMany?
    .Where(re => re.REPORT_ID == report.ID)
    .Load();

Documentation: EF+ Query IncludeOptimized