sql-server - 在 Linq 中查询速度慢，在 LinqPad、SQL Management Studio 和 SQL Profiler 中查询速度快

Question

我有我正在使用的这个 linq 查询，当我在我的 asp.net 应用程序中运行它时需要 50 秒才能运行，但是在 LinqPad 和 Sql Management Studio 中执行相同的查询需要 500 毫秒。

我什至从 SQL Profiler 中获取查询并在 SQL Management Studio 中再次运行它，大约需要 500 毫秒。 Linq 可以做什么开销，额外的 49s？

下面是引用代码，感谢您的帮助。

var rCampaign =
    (from a in db.AdCreative
     join h in db.AdHit on a.ID equals h.AdID into gh
     join l in db.AdGroup_Location on a.AdGroupID equals l.AdGroupID into gj
     from subloc in gj.DefaultIfEmpty()
     from subhits in gh.DefaultIfEmpty()
     where a.AdGroup.AdHost.Select(q => q.ID).Contains(rPlatform.ID) &&
           a.AdGroup.AdPublisher.Select(q => q.ID).Contains(rPublisher.ID) &&
           a.AdDimensionID == AdSize &&
           a.AdGroup.Campaign.Starts <= rNow &&
           a.AdGroup.Campaign.Ends >= rNow &&
           subhits.HitType == 1 &&
           (subloc == null || subloc.LocationID == rLocationID)
     select new {
         ID = a.ID,
         Name = a.Name,
         Spent = (subhits.AdDimension != null) ? ((double)subhits.AdDimension.Credit / 1000) : 0,
         CampaignID = a.AdGroup.Campaign.ID,
         CampaignName = a.AdGroup.Campaign.Name,
         CampaignBudget = a.AdGroup.Campaign.DailyBudget
     }).GroupBy(adgroup => adgroup.ID)
       .Select(adgroup => new {
           ID = adgroup.Key,
           Name = adgroup.FirstOrDefault().Name,
           Spent = adgroup.Sum(q => q.Spent),
           CampaignID = adgroup.FirstOrDefault().CampaignID,
           CampaignName = adgroup.FirstOrDefault().CampaignName,
           CampaignBudget = adgroup.FirstOrDefault().CampaignBudget,
       })
      .GroupBy(q => q.CampaignID)
      .Select(campaigngroup => new {
          CampaignID = campaigngroup.Key,
          DailyBudget = campaigngroup.FirstOrDefault().CampaignBudget,
          Consumed = campaigngroup.Sum(q => q.Spent),
          RemainningCredit = campaigngroup.FirstOrDefault().CampaignBudget - campaigngroup.Sum(q => q.Spent),
          Ads = campaigngroup.Select(ag => new {
              ID = ag.ID,
              Name = ag.Name,
              Spent = ag.Spent
          }).OrderBy(q => q.Spent)
      })
      .Where(q => q.Consumed <= q.DailyBudget).OrderByDescending(q => q.RemainningCredit).First();

Answer 1

有几种方法可以简化该查询:

• select into 可让您将其全部保留在查询语法中。
• join ... into/from/DefaultIfMany 构造实现了左连接，可以替换为 join ... into 表示组连接的结构。
• 一些接近末尾的组不能为空，所以FirstOrDefault是不必要的。
• 一些 where 条件可以在查询变得复杂之前移到顶部。

这是我的尝试。修改很重要，因此可能需要一些调试:

var rCampaign = ( 
    from a in db.AdCreative

    where a.AdDimensionID == AdSize &&
          a.AdGroup.Campaign.Starts <= rNow &&
          a.AdGroup.Campaign.Ends >= rNow &&
          a.AdGroup.AdHost.Select(q => q.ID).Contains(rPlatform.ID) &&
          a.AdGroup.AdPublisher.Select(q => q.ID).Contains(rPublisher.ID)

    join hit in db.AdHit.Where(h => h.HitType == 1 && h.LocationID == rLocationID)
        on a.ID equals hit.AdID
        into hits

    join loc in db.AdGroup_Location
        on a.AdGroupID equals loc.AdGroupID
        into locs

    where !locs.Any() || locs.Any(l => l.LocationID == rLocationID)

    select new {
        a.ID,
        a.Name,
        Spent = hits.Sum(h => h.AdDimension.Credit / 1000) ?? 0,
        CampaignID = a.AdGroup.Campaign.ID,
        CampaignName = a.AdGroup.Campaign.Name,
        CampaignBudget = a.AdGroup.Campaign.DailyBudget,
    } into adgroup
    group adgroup by adgroup.CampaignID into campaigngroup
    select new
    {
        CampaignID = campaigngroup.Key,
        DailyBudget = campaigngroup.First().CampaignBudget,
        Consumed = campaigngroup.Sum(q => q.Spent),
        RemainingCredit = campaigngroup.First().CampaignBudget - campaigngroup.Sum(q => q.Spent),
        Ads = campaigngroup.Select(ag => new {
            ag.ID,
            ag.Name,
            ag.Spent,
        }).OrderBy(q => q.Spent)
    } into q
    where q.Consumed <= q.DailyBudget
    orderby q.RemainingCredit desc)
    .First()