加速Linq实体查询

Question

我目前有一个查询，它不需要太长时间，但由于数据库中的数据量过大，有时会崩溃。

有人能否注意到我可以做些什么来加快查询速度？

public IList<Report> GetReport(CmsEntities context, long manufacturerId, long? regionId, long? vehicleTypeId)
        {
            var now = DateTime.Now;
            var today = new DateTime(now.Year, now.Month, 1);
            var date1monthago = today.AddMonths(-1);
            var date2monthago = today.AddMonths(-2);
            var date3monthago = today.AddMonths(-3);
            var date4monthago = today.AddMonths(-4);
            var date5monthago = today.AddMonths(-5);
            var date6monthago = today.AddMonths(-6);
            today = TimeManager.EndOfDay(new DateTime(now.AddMonths(-1).Year, today.AddMonths(-1).Month, DateTime.DaysInMonth(now.Year, today.AddMonths(-1).Month)));             
            var query = from item in context.Invoices
                         where item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Select(x => x.ManufacturerId).Contains(manufacturerId)
                         && (item.InvoiceDate >= date6monthago && item.InvoiceDate <= today)
                         && (regionId.HasValue && regionId.Value > 0 ? item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Select(x => x.RegionId).Contains(regionId.Value) : true)
                         && (item.InvType == "I" || item.InvType == null)
                         && (vehicleTypeId.HasValue && vehicleTypeId.Value > 0 ? item.Repair.Job.Vehicle.Model.VehicleTypes.Select(x => x.Id).Contains(vehicleTypeId.Value) : true)
                         select item;

            var query2 = from item in query
                         group item by new { item.Repair.Job.Bodyshop } into g
                         let manufJobs = query.Where(x => x.Repair.Job.Vehicle.Model.ManufacturerId == manufacturerId && x.Repair.Job.BodyshopId == g.Key.Bodyshop.Id)
                         let allJobs = query.Where(x => x.Repair.Job.BodyshopId == g.Key.Bodyshop.Id)
                         select new tReport
                         {                                     
    MonthSixManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date6monthago.Month && x.InvoiceDate.Year == date6monthago.Year).GroupBy(x => x.Repair.Job).Count(),
    MonthSixJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date6monthago.Month && x.InvoiceDate.Year == date6monthago.Year).GroupBy(x => x.Repair.Job).Count(),

    MonthFiveManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date5monthago.Month && x.InvoiceDate.Year == date5monthago.Year).GroupBy(x => x.Repair.Job).Count(),
    MonthFiveJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date5monthago.Month && x.InvoiceDate.Year == date5monthago.Year).GroupBy(x => x.Repair.Job).Count(),

    MonthFourManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date4monthago.Month && x.InvoiceDate.Year == date4monthago.Year).GroupBy(x => x.Repair.Job).Count(),
    MonthFourJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date4monthago.Month && x.InvoiceDate.Year == date4monthago.Year).GroupBy(x => x.Repair.Job).Count(),

    MonthThreeManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date3monthago.Month && x.InvoiceDate.Year == date3monthago.Year).GroupBy(x => x.Repair.Job).Count(),
    MonthThreeJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date3monthago.Month && x.InvoiceDate.Year == date3monthago.Year).GroupBy(x => x.Repair.Job).Count(),

    MonthTwoManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date2monthago.Month && x.InvoiceDate.Year == date2monthago.Year).GroupBy(x => x.Repair.Job).Count(),
    MonthTwoJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date2monthago.Month && x.InvoiceDate.Year == date2monthago.Year).GroupBy(x => x.Repair.Job).Count(),

    MonthOneManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date1monthago.Month && x.InvoiceDate.Year == date1monthago.Year).GroupBy(x => x.Repair.Job).Count(),
    MonthOneJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date1monthago.Month && x.InvoiceDate.Year == date1monthago.Year).GroupBy(x => x.Repair.Job).Count(),

    ManufTotal = manufJobs.GroupBy(x => x.Repair.Job).Count(),
    Total = allJobs.GroupBy(x => x.Repair.Job).Count(),

    PercentageOf = ((decimal)manufJobs.GroupBy(x => x.Repair.Job).Count() / (decimal)allJobs.GroupBy(x => x.Repair.Job).Count()) * 100
                         };

            return query2.OrderBy(x => x).ToList();
        }

