SQL Server高效生成期初余额列的优化方案咨询

我有一张交易表，按日期、账号和项目号记录正负金额。需要生成包含期初余额列的结果集，该余额为查询周期开始日期之前所有金额的总和。目前通过自定义函数[sftpUser.BeginBal]实现，但3000行数据集运行耗时超4分钟，求更优实现方案。

当前实现代码 #

Select sftpUser.BeginBal(@startDate,a.ProjectID) as BeginBal,
    a.accountnum, a.AccountDesc, a.JournalRef, a.Projectid, a.ProjDesc, sum(a.amount) as PeriodAmount, b.donorstmtdesc as ProjectName,
    (case when substring(a.accountnum,4,4) in ('4000','4025','4900','4920','4930','4940','5060','5400') then 0 else 1 end) as Grp2,
    (case when substring(a.accountnum,4,4) in ('4000','4025') then 0
          when substring(a.accountnum,4,4) in ('4900','4920','4930','4940','5060','5400') then 1
                  when substring(a.accountnum,4,4) = '6000' then 2
          else 3 end) as RptLn
    from activity a
    left outer join sftpuser.projects b on a.ProjectID=b.projectid
    Where a.PostStatus='Posted' and a.PostDate between @startdate and @enddate and a.ProjectID in (Select ProjID from sftpUser.ProjectID_Split(@ProjID,','))
    Group by a.accountnum, a.AccountDesc, a.JournalRef, a.ProjectID, a.ProjDesc, b.donorstmtdesc

自定义函数[sftpUser.BeginBal] #

Create Function [sftpUser].[BeginBal] (@asof date, @ProjectID as varchar(50))
Returns Money
as Begin
Return (Select sum(a.amount) as BeginBal from sftpUser.activity a where poststatus='Posted' and PostDate<@asof and a.ProjectID=@Projectid)
End

Activity表结构示例 #

预期结果集示例 #

1. 替换标量函数为预聚合数据集 #

标量函数会逐行触发独立查询，3000行就会执行3000次聚合，这是性能瓶颈核心。改为一次性计算所有目标项目的期初余额，再关联主查询：

WITH ProjectBeginBal AS (
    SELECT 
        ProjectID,
        SUM(amount) AS BeginBal
    FROM sftpUser.activity
    WHERE PostStatus = 'Posted' 
      AND PostDate < @startDate
      AND ProjectID IN (SELECT ProjID FROM sftpUser.ProjectID_Split(@ProjID,','))
    GROUP BY ProjectID
)
SELECT 
    COALESCE(pbb.BeginBal, 0) AS BeginBal,
    a.accountnum, a.AccountDesc, a.JournalRef, a.Projectid, a.ProjDesc, 
    SUM(a.amount) AS PeriodAmount, b.donorstmtdesc AS ProjectName,
    CASE WHEN SUBSTRING(a.accountnum,4,4) IN ('4000','4025','4900','4920','4930','4940','5060','5400') THEN 0 ELSE 1 END AS Grp2,
    CASE 
        WHEN SUBSTRING(a.accountnum,4,4) IN ('4000','4025') THEN 0
        WHEN SUBSTRING(a.accountnum,4,4) IN ('4900','4920','4930','4940','5060','5400') THEN 1
        WHEN SUBSTRING(a.accountnum,4,4) = '6000' THEN 2
        ELSE 3 
    END AS RptLn
FROM activity a
LEFT JOIN sftpuser.projects b ON a.ProjectID = b.projectid
LEFT JOIN ProjectBeginBal pbb ON a.ProjectID = pbb.ProjectID
WHERE a.PostStatus = 'Posted' 
  AND a.PostDate BETWEEN @startdate AND @enddate 
  AND a.ProjectID IN (SELECT ProjID FROM sftpUser.ProjectID_Split(@ProjID,','))
GROUP BY a.accountnum, a.AccountDesc, a.JournalRef, a.ProjectID, a.ProjDesc, b.donorstmtdesc, pbb.BeginBal

2. 添加覆盖索引减少IO #

为activity表创建针对性索引，避免全表扫描：

CREATE NONCLUSTERED INDEX IX_Activity_PostStatus_PostDate_ProjectID_Amount
ON sftpUser.activity (PostStatus, PostDate, ProjectID)
INCLUDE (amount, accountnum, AccountDesc, JournalRef, ProjDesc);

如果使用SQL Server 2016及以上版本，建议替换自定义拆分函数为内置的STRING_SPLIT，性能更优。

3. 复用项目ID拆分结果 #

主查询和期初余额计算重复调用拆分函数，可将结果存入临时表复用：

CREATE TABLE #TempProjIDs (ProjID varchar(50));
INSERT INTO #TempProjIDs
SELECT ProjID FROM sftpUser.ProjectID_Split(@ProjID,',');

WITH ProjectBeginBal AS (
    SELECT 
        ProjectID,
        SUM(amount) AS BeginBal
    FROM sftpUser.activity
    WHERE PostStatus = 'Posted' 
      AND PostDate < @startDate
      AND ProjectID IN (SELECT ProjID FROM #TempProjIDs)
    GROUP BY ProjectID
)
SELECT 
    COALESCE(pbb.BeginBal, 0) AS BeginBal,
    a.accountnum, a.AccountDesc, a.JournalRef, a.Projectid, a.ProjDesc, 
    SUM(a.amount) AS PeriodAmount, b.donorstmtdesc AS ProjectName,
    CASE WHEN SUBSTRING(a.accountnum,4,4) IN ('4000','4025','4900','4920','4930','4940','5060','5400') THEN 0 ELSE 1 END AS Grp2,
    CASE 
        WHEN SUBSTRING(a.accountnum,4,4) IN ('4000','4025') THEN 0
        WHEN SUBSTRING(a.accountnum,4,4) IN ('4900','4920','4930','4940','5060','5400') THEN 1
        WHEN SUBSTRING(a.accountnum,4,4) = '6000' THEN 2
        ELSE 3 
    END AS RptLn
FROM activity a
LEFT JOIN sftpuser.projects b ON a.ProjectID = b.projectid
LEFT JOIN ProjectBeginBal pbb ON a.ProjectID = pbb.ProjectID
WHERE a.PostStatus = 'Posted' 
  AND a.PostDate BETWEEN @startdate AND @enddate 
  AND a.ProjectID IN (SELECT ProjID FROM #TempProjIDs)
GROUP BY a.accountnum, a.AccountDesc, a.JournalRef, a.ProjectID, a.ProjDesc, b.donorstmtdesc, pbb.BeginBal

DROP TABLE #TempProjIDs;

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