Linq查询超时，如何精简查询

本文关键字：查询 何精简 超时 Linq | 更新日期: 2023-09-27 18:18:33

我们的前端UI有一个过滤系统，在后端操作数百万行。它使用一个在逻辑过程中构建的IQueryable，然后一次性执行。每个单独的UI组件被组合在一起(例如，Dropdown1和Dropdown2将只返回具有共同选择的内容的行)。这不是问题。但是，Dropdown3中有两种类型的数据，并且选中的项需要一起ORd，然后与查询的其余部分进行and。

由于操作的行数量很大，所以它一直超时。由于需要进行一些额外的连接，所以这有点棘手。下面是我的代码，替换了表名:

//The end list has driver ids in it--but the data comes from two different places. Build a list of all the driver ids.
driverIds = db.CarDriversManyToManyTable.Where(
                        cd =>
                            filter.CarIds.Contains(cd.CarId) && //get driver IDs for each car ID listed in filter object
                            ).Select(cd => cd.DriverId).Distinct().ToList();
driverIds = driverIds.Concat(
                    db.DriverShopManyToManyTable.Where(ds => filter.ShopIds.Contains(ds.ShopId)) //Get driver IDs for each Shop listed in filter object
                        .Select(ds => ds.DriverId)
                        .Distinct()).Distinct().ToList();
//Now we have a list solely of driver IDs
//The query operates over the Driver table. The query is built up like this for each item in the UI. Changing from Linq is not an option.
query = query.Where(d => driverIds.Contains(d.Id));

我怎样才能简化这个查询，这样我就不必在内存中检索成千上万的id，然后将它们反馈给SQL?

Linq查询超时，如何精简查询

有几种方法可以生成单个SQL查询。它们只需要保留IQueryable<T>类型的查询部分，即不使用ToList, ToArray, AsEnumerable等强制在内存中执行和求值的方法。

一种方法是创建包含过滤id的Union查询(根据定义，它将是唯一的)，并使用join操作符将其应用于主查询:

var driverIdFilter1 = db.CarDriversManyToManyTable
    .Where(cd => filter.CarIds.Contains(cd.CarId))
    .Select(cd => cd.DriverId);
var driverIdFilter2 = db.DriverShopManyToManyTable
    .Where(ds => filter.ShopIds.Contains(ds.ShopId))
    .Select(ds => ds.DriverId);
var driverIdFilter = driverIdFilter1.Union(driverIdFilter2);
query = query.Join(driverIdFilter, d => d.Id, id => id, (d, id) => d);

另一种方法是使用两个OR-ed基于Any的条件，这将转换为EXISTS(...) OR EXISTS(...) SQL查询过滤器:

query = query.Where(d =>
    db.CarDriversManyToManyTable.Any(cd => d.Id == cd.DriverId && filter.CarIds.Contains(cd.CarId))
    ||
    db.DriverShopManyToManyTable.Any(ds => d.Id == ds.DriverId && filter.ShopIds.Contains(ds.ShopId))
);

这个问题的答案很复杂，有很多方面，单独来看，可能对你的具体情况有帮助，也可能没有帮助。

首先，考虑使用分页。.Skip(PageNum * PageSize).Take(PageSize)我怀疑你的用户需要在前端一次看到数百万行。只给他们100个，或者你认为合理的更小的数字。

您已经提到需要使用连接来获取所需的数据。这些连接可以在形成IQueryable(实体框架)时完成，而不是在内存中(与对象的链接)。阅读linq中的连接语法。

然而，在LINQ中执行显式连接并不是最佳实践，特别是当您自己设计数据库时。如果您正在对实体进行数据库第一代处理，请考虑在表上放置外键约束。这将允许数据库优先的实体生成选择这些，并为您提供导航属性，这将大大简化您的代码。

如果您对数据库设计没有任何控制或影响，那么我建议您先用SQL构造查询，看看它是如何执行的。在那里进行优化，直到获得所需的性能，然后将其转换为使用显式连接作为最后手段的实体框架linq查询。

为了加快这样的查询速度，您可能需要对连接的所有"key"列执行索引。找出需要哪些索引来提高性能的最佳方法是，将EF linq生成的SQL查询带到SQL Server Management Studio中。在此基础上，更新生成的SQL，为@p参数提供一些预定义的值，以作为示例。完成这些操作后，右键单击查询，使用显示估计执行计划或包含实际执行计划。如果索引可以提高查询性能，那么这个特性很有可能会告诉您这一点，甚至为您提供创建所需索引的脚本。

在我看来，使用LINQ扩展的实例版本在完成之前创建了几个集合。使用from语句版本应该可以减少一些:

driveIds = (from var record in db.CarDriversManyToManyTable
            where filter.CarIds.Contains(record.CarId)
            select record.DriverId).Concat
            (from var record in db.DriverShopManyToManyTable
             where filter.ShopIds.Contains(record.ShopId)
             select record.DriverId).Distinct()

使用groupby扩展名也会比查询每个驾驶员Id提供更好的性能。

相关文章：