主页 / user-22063464

mnol's questions

Martin Hope
mnol
Asked: 2025-04-08 20:55:46 +0800 CST
  • 5

运行 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#