优化超10亿行Employee_bank_history表的插入性能

我们企业拥有75000余名员工，薪资系统中每位员工对应32类账户（如病假、年假、加班存休等），核心业务表结构及示例数据如下：

Employee表 #

Employee
(
   Employee_key INT IDENTITY(1,1),
   Lastname VARCHAR(50),
   Firstname VARCHAR(50)
)

示例数据：

Bank表 #

Bank
(
   Bank_key INT IDENTITY(1,1),
   Bank_name VARCHAR(50)
)

示例数据：

Employee_balance表 #

Employee_balance
(
  Employee_key INT, -- 外键关联Employee表
  Bank_key INT, -- 外键关联Bank表
  Bank_balance NUMERIC(10,5) -- 包含未来日期交易的账户总余额
)

示例数据：

Employee_balance_trans表 #

Employee_balance_trans
(
  Employee_key INT, -- 外键关联Employee表
  Bank_key INT, -- 外键关联Bank表
  Trans_dt DATE, -- 影响账户的交易日期
  Bank_delta NUMERIC(10,5)  
)

示例数据：

由于Employee_balance表包含未来日期的交易净额，需通过SQL计算指定日期的账户余额。为满足部门经理查看员工账户月度期初/期末余额的需求，创建了Employee_bank_history表：

Employee_bank_history
(
   employee_key INT, -- 外键关联Employee表
   bank_key INT, -- 外键关联Bank表
   bank_date DATE,
   bank_balance NUMERIC(10,5) -- 对应bank_date的账户余额
)

该表以employee_key, bank_key, bank_date为唯一聚集索引，每日需插入2021年12月31日至当前日期的数据，最大数据量近20亿行（75000×32×730）。当前使用含CROSS JOIN的INSERT语句插入9.5亿行数据需30-45分钟，需优化插入方法，在不减少数据量的前提下提升速度、降低服务器资源占用。

1. 替换全量CROSS JOIN为高效维度关联 #

避免直接用CROSS JOIN生成所有员工-账户-日期组合，通过预生成日期维度表+缓存员工账户基础数据的方式减少笛卡尔积开销：

步骤1：预生成日期范围临时表 #

CREATE TABLE #DateRange (bank_date DATE PRIMARY KEY);
WITH DateCTE AS (
    SELECT CAST('2021-12-31' AS DATE) AS bank_date
    UNION ALL
    SELECT DATEADD(DAY, 1, bank_date)
    FROM DateCTE
    WHERE bank_date < CAST(GETDATE() AS DATE)
)
INSERT INTO #DateRange (bank_date)
SELECT bank_date FROM DateCTE
OPTION (MAXRECURSION 0);

步骤2：缓存员工账户基础数据后关联计算 #

WITH EmpBankBase AS (
    SELECT eb.Employee_key, eb.Bank_key, eb.Bank_balance AS InitialBalance
    FROM Employee_balance eb
),
BalanceCalculation AS (
    SELECT 
        ebb.Employee_key,
        ebb.Bank_key,
        dr.bank_date,
        ebb.InitialBalance + COALESCE(SUM(ebt.Bank_delta), 0) AS bank_balance
    FROM EmpBankBase ebb
    CROSS JOIN #DateRange dr
    LEFT JOIN Employee_balance_trans ebt
        ON ebb.Employee_key = ebt.Employee_key
        AND ebb.Bank_key = ebt.Bank_key
        AND ebt.Trans_dt <= dr.bank_date
    GROUP BY ebb.Employee_key, ebb.Bank_key, dr.bank_date, ebb.InitialBalance
)
INSERT INTO Employee_bank_history (employee_key, bank_key, bank_date, bank_balance)
SELECT Employee_key, Bank_key, bank_date, bank_balance
FROM BalanceCalculation
ORDER BY employee_key, bank_key, bank_date; -- 匹配聚集索引顺序，减少页拆分

2. 数据库层面的性能优化 #

• 临时禁用非核心约束与索引：插入前禁用Employee_bank_history的非聚集索引、外键约束，插入完成后重建/启用，避免插入时的索引维护开销。
• 启用批量插入优化：
  • 临时将数据库切换为BULK_LOGGED恢复模式（完成后切回原模式），减少日志生成量。
  • 在INSERT语句中添加WITH (TABLOCK)提示，触发大容量日志记录逻辑。
• 配置并行执行：设置MAXDOP为合理值（如CPU核心数的一半），或在INSERT语句末尾添加OPTION (MAXDOP n)强制并行执行，提升计算效率。
• 分区表改造：将Employee_bank_history按bank_date分区（如按季度/月度），插入时仅操作目标分区，降低锁竞争与IO开销。

3. 增量更新替代全量插入 #

每日仅插入新增日期（前一天至当前日期）的数据，而非全量从2021-12-31开始插入：

DECLARE @StartDate DATE = DATEADD(DAY, -1, CAST(GETDATE() AS DATE));
DECLARE @EndDate DATE = CAST(GETDATE() AS DATE);

WITH DateCTE AS (
    SELECT @StartDate AS bank_date
    UNION ALL
    SELECT DATEADD(DAY, 1, bank_date)
    FROM DateCTE
    WHERE bank_date < @EndDate
),
EmpBankBase AS (
    SELECT eb.Employee_key, eb.Bank_key, eb.Bank_balance AS InitialBalance
    FROM Employee_balance eb
),
BalanceCalculation AS (
    SELECT 
        ebb.Employee_key,
        ebb.Bank_key,
        dc.bank_date,
        ebb.InitialBalance + COALESCE(SUM(ebt.Bank_delta), 0) AS bank_balance
    FROM EmpBankBase ebb
    CROSS JOIN DateCTE dc
    LEFT JOIN Employee_balance_trans ebt
        ON ebb.Employee_key = ebt.Employee_key
        AND ebb.Bank_key = ebt.Bank_key
        AND ebt.Trans_dt <= dc.bank_date
    GROUP BY ebb.Employee_key, ebb.Bank_key, dc.bank_date, ebb.InitialBalance
)
INSERT INTO Employee_bank_history (employee_key, bank_key, bank_date, bank_balance)
SELECT Employee_key, Bank_key, bank_date, bank_balance
FROM BalanceCalculation
ORDER BY employee_key, bank_key, bank_date
WITH (TABLOCK);

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