编辑

var query = from item in context.Invoices.AsNoTracking()
                    where item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(x => x.ManufacturerId == manufacturerId)
                    && (item.InvoiceDate >= date12monthago && item.InvoiceDate <= today)
                    && (item.InvType == "I" || item.InvType == null)
                    select item;

        if (regionId.HasValue && regionId.Value > 0)
        {
            query = query.Where(item => item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Select(x => x.RegionId).Contains(regionId.Value));
        }

        if (vehicleTypeId.HasValue && vehicleTypeId.Value > 0)
        {
            query = query.Where(item => item.Repair.Job.Vehicle.Model.VehicleTypes.Select(x => x.Id).Contains(vehicleTypeId.Value));
        }


              var query2 = from item in hey
                     group item by new { item.Repair.Job.Bodyshop, item.InvoiceDate.Month } into m
                     select new TReport
                     {
                         Bodyshop = m.Key.Bodyshop.Name,
                         Bays = m.Key.Bodyshop.Bays,
                         Region = m.Key.Bodyshop.Manufacturer2Bodyshop.FirstOrDefault(x => x.ManufacturerId == manufacturerId).Region.Name,
                         BodyshopCode = m.Key.Bodyshop.Manufacturer2Bodyshop.FirstOrDefault(x => x.ManufacturerId == manufacturerId).BodyshopCode,
                         Total = m.Count(),
                         ManufTotal = m.Where(x => x.Repair.Job.Vehicle.Model.ManufacturerId == manufacturerId).Count(),
                         Totals = m.GroupBy(j => j.InvoiceDate.Month).Select(j => new TPercentReportInner
                         {
                             Month = j.Key,
                             ManufTotal = j.Where(x => x.Repair.Job.Vehicle.Model.ManufacturerId == manufacturerId).Count(),
                             AllTotal = j.Count()
                         })
                     };

我已经简化了查询语句。但是，现在它的性能比以前还要差？

- Beginner

---

将查询设置为AsNoTracking()，因为它们仅用于检索，然后根据@AkashKava的答案进一步进行优化。 - Nikita Shrivastava

这是很多的linq。当使用普通的tsql执行查询时，它的性能如何？你可以总是在sql中编写查询，使用EF执行它并使EF使实体数据可用，这可能更快。另外，尝试分析生成的sql，看看时间花费在哪里。 - 3dd

从生成的SQL开始，分析查询计划。不过我相信手工制作一个专用视图会更好。 - jeroenh

我看到你经常这样写：item.Repair.Job，如果你直接在工作上执行查询，会不会更快呢？另外，我看到你有两个查询。有时候惰性求值是一件好事，但在这里，如果你像这样做：var query2 = from item in query.Tolist()...可能会更好。 但我能想到的最好的改进是在数据库中创建一个视图，并使用标量函数检索信息。Entity Framework 可以处理这个问题。 即使这样可能会很慢，你最终可能还是要做跳过/取出操作。 当然，拥有一个索引数据库也是有帮助的。 - memory of a dream

我把这个放在注释里，以免冒犯任何人，但我真的相信这是一个可行的答案。说实话，如果你在 EF 中通过 Linq 有复杂的查询，我发现把复杂的查询放到传统的 ADO.net 类中通常更容易且性能更好。如果失败了，你总可以把查询放到存储过程中，然后从 Entity 调用该过程。目标基本上是要删除抽象层，因为很多时候它正在做一些不应该做的事情，导致你遇到的性能问题。 - HBomb

Answer 1

