SQL Server现金支付报表视图查询加载过慢求助

以下是用于展示现金支付报表的视图查询语句，目前数据加载耗时过长。经排查，左连接中的子查询是主要性能瓶颈，相关表已创建非聚集索引，但尝试将表移出子查询后无法保证数据准确性，现寻求可行的查询优化方案。

SELECT
    Billing_AccountPaymentDate.DueDate,
    SUM(Billing_AccountCharge.NetAmount) AS NetCharges,
    ISNULL(SUM(AccountChargePayment.SchoolPayments + AccountChargePayment.SchoolRemittances + AccountChargePayment.SchoolRemittancesPending), 0) / SUM(Billing_AccountCharge.NetAmount) AS PercentCollected,
    SUM(Billing_AccountCharge.NetAmount - ISNULL(AccountChargePayment.SchoolPayments + AccountChargePayment.SchoolRemittances + AccountChargePayment.SchoolRemittancesPending, 0)) AS RemainingBalance,
    Billing_AccountPaymentDate.RemittanceEffectiveDate,
    Billing_Account.SchoolId,
    ISNULL(SUM(AccountChargePayment.SchoolPayments), 0) AS SchoolPayments,
    ISNULL(SUM(AccountChargePayment.SchoolRemittances), 0) AS SchoolRemittances,
    ISNULL(SUM(AccountChargePayment.SchoolRemittancesPending), 0) AS SchoolRemittancesPending,
    Billing_Account.SchoolYearId,
    ISNULL(SUM(AccountChargePayment.SchoolPayments + AccountChargePayment.SchoolRemittances), 0) AS TotalReceipts
FROM
    Billing_AccountCharge
INNER JOIN
    Billing_AccountInvoice ON
    Billing_AccountInvoice.AccountInvoiceId = Billing_AccountCharge.AccountInvoiceId
INNER JOIN
    Billing_Account ON
    Billing_Account.AccountId = Billing_AccountInvoice.AccountId
INNER JOIN
    Billing_PaymentMethod ON
    Billing_PaymentMethod.PaymentMethodId = CASE WHEN Billing_AccountInvoice.AutomaticPaymentEligible = 1 THEN Billing_Account.PaymentMethodId ELSE 3 END -- Send Statements
INNER JOIN
    Billing_AccountPaymentDate ON
    Billing_AccountPaymentDate.AccountPaymentMethodId = Billing_PaymentMethod.AnticipatedAccountPaymentMethodId AND
    Billing_AccountPaymentDate.DueDate = Billing_AccountInvoice.DueDate AND
    Billing_AccountPaymentDate.HoldForFee = Billing_Account.HoldPaymentForFee
INNER JOIN
    Billing_ChargeItem ON
    Billing_ChargeItem.ChargeItemId = Billing_AccountCharge.ChargeItemId
LEFT OUTER JOIN
    (
        SELECT
            Billing_AccountChargePayment.AccountChargeId,
            SUM(CASE WHEN Billing_AccountPayment.AccountPaymentTypeId = 9 THEN Billing_AccountChargePayment.Amount ELSE 0 END) AS SchoolPayments,
            SUM(CASE WHEN Billing_AccountChargePayment.SchoolRemittanceId IS NOT NULL THEN Billing_AccountChargePayment.Amount ELSE 0 END) AS SchoolRemittances,
            SUM(CASE WHEN Billing_AccountChargePayment.SchoolRemittanceId IS NULL AND Billing_AccountPayment.AccountPaymentTypeId <> 9 THEN Billing_AccountChargePayment.Amount ELSE 0 END) AS SchoolRemittancesPending
        FROM
            Billing_AccountChargePayment
        INNER JOIN
            Billing_AccountPayment ON
            Billing_AccountPayment.AccountPaymentId = Billing_AccountChargePayment.AccountPaymentId
        GROUP BY
            Billing_AccountChargePayment.AccountChargeId
    ) AccountChargePayment ON
    AccountChargePayment.AccountChargeId = Billing_AccountCharge.AccountChargeId
WHERE
    Billing_AccountInvoice.AccountInvoiceStatusId <> 4 AND -- Voided
    Billing_ChargeItem.RemitToSchool = 1
    AND Billing_Account.[SchoolId] = 6  --hard code in a school with data
    AND Billing_Account.[SchoolYearId] = 12   --hard code in a school year with data
GROUP BY
    Billing_AccountPaymentDate.DueDate,
    Billing_AccountPaymentDate.RemittanceEffectiveDate,
    Billing_Account.SchoolId,
    Billing_Account.SchoolYearId
HAVING
    SUM(Billing_AccountCharge.NetAmount) <> 0
ORDER BY Billing_AccountPaymentDate.DueDate ASC

优化建议 #

