LINQ查询性能远逊于等价SQL，求优化方案

我写了一段LINQ查询，用SQL Profiler抓取其生成的T-SQL并查看统计信息后，发现性能极差，执行计划复杂到难以解读。但等价的原生SQL查询在读取性能上要好几个数量级。

想问下有没有办法优化该LINQ查询的性能？还是只能接受现状改用存储过程？

补充说明：输入包含235个ID，SQL中用子查询获取ID仅为操作便利，实际所用ID与LINQ中的完全一致。

LINQ代码 #

var itemchangeList = (from itm in db.AS_ITM
                      join rsi in db.AS_ITM_RTL_STR on itm.ID_ITM equals rsi.ID_ITM
                      join sti in db.AS_ITM_STK on itm.ID_ITM equals sti.ID_ITM
                      join pi in db.ID_IDN_PS on itm.ID_ITM equals pi.ID_ITM
                      join ics in db.AS_ITM_CustomFilters on itm.ID_ITM equals ics.ItemId into icsGroup
                      from ics in icsGroup.DefaultIfEmpty()  // 左连接
                      join cs in db.CustomFilters on ics.CustomFilterId equals cs.CustomFilterId into csGroup
                      from cs in csGroup.DefaultIfEmpty()    // 左连接
                      where rsi.ID_CPY == WsKey.CompanyID && rsi.ID_STR_RT == WsKey.StoreID && itemIds.Contains(itm.ID_ITM)
                      group new { itm, sti, pi, cs } by itm.ID_ITM into grouped
                      select new Contexts.Items.PendingItemChange
                      {
                          ItemId = grouped.Key,
                          AuthForSale = grouped.FirstOrDefault().itm.FL_AZN_FR_SLS == "1" ? true : false,
                          SaleType = null,
                          StoreManaged = grouped.FirstOrDefault().itm.StoreManaged,
                          ApacsCode = null,
                          ProhibitEmployeeDiscount = grouped.FirstOrDefault().pi.FL_DSC_EM_ALW == "1" ? true : false,
                          ProhibitPriceModification = grouped.FirstOrDefault().itm.FL_ITM_PRC_MOD == "1" ? true : false,
                          RequiredMargin = grouped.FirstOrDefault().sti.Margin,
                          ProductTagsList = grouped.Select(x => x.cs.CustomFilterId.ToString()).Distinct().ToList(),
                          EligibleForOnlineDelivery = grouped.FirstOrDefault().sti.EligibleForOnlineDelivery,
                          DepositItemId = grouped.FirstOrDefault().pi.ID_ITM_DS,
                          Analgesic = grouped.FirstOrDefault().itm.Analgesic == "1" ? true : false,
                          CountryOfOrigin = grouped.FirstOrDefault().sti.CountryOfOrigin,
                          Brand = grouped.FirstOrDefault().sti.Brand,
                          MiscInfo = grouped.FirstOrDefault().sti.MiscText
                      }).ToList();

等价原生SQL代码 #

SELECT
    itm.ID_ITM AS ItemId,
    CASE WHEN MAX(CAST(itm.FL_AZN_FR_SLS AS INT)) = 1 THEN 'true' ELSE 'false' END AS AuthForSale,
    CASE WHEN MAX(CAST(itm.StoreManaged AS INT)) = 1 THEN 'true' ELSE 'false' END AS StoreManaged,
    CASE WHEN MAX(CAST(pi.FL_DSC_EM_ALW AS INT)) = 1 THEN 'true' ELSE 'false' END AS ProhibitEmployeeDiscount,
    CASE WHEN MAX(CAST(itm.FL_ITM_PRC_MOD AS INT)) = 1 THEN 'true' ELSE 'false' END AS ProhibitPriceModification,
    MAX(sti.Margin) AS RequiredMargin,
    CASE WHEN MAX(CAST(sti.EligibleForOnlineDelivery AS INT)) = 1 THEN 'true' ELSE 'false' END AS EligibleForOnlineDelivery,
    MAX(pi.ID_ITM_DS) AS DepositItemId,
    CASE WHEN MAX(CAST(itm.Analgesic AS INT)) = 1 THEN 'true' ELSE 'false' END AS Analgesic,
    MAX(sti.CountryOfOrigin) AS CountryOfOrigin,
    MAX(sti.Brand) AS Brand,
    MAX(sti.MiscText) AS MiscInfo,
    STRING_AGG(cs.CustomFilterId, ',') AS ProductTagsList