我建议您先从查询中删除硬编码的可选条件，这样查询优化器就可以根据您的参数使用不同的查询计划，例如：

var query = from item in context.Invoices.AsNoTracking()
            where item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Select(x => x.ManufacturerId).Contains(manufacturerId)
            && (item.InvoiceDate >= date12monthago && item.InvoiceDate <= today)
            && (item.InvType == "I" || item.InvType == null)
            select item;

if (regionId.HasValue && regionId.Value > 0)
    query=query.Where(item=>item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Select(x => x.RegionId).Contains(regionId.Value));

if (vehicleTypeId.HasValue && vehicleTypeId.Value > 0)
    query=query.Where(item=>item.Repair.Job.Vehicle.Model.VehicleTypes.Select(x => x.Id).Contains(vehicleTypeId.Value));

var query2 = from item in query
             group item by new { item.InvoiceDate.Month, item.Repair.Job.Bodyshop } into g
             select new TReport
             {
                 BodyshopId = g.Key.Bodyshop.Id,  
                 Month = g.Key.Month,
                 MonthAllJobTotal = g.Count()
             };

return query2.ToList();

您还可以检查一下是否将 .Select(x=>x.id).Contains(id) 或者 .Any(x=>x.Id==id) 转换成更快的查询方式，尽管我认为它们在查询计划和执行速度上应该是相似的。这样就会得到：

var query = from item in context.Invoices.AsNoTracking()
            where item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(m=>m.ManufacturerId==manufacturerId)
            && (item.InvoiceDate >= date12monthago && item.InvoiceDate <= today)
            && (item.InvType == "I" || item.InvType == null)
            select item;

if (regionId.HasValue && regionId.Value > 0)
    query=query.Where(item=>item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(m=>m.RegionId==regionId.Value));

if (vehicleTypeId.HasValue && vehicleTypeId.Value > 0)
    query=query.Where(item=>item.Repair.Job.Vehicle.Model.VehicleTypes.Any(v=>v.Id==vehicleTypeId.Value));

var query2 = from item in query
             group item by new { item.InvoiceDate.Month, item.Repair.Job.Bodyshop } into g
             select new TReport
             {
                 BodyshopId = g.Key.Bodyshop.Id,  
                 Month = g.Key.Month,
                 MonthAllJobTotal = g.Count()
             };

return query2.ToList();

根据你现有的情况，我猜测.AsNoTracking()对你的帮助并不大，但它也不会有什么坏处。当检索大量实体时，它的效果更为明显，但这似乎不是你正在做的。

然后，我建议你进一步规范查询，去掉硬编码的ManufacturerId，你可以这样写：

var query = from item in context.Invoices.AsNoTracking()
            where (item.InvoiceDate >= date12monthago && item.InvoiceDate <= today)
            && (item.InvType == "I" || item.InvType == null)
            select item;

if (manufacturerId.HasValue && manufacturerId.Value > 0)
    query=query.Where(item=>item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(m=>m.ManufacturerId==manufacturerId));

if (regionId.HasValue && regionId.Value > 0)
    query=query.Where(item=>item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(m=>m.RegionId==regionId.Value));

if (vehicleTypeId.HasValue && vehicleTypeId.Value > 0)
    query=query.Where(item=>item.Repair.Job.Vehicle.Model.VehicleTypes.Any(v=>v.Id==vehicleTypeId.Value));

var query2 = from item in query
             group item by new { item.InvoiceDate.Month, item.Repair.Job.Bodyshop } into g
             select new TReport
             {
                 BodyshopId = g.Key.Bodyshop.Id,  
                 Month = g.Key.Month,
                 MonthAllJobTotal = g.Count()
             };

return query2.ToList();

最后，我会返回一个IQueryable而不是List，这样如果你不需要一个或多个列，它们可以从最终查询中删除，例如：

