如何优化EF6中LINQ查询？NbLinksExpirationStatus性能调优

我用EF6和SQL Server开发，NbLinksExpirationStatus方法执行耗时约12秒，达不到性能要求。一开始怀疑是Contains()导致的性能问题，换成原生SQL查询后性能还是没提升，想问问有什么优化办法？到底要不要改用原生SQL？

前置代码 #

HashSet<int> oneEntitiesPerimeter = oneEquipment.Select(x => x.ID_ONE).ToHashSet();
HashSet<int> twoEntitiesPerimeter = twoEquipment.Select(x => x.ID_TWO).ToHashSet();

原EF查询方法 #

public static Tuple<int, int> NbLinksExpirationStatus(HashSet<int> oneEntitiesPerimeter, HashSet<int> twoEntitiesPerimeter)
{
    using (DbEntities context = new DbEntities())
    {
        int aboutToExpireValue = Constants.LinkStatusAboutToExpire; 
        int expiredValue = Constants.LinkStatusExpired; 

        var oneIds = oneEntitiesPerimeter ?? new HashSet<int>();
        var twoIds = twoEntitiesPerimeter ?? new HashSet<int>();

        var joinedQuery = (from t in context.ONE_DISTRIBUTION_STATUS
                            join o in context.TWO_DISTRIBUTION_STATUS on t.ID_ent equals o.ID_ent into joined
                            from o in joined.DefaultIfEmpty()
                            where oneIds.Contains(t.ID_ONE) && (o == null || twoIds.Contains(o.ID_TWO))
                            select new { oneExpirationStatus = t.LINK_STATUS_EXPIRATION, twoExpirationStatus = o.LINK_STATUS_EXPIRATION }).ToList();

        var aboutToExpireCount = joinedQuery.Where(j =>
            j.oneExpirationStatus == aboutToExpireValue || j.twoExpirationStatus == aboutToExpireValue).Count();

        var expiredCount = joinedQuery.Where(j =>
            j.oneExpirationStatus == expiredValue || j.twoExpirationStatus == expiredValue).Count();

        return new Tuple<int, int>(aboutToExpireCount, expiredCount);
    }
}

尝试的原生SQL查询代码 #

string oneIds = "NULL";            
if(oneEntitiesPerimeter != null && oneEntitiesPerimeter.Count > 0)
{
    oneIds = string.Join(",", oneEntitiesPerimeter);
}
string twoIds = "NULL";
if (twoEntitiesPerimeter != null && twoEntitiesPerimeter.Count > 0)
{
    twoIds = string.Join(",", twoEntitiesPerimeter);
}

string sqlQuery = "SELECT " +
    "COALESCE(SUM(CASE WHEN t.LINK_STATUS_EXPIRATION = " + aboutToExpireValue + " OR COALESCE(o.LINK_STATUS_EXPIRATION, '')= " + aboutToExpireValue + " THEN 1 ELSE 0 END), 0) AS AboutToExpire," +
    "COALESCE(SUM(CASE WHEN t.LINK_STATUS_EXPIRATION = " + expiredValue + " OR COALESCE(o.LINK_STATUS_EXPIRATION, '') = " + expiredValue + " THEN 1 ELSE 0 END), 0) AS Expired "+ 
    "FROM ONE_DISTRIBUTION_STATUS t LEFT JOIN TWO_DISTRIBUTION_STATUS o ON t.ID_ent = o.ID_ent WHERE t.ID_ONE in (" + oneIds + ") AND (o.ID_TWO in (" + twoIds + ") OR o.ID_TWO IS NULL)";

var counter = context.Database.SqlQuery<LinkExpirationStatusWidget>(sqlQuery).FirstOrDefault();           
result = new Tuple<int, int>(counter.AboutToExpire, counter.Expired);

结果实体类 #

public class LinkExpirationStatusWidget
{
    /// <summary>
    /// Gets or sets count of links about to expire
    /// </summary>
    /// <value>About To Expire count</value>
    public int AboutToExpire { get; set; }

    /// <summary>
    /// Gets or sets count of links expired
    /// </summary>
    /// <value>Expired count</value>
    public int Expired { get; set; }
}

优化建议 #

1. 先排查数据库层面的问题 #

• 检查索引：确保ONE_DISTRIBUTION_STATUS表的ID_ONE、ID_ent、LINK_STATUS_EXPIRATION字段，以及TWO_DISTRIBUTION_STATUS表的ID_ent、ID_TWO、LINK_STATUS_EXPIRATION字段都创建了合适的索引。尤其是关联字段ID_ent和过滤字段ID_ONE、ID_TWO，没有索引会导致全表扫描，这是性能瓶颈的常见原因。
• 查看执行计划：在SQL Server Management Studio中执行你的原生SQL，查看实际执行计划，定位全表扫描、键查找等高成本操作，针对性调整索引。

2. 优化EF查询逻辑 #

• 避免提前调用ToList()：原代码中joinedQuery.ToList()会把所有符合条件的数据加载到内存后再统计，数据量大时会严重拖慢速度。应该把统计逻辑推到数据库执行，修改EF查询直接返回统计结果：