FROM AS_ITM itm
JOIN AS_ITM_RTL_STR rsi ON itm.ID_ITM = rsi.ID_ITM
JOIN AS_ITM_STK sti ON itm.ID_ITM = sti.ID_ITM
JOIN ID_IDN_PS pi ON itm.ID_ITM = pi.ID_ITM
LEFT JOIN AS_ITM_CustomFilters ics ON itm.ID_ITM = ics.ItemId
LEFT JOIN CustomFilters cs ON ics.CustomFilterId = cs.CustomFilterId
WHERE itm.ID_ITM IN (SELECT ItemId FROM PendingItemChanges)
GROUP BY itm.ID_ITM;

性能统计 #

• LINQ查询性能统计：
  
• 原生SQL查询性能统计：
  

1. 替换FirstOrDefault()为聚合函数，对齐原生SQL逻辑 #

LINQ中分组后用grouped.FirstOrDefault()获取字段，会导致EF生成低效的嵌套查询——因为它无法确认同一分组内这些字段值唯一。而原生SQL用MAX()聚合（同一ID_ITM下值唯一，聚合结果与FirstOrDefault()一致），可以让EF生成更高效的SQL：

// 示例：替换AuthForSale的获取方式
AuthForSale = grouped.Max(x => x.itm.FL_AZN_FR_SLS) == "1" ? true : false,
// 其他字段同理修改
RequiredMargin = grouped.Max(x => x.sti.Margin),
DepositItemId = grouped.Max(x => x.pi.ID_ITM_DS),
CountryOfOrigin = grouped.Max(x => x.sti.CountryOfOrigin),
// ... 其余字段均用Max/Min聚合

2. 优化标签列表生成，在数据库端完成聚合 #

LINQ中Select().Distinct().ToList()会把所有标签数据拉取到内存再处理，而原生SQL用STRING_AGG在数据库端完成聚合。可使用EF的内置函数实现：

// EF Core 2.1+ 可用EF.Functions.StringAgg
ProductTagsList = grouped
    .Where(x => x.cs != null)
    .Select(x => x.cs.CustomFilterId.ToString())
    .Aggregate((a, b) => $"{a},{b}")

3. 提前过滤数据，减少关联数据量 #

将itemIds.Contains(itm.ID_ITM)和门店、公司ID的过滤条件提前到关联前，让数据库更早排除无用数据：

var filteredRsi = db.AS_ITM_RTL_STR.Where(r => r.ID_CPY == WsKey.CompanyID && r.ID_STR_RT == WsKey.StoreID);
var itemchangeList = (from itm in db.AS_ITM.Where(i => itemIds.Contains(i.ID_ITM))
                      join rsi in filteredRsi on itm.ID_ITM equals rsi.ID_ITM
                      // ... 其余关联逻辑不变

4. 关闭实体跟踪，减少不必要开销 #

因为仅需读取数据，无需跟踪实体状态，添加AsNoTracking()可降低EF的内存和性能消耗：

var itemchangeList = (from itm in db.AS_ITM.AsNoTracking()
                      // ... 其余代码不变
                      ).ToList();

5. 若以上优化无效，直接使用原生SQL或存储过程 #

如果调整LINQ后性能仍未达标，可直接在EF中执行原生SQL：

var itemchangeList = db.PendingItemChange.FromSqlRaw(@"
    SELECT
        itm.ID_ITM AS ItemId,
        CASE WHEN MAX(CAST(itm.FL_AZN_FR_SLS AS INT)) = 1 THEN 'true' ELSE 'false' END AS AuthForSale,
        -- ... 其余原生SQL内容
        WHERE itm.ID_ITM IN ({0})
", string.Join(",", itemIds)).ToList();

或创建存储过程，通过EF调用，完全复用原生SQL的高效执行计划。

内容的提问来源于stack exchange，提问作者BMills