主页 / coding / 问题 / 79562096
Accepted
mnol
Asked: 2025-04-08 20:55:46 +0800 CST

如何让 LINQ 查询更快

  • 772

运行 NbLinksExpirationStatus 大约需要 12 秒，这对于我想要实现的目标来说不够快。这可能是 Contains() 的问题吗？有什么方法可以让它执行得更快吗？我正在使用 EF6 和 SQL Server。请问我在这种情况下是否最好直接使用 sqlquery？

笔记 ：

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

存在查询的方法：

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

        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; }
}
c#

2 个回答

  1. Best Answer

    您无需执行两个单独的查询，而是可以在一个查询中聚合并获取计数。

    还

    • 使用 valuetuples 而不是旧式的Tuple。
    • Contains在连接之前推动第二个，以提高效率。
    • async尽可能使用。
    • 不要使用ToList，而是在服务器上进行聚合。
    public static async Task<(int aboutToExpireCount, int expiredCount)> NbLinksExpirationStatus(HashSet<int>? oneEntities, HashSet<int>? twoEntities)
{
    using DbEntities context = new DbEntities();
    int aboutToExpireValue = Constants.LinkStatusAboutToExpire;
    int expiredValue = Constants.LinkStatusExpired;

    oneEntities ??= new HashSet<int>();
    twoEntities ??= new HashSet<int>();

    var counts = await (
        from t in context.ONE_DISTRIBUTION_STATUS
        join o in context.TWO_DISTRIBUTION_STATUS.Where(two => twoEntities.Contains(two.ID_TWO))
          on t.ID_ent equals o.ID_ent into joined
        from o in joined.DefaultIfEmpty()
        where oneIds.Contains(t.ID_ONE)
        group new { t, o } by 1 into grp  // group by a constant value
        select new {
            aboutToExpireCount =  grp.Count(tuple =>
                tuple.t.LINK_STATUS_EXPIRATION == aboutToExpireValue || tuple.o.LINK_STATUS_EXPIRATION == aboutToExpireValue),
            expiredCount = grp.Count(tuple =>
                tuple.t.LINK_STATUS_EXPIRATION == expiredValue || tuple.o.LINK_STATUS_EXPIRATION == expiredValue),
        }
    ).FirstAsync(); 

    return (counts.aboutToExpireCount, counts.expiredCount);
}
    • 2

  2. ToList()只需删除末尾的 ，就很有可能获得巨大的性能提升joinedQuery。该调用会导致joinedQuery执行，并（根据您的评论）向服务器发送数十万行数据。删除它之后，每次 调用Count()都会发送一个单独的请求，但每个请求都允许 SQL Server 优化查询并仅返回一个数据点，这几乎肯定会使速度提高几个数量级。

    我强烈建议将诸如ToList()和之类的链式方法放在Count()它们自己的行上，因为当它们位于像这样的长代码行末尾时很容易错过它们。

    其他优化，例如将查询分组为单次往返、使用异步等，目前可能还为时过早。还有很多其他选项可以提高性能，但它们在代码可维护性方面都存在缺陷，而且可能不会带来太大的改进。

    • 0

相关问题