public static Tuple<int, int> NbLinksExpirationStatus(HashSet<int> oneEntitiesPerimeter, HashSet<int> twoEntitiesPerimeter)
{
    using (DbEntities context = new DbEntities())
    {
        int aboutToExpireValue = Constants.LinkStatusAboutToExpire;
        int expiredValue = Constants.LinkStatusExpired;

        var oneIds = oneEntitiesPerimeter ?? new HashSet<int>();
        var twoIds = twoEntitiesPerimeter ?? new HashSet<int>();

        var query = from t in context.ONE_DISTRIBUTION_STATUS
                    join o in context.TWO_DISTRIBUTION_STATUS on t.ID_ent equals o.ID_ent into joined
                    from o in joined.DefaultIfEmpty()
                    where oneIds.Contains(t.ID_ONE) && (o == null || twoIds.Contains(o.ID_TWO))
                    select new 
                    { 
                        IsAboutToExpire = (t.LINK_STATUS_EXPIRATION == aboutToExpireValue || (o != null && o.LINK_STATUS_EXPIRATION == aboutToExpireValue)),
                        IsExpired = (t.LINK_STATUS_EXPIRATION == expiredValue || (o != null && o.LINK_STATUS_EXPIRATION == expiredValue))
                    };

        var result = query.Aggregate(
            new { AboutToExpire = 0, Expired = 0 },
            (acc, item) => new 
            { 
                AboutToExpire = acc.AboutToExpire + (item.IsAboutToExpire ? 1 : 0),
                Expired = acc.Expired + (item.IsExpired ? 1 : 0)
            });

        return new Tuple<int, int>(result.AboutToExpire, result.Expired);
    }
}

这样EF会将统计逻辑转换为SQL在数据库执行，大幅减少内存加载和网络传输的数据量。

3. 优化原生SQL的写法 #

• 改用参数化查询：你的原生SQL用字符串拼接oneIds和twoIds，不仅有SQL注入风险，还会导致SQL Server无法复用执行计划。推荐使用表值参数传递集合：

using (DbEntities context = new DbEntities())
{
    int aboutToExpireValue = Constants.LinkStatusAboutToExpire;
    int expiredValue = Constants.LinkStatusExpired;

    var oneIds = oneEntitiesPerimeter ?? new HashSet<int>();
    var twoIds = twoEntitiesPerimeter ?? new HashSet<int>();

    string sqlQuery = @"
        SELECT 
            COALESCE(SUM(CASE WHEN t.LINK_STATUS_EXPIRATION = @AboutToExpire OR (o.LINK_STATUS_EXPIRATION = @AboutToExpire AND o.ID_TWO IS NOT NULL) THEN 1 ELSE 0 END), 0) AS AboutToExpire,
            COALESCE(SUM(CASE WHEN t.LINK_STATUS_EXPIRATION = @Expired OR (o.LINK_STATUS_EXPIRATION = @Expired AND o.ID_TWO IS NOT NULL) THEN 1 ELSE 0 END), 0) AS Expired 
        FROM ONE_DISTRIBUTION_STATUS t 
        LEFT JOIN TWO_DISTRIBUTION_STATUS o ON t.ID_ent = o.ID_ent 
        WHERE t.ID_ONE IN @OneIds 
          AND (o.ID_TWO IN @TwoIds OR o.ID_TWO IS NULL)";

    var parameters = new List<SqlParameter>
    {
        new SqlParameter("@AboutToExpire", aboutToExpireValue),
        new SqlParameter("@Expired", expiredValue),
        new SqlParameter("@OneIds", SqlDbType.Structured) { Value = oneIds.ToDataTable("ID") },
        new SqlParameter("@TwoIds", SqlDbType.Structured) { Value = twoIds.ToDataTable("ID") }
    };

    var counter = context.Database.SqlQuery<LinkExpirationStatusWidget>(sqlQuery, parameters.ToArray()).FirstOrDefault();
    return new Tuple<int, int>(counter?.AboutToExpire ?? 0, counter?.Expired ?? 0);
}

需要添加一个扩展方法将HashSet<int>转换为DataTable：

public static DataTable ToDataTable<T>(this IEnumerable<T> items, string columnName)
{
    DataTable table = new DataTable();
    table.Columns.Add(columnName, typeof(T));
    foreach (var item in items)
    {
        table.Rows.Add(item);
    }
    return table;
}

同时在SQL Server中创建对应的表值类型：

CREATE TYPE IntList AS TABLE (ID int);

参数化查询能让SQL Server缓存执行计划，提升重复查询的性能，同时彻底避免SQL注入风险。

4. 是否改用原生SQL？ #

如果EF生成的SQL执行计划不够高效，或者需要实现复杂的查询逻辑，参数化的原生SQL是更优选择。但优先优化EF查询写法和数据库索引，多数情况下性能问题不是EF本身导致的，而是索引缺失或查询逻辑不合理造成的。

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