Entity Framework 6 - 查询性能 [英] Entity Framework 6 - Query Performance

问题描述

我使用 Entity Framework 6，我目前有一个包含许多包含的查询，它将大约 1200 个实体加载到 dbContext 中.加载实体似乎很慢，因为查询需要将近一分钟.我能对表演做些什么吗?我有 4 个这样的查询需要 2.5 分钟才能加载?LazyLoading 已启用，但出于性能原因，我预加载了实体.

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();

推荐答案

性能建议:

• 阻止跟踪.以只读模式查询.
• 防止在一次查询中获取过多数据.尝试分页.
• 防止包含.查询中有太多的 Include 会导致性能不佳.

• 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.

考虑添加 AsNoTracking 以提高查询性能.

Consider adding AsNoTracking for this makes query performance better.

参考:https://docs.microsoft.com/en-us/ef/core/querying/tracking#no-tracking-queries

查询速度慢的主要原因是它输出了太多数据.考虑添加:Take(200)、Skip() 以仅获取您需要或当前页面需要的数据.使用寻呼机生成报告.这可能会有很大帮助.

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.

Include 生成 SQL 以选择多个表.这大大增加了复杂性.只能选择自己需要的数据，避免编写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()

同样，当我们使用 Select 时，我们可以删除 .Include 因为它在这里不会有任何影响.

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

这篇关于Entity Framework 6 - 查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！