• 缩小子查询数据范围：先从主查询中筛选出符合条件的AccountChargeId集合，再在子查询中仅处理这些ID的数据，避免子查询扫描全表。比如将过滤后的AccountChargeId存入临时表，再关联子查询：

  -- 创建临时表存储需要的AccountChargeId
  SELECT bac.AccountChargeId
  INTO #FilteredCharges
  FROM Billing_AccountCharge bac
  INNER JOIN Billing_AccountInvoice bai ON bai.AccountInvoiceId = bac.AccountInvoiceId
  INNER JOIN Billing_Account ba ON ba.AccountId = bai.AccountId
  INNER JOIN Billing_ChargeItem bci ON bci.ChargeItemId = bac.ChargeItemId
  WHERE bai.AccountInvoiceStatusId <> 4 
    AND bci.RemitToSchool = 1
    AND ba.SchoolId = 6 
    AND ba.SchoolYearId = 12;
  
  -- 子查询关联临时表过滤数据
  SELECT
      bacp.AccountChargeId,
      SUM(CASE WHEN bap.AccountPaymentTypeId = 9 THEN bacp.Amount ELSE 0 END) AS SchoolPayments,
      SUM(CASE WHEN bacp.SchoolRemittanceId IS NOT NULL THEN bacp.Amount ELSE 0 END) AS SchoolRemittances,
      SUM(CASE WHEN bacp.SchoolRemittanceId IS NULL AND bap.AccountPaymentTypeId <> 9 THEN bacp.Amount ELSE 0 END) AS SchoolRemittancesPending
  FROM Billing_AccountChargePayment bacp
  INNER JOIN Billing_AccountPayment bap ON bap.AccountPaymentId = bacp.AccountPaymentId
  INNER JOIN #FilteredCharges fc ON fc.AccountChargeId = bacp.AccountChargeId
  GROUP BY bacp.AccountChargeId;
  

• 优化子查询的覆盖索引：为Billing_AccountChargePayment创建包含AccountChargeId、AccountPaymentId、Amount、SchoolRemittanceId的非聚集覆盖索引；为Billing_AccountPayment创建包含AccountPaymentId、AccountPaymentTypeId的非聚集覆盖索引，让子查询无需回表即可获取所有所需数据。

• 重构JOIN条件中的CASE逻辑：原查询中Billing_PaymentMethod的JOIN条件使用CASE可能导致索引失效，可拆分为两个分支用UNION ALL合并，利用索引提升性能：

  -- 拆分AutomaticPaymentEligible=1和非1的情况
  SELECT ... -- 原SELECT全部内容
  FROM Billing_AccountCharge
  INNER JOIN Billing_AccountInvoice ON ...
  INNER JOIN Billing_Account ON ...
  INNER JOIN Billing_PaymentMethod ON Billing_PaymentMethod.PaymentMethodId = Billing_Account.PaymentMethodId
  INNER JOIN Billing_AccountPaymentDate ON ...
  INNER JOIN Billing_ChargeItem ON ...
  LEFT OUTER JOIN ... -- 子查询内容
  WHERE Billing_AccountInvoice.AutomaticPaymentEligible = 1
    AND ... -- 其他WHERE条件
  GROUP BY ... -- 原GROUP BY内容
  HAVING ... -- 原HAVING内容
  
  UNION ALL
  
  SELECT ... -- 原SELECT全部内容
  FROM Billing_AccountCharge
  INNER JOIN Billing_AccountInvoice ON ...
  INNER JOIN Billing_Account ON ...
  INNER JOIN Billing_PaymentMethod ON Billing_PaymentMethod.PaymentMethodId = 3
  INNER JOIN Billing_AccountPaymentDate ON ...
  INNER JOIN Billing_ChargeItem ON ...
  LEFT OUTER JOIN ... -- 子查询内容
  WHERE Billing_AccountInvoice.AutomaticPaymentEligible <> 1
    AND ... -- 其他WHERE条件
  GROUP BY ... -- 原GROUP BY内容
  HAVING ... -- 原HAVING内容
  ORDER BY Billing_AccountPaymentDate.DueDate ASC
  

• 改用CTE替代子查询：将子查询改为CTE，部分场景下SQL优化器会生成更优的执行计划，同时保持逻辑清晰：

  WITH AccountChargePayment AS (
      SELECT
          bacp.AccountChargeId,
          SUM(CASE WHEN bap.AccountPaymentTypeId = 9 THEN bacp.Amount ELSE 0 END) AS SchoolPayments,
          SUM(CASE WHEN bacp.SchoolRemittanceId IS NOT NULL THEN bacp.Amount ELSE 0 END) AS SchoolRemittances,
          SUM(CASE WHEN bacp.SchoolRemittanceId IS NULL AND bap.AccountPaymentTypeId <> 9 THEN bacp.Amount ELSE 0 END) AS SchoolRemittancesPending
      FROM Billing_AccountChargePayment bacp
      INNER JOIN Billing_AccountPayment bap ON bap.AccountPaymentId = bacp.AccountPaymentId
      INNER JOIN (
          SELECT bac.AccountChargeId
          FROM Billing_AccountCharge bac
          INNER JOIN Billing_AccountInvoice bai ON bai.AccountInvoiceId = bac.AccountInvoiceId
          INNER JOIN Billing_Account ba ON ba.AccountId = bai.AccountId
          INNER JOIN Billing_ChargeItem bci ON bci.ChargeItemId = bac.ChargeItemId
          WHERE bai.AccountInvoiceStatusId <> 4 
            AND bci.RemitToSchool = 1
            AND ba.SchoolId = 6 
            AND ba.SchoolYearId = 12
      ) fc ON fc.AccountChargeId = bacp.AccountChargeId
      GROUP BY bacp.AccountChargeId
  )
  SELECT ... -- 原SELECT全部内容
  FROM ... -- 原FROM和JOIN内容
  LEFT OUTER JOIN AccountChargePayment ON ...
  WHERE ... -- 原WHERE条件
  GROUP BY ... -- 原GROUP BY内容
  HAVING ... -- 原HAVING内容
  ORDER BY Billing_AccountPaymentDate.DueDate ASC
  

• 调整主查询连接顺序：让过滤条件最严格的表（如Billing_Account先按SchoolId和SchoolYearId过滤）优先执行，减少后续连接的数据处理量。

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