public IQueryable<Report> GetReport(CmsEntities context, long? manufacturerId, long? regionId, long? vehicleTypeId)
    {
{
        var now = DateTime.Now;
        var today = new DateTime(now.Year, now.Month, 1);
        var date1monthago = today.AddMonths(-1);
        var date2monthago = today.AddMonths(-2);
        var date3monthago = today.AddMonths(-3);
        var date4monthago = today.AddMonths(-4);
        var date5monthago = today.AddMonths(-5);
        var date6monthago = today.AddMonths(-6);
        today = TimeManager.EndOfDay(new DateTime(now.AddMonths(-1).Year, today.AddMonths(-1).Month, DateTime.DaysInMonth(now.Year, today.AddMonths(-1).Month)));             

    var query = from item in context.Invoices.AsNoTracking()
                where (item.InvoiceDate >= date12monthago && item.InvoiceDate <= today)
                && (item.InvType == "I" || item.InvType == null)
                select item;

    if (manufacturerId.HasValue && manufacturerId.Value > 0)
        query=query.Where(item=>item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(m=>m.ManufacturerId==manufacturerId));

    if (regionId.HasValue && regionId.Value > 0)
        query=query.Where(item=>item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(m=>m.RegionId==regionId.Value));

    if (vehicleTypeId.HasValue && vehicleTypeId.Value > 0)
        query=query.Where(item=>item.Repair.Job.Vehicle.Model.VehicleTypes.Any(v=>v.Id==vehicleTypeId.Value));

    var query2 = from item in query
                 group item by new { item.InvoiceDate.Month, item.Repair.Job.Bodyshop } into g
                 select new TReport
                 {
                     BodyshopId = g.Key.Bodyshop.Id,  
                     Month = g.Key.Month,
                     MonthAllJobTotal = g.Count()
                 };

    return query2;
}

然后我会将它们拆分并转换为扩展方法：

public static class MyExtensions 
{
    public static IQueryable<Invoice> Recent(this IQueryable<Invoice> context,long? manufacturerId=null,long? regionId=null,long? vehicleId=null)
    {
        var now = DateTime.Now;
        var today = new DateTime(now.Year, now.Month, 1);
        var date1monthago = today.AddMonths(-1);
        var date2monthago = today.AddMonths(-2);
        var date3monthago = today.AddMonths(-3);
        var date4monthago = today.AddMonths(-4);
        var date5monthago = today.AddMonths(-5);
        var date6monthago = today.AddMonths(-6);
        today = TimeManager.EndOfDay(new DateTime(now.AddMonths(-1).Year, today.AddMonths(-1).Month, DateTime.DaysInMonth(now.Year, today.AddMonths(-1).Month)));             

        var query = from item in context.Invoices.AsNoTracking()
                where (item.InvoiceDate >= date12monthago && item.InvoiceDate <= today)
                && (item.InvType == "I" || item.InvType == null)
                select item;

        if (manufacturerId.HasValue && manufacturerId.Value > 0)
            query=query.Where(item=>item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(m=>m.ManufacturerId==manufacturerId));

        if (regionId.HasValue && regionId.Value > 0)
            query=query.Where(item=>item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(m=>m.RegionId==regionId.Value));

        if (vehicleTypeId.HasValue && vehicleTypeId.Value > 0)
            query=query.Where(item=>item.Repair.Job.Vehicle.Model.VehicleTypes.Any(v=>v.Id==vehicleTypeId.Value));
    return query;
}
public static IQueryable<Report> ToReport(this IQueryable<Invoice> context)
{
    return (from item in query
                 group item by new { item.InvoiceDate.Month, item.Repair.Job.Bodyshop } into g
                 select new TReport
                 {
                     BodyshopId = g.Key.Bodyshop.Id,  
                     Month = g.Key.Month,
                     MonthAllJobTotal = g.Count()
                 });

}
}

现在您可以做到以下事情：

var reports=db.Invoices.Recent.ToReport(); 

或者

var reports=db.Invoices.Recent(ManufacturerEnum.Toyota).ToReport();

- Robert McKee

Answer 2

首先，一些通用 SQL 优化技巧：

在尝试进行优化之前，您应该始终进行性能分析。性能分析的综合优势在于给您提供客观的性能描述，为您提供提示，让您开始优化工作。作为额外的好处，在一天结束时，您可以用明确的客观数字（甚至是面向幻灯片爱好者的性能图表）证明自己的辛勤工作。

正如一些人建议的那样，您可以尝试以两种不同的方式进行优化：

• 优化 SQL 计划（可能需要花费时间）

  1. 找出 SQL 查询语句。
  2. 在 SQL 管理工具中运行查询，以定义最佳执行计划（可能需要在过程中添加索引）。从那里，您应该知道优化是否足以达到您的性能标准。
  3. 研究当前执行计划，检查当前查询是否使用了最优的索引/键、连接。
  4. 修改代码或使用中间 SQL 对象（存储过程或视图）使 Linq to SQL 代码使用优化后的执行计划。

• 优化代码处理数据的方式（耗时较少的任务）

  1. 缓存（可以用几种方式实现，但原则是获取所需数据并在代码中处理）。当查询优化器无法有效地管理您的查询时（旧数据库版本/引擎效果不佳）特别有效。
  2. 编译 Link to SQL 查询。 Joe Albahari 比我讲得更好。

您可能首选哪种方法仅取决于您期望从中获得的性能提升以及您从分析会话中获得的结果。

从您的 Linq to SQL 代码的复杂性来看，生成的 SQL 查询很可能不够优化。另一方面，与远程 SQL 数据库进行 I/O 相比，Linq 在内存对象管理方面相当快。

请注意，如果您不缓存查询，它将每次获取数据。缓存可以像这样完成：

var allJobs = query.Where(x => x.Repair.Job.BodyshopId == g.Key.Bodyshop.Id).ToArray();

作为一个通用的准则，Keep It Simple Stupid（保持简单愚蠢）原则并不适用于你的查询。你可以进行一些重构工作，因为代码存在相当多的冗余。

MonthSixManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date6monthago.Month && x.InvoiceDate.Year == date6monthago.Year).GroupBy(x => x.Repair.Job).Count(),
    MonthSixJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date6monthago.Month && x.InvoiceDate.Year == date6monthago.Year).GroupBy(x => x.Repair.Job).Count(),

有一种广泛的观念认为，通过将所有代码分组到同一个方法中（类似于汇编代码），可以限制方法调用的数量，从而在性能方面表现更好。然而，事实是反直觉的。使用分而治之的原则，将方法划分为块状代码最终实际上会表现得更好，因为它更易于维护、优化、增强和重构。这正是从汇编语言到C#的软件演进的整个意义所在。

- Fab

---

当你不知道瓶颈在哪里时，如何优化代码？我经常看到人们花费大量时间优化他们的代码，而瓶颈甚至不在他们预期的地方。 - Dillen Meijboom

Answer 3

您可以实现分页来避免全部实例化结果。我的意思是，您可以实现跳过和获取linq方法。

以下是基于您的代码的简单示例：

public IList<Report> GetReport(CmsEntities context, long manufacturerId, long? regionId, long? vehicleTypeId, int pageSize, int currentPage)
        {

        //Code removed to simplify

        return  query2.Skip(pageSize * currentPage).Take(pageSize );

        }

- Fermín

---

这是我目前考虑提出的事情，他们想要看到所有东西，但也许不可能。 - Beginner

Answer 4

（注意：我基于您编辑中的查询进行了以下建议）

有几件事情您可以尝试。首先，您可以简化使用 .Select() 和 .Contains() 的方法，改用 .Any()。这样可能会生成一个更简单的 EF 查询语句，但是如果没有使用 SQL Server Profiler 或 Visual Studio Debugger 查看实际的 SQL 查询语句，很难确定。例如，将 .Select(x => x.ManufacturerId).Contains(manufacturerId) 改为 .Any(x => x.ManufacturerId == manufacturerId)。

尝试的第二件事是在SQL Server上执行第一个查询，并在应用程序中处理第二个查询。EF defers execution 延迟查询的执行，直到对结果进行枚举（例如 .ToList() 或 foreach）。因此，您可以尝试对第一个查询执行 (from item in context ... select item).ToList()，这将导致第二个查询在应用程序中执行，而不是在SQL Server上执行。 如果第二个查询中的 group by 由SQL Server执行而不是在应用程序本身中执行时会导致性能下降，则此方法会有所帮助。

然而，如果您尝试第二个建议，可能会由于item.Repair.Job.Bodyshop分组是一个virtual导航属性而导致负面影响，因为EF将不得不单独获取该对象（而不是一次性获取所有对象）。这可以通过将第一个查询更改为context.Invoices.AsNoTracking().Include("Repair.Job")或context.Invoices.AsNoTracking().Include(x => x.Repair.Job)（第二个选项在旧版EF中不可用），并将第二个查询更改为group item by new { item.InvoiceDate.Month, item.Repair.Job.BodyshopId }来缓解。

- jrivor

---

我认为你的第一个观点是至关重要的。其他观点则不那么重要，因为分组不会被转换为 SQL 的 group by 而只会被转换为排序（实际分组已经在内存中完成）。这里并不适用于 Include，因为结果是一个投影。另一个观点是，regionId.Value > 0 ? 等可以转换为 if (regionId.Value > 0) query = query.Where(item => item.Repair.Job....，这将减少数据库引擎需要做出的决策数量。 - Gert Arnold

@GertArnold 这篇博客的最后一部分似乎暗示着，对于分组，EF的SQL Server提供程序会生成SQL查询，而不是在应用程序中执行。如果没有使用.ToList()枚举查询之间的情况下，我不确定这两个查询是否被合并并在SQL服务器上执行。至于您的第二点，根据需要有条件地链接.Where()子句应该有助于查询生成。检查生成的SQL的性能是唯一确定的方法。 - jrivor

Answer 5

我建议你将它分解成更小的部分。将初始数据放入单独的列表中，然后再将它们缝合在一起。

你可以使用ToDictionary和ToLookup扩展来获得很多优势。

我敢打赌，如果你看一下发送到数据库的SQL语句，它会让人发疯的。

- sm14

Answer 6

除了消除不必要的where条件外，我会把赌注放在group by子句上。

您将item.Repair.Job.Bodyshop作为分组字段之一。每当您使用此类内容时，EF将生成一个SQL GROUP BY子句，其中包括相应表中的所有字段。我不知道您的Bodyshop实体对应的数据库表中有多少列，但无论如何，以这种方式使用它很可能不允许创建良好的SQL执行计划。

我建议您尝试以下缩减查询的等效方法：

var query = context.Invoices.AsNoTracking().Where(item =>
    (item.InvType == "I" || item.InvType == null) &&
    (item.InvoiceDate >= date12monthago && item.InvoiceDate <= today));

if (regionId.HasValue && regionId.Value > 0)
    query = query.Where(item =>
        item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(source =>
            source.ManufacturerId == manufacturerId && source.RegionId == regionId.Value));
else
    query = query.Where(item => 
        item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(source => 
            source.ManufacturerId == manufacturerId));

if (vehicleTypeId.HasValue && vehicleTypeId.Value > 0)
    query = query.Where(item =>
        item.Repair.Job.Vehicle.Model.VehicleTypes.Any(vehicleType => 
            vehicleType.Id == vehicleTypeId.Value);

var query2 = query
    .GroupBy(item => new { Month = item.InvoiceDate.Month, BodyshopId = item.Repair.Job.Bodyshop.Id })
    .Select(g => new TReport { BodyshopId = g.Key.BodyshopId, Month = g.Key.Month, MonthAllJobTotal = g.Count() });

var result = query2.ToList();

- Ivan Stoev