如何解决存储过程计算日期间差时出现的执行超时问题

以上代码确实存在大量性能问题，是处理100万条记录时超时的核心原因，具体优化方案如下：

• 标量自定义函数开销过高：fn_getdiff是标量函数，每次调用都会单独执行查询，100万条记录每条调用5次相当于要执行500万次独立子查询，且标量函数无法被SQL优化器生成并行执行计划，进一步拖慢执行速度
• 无效逻辑与冗余计算：函数内select id from tbl_code where id=@ccode的子查询完全无意义，直接判断tcode=@ccode即可；同时重复对日期字段做convert转换，既无法用到索引又额外增加CPU开销
• 逐条调用的架构缺陷：前端循环100万次逐条调用存储过程，每次调用都要单独发起数据库请求、执行单条update，网络IO和事务提交的开销被放大了数百倍
• 索引缺失：tbl_holidays没有针对查询条件的覆盖索引，每次查询都要扫描全表；如果tbl_Scheduler的id字段没有主键/唯一索引，update的行定位也会变慢

1. 替换标量函数为内联表值函数 #

内联表值函数可以被SQL优化器合并到主执行计划中，支持并行执行，性能远高于标量函数：

CREATE FUNCTION fn_getdiff_inline
(
    @startdate date = null,
    @enddate date = null,
    @ccode varchar(10) = null
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        CASE 
            WHEN @startdate IS NULL OR @enddate IS NULL THEN 0
            ELSE DATEDIFF(day, @startdate, @enddate) - ISNULL((
                SELECT COUNT(DISTINCT tdays) 
                FROM tbl_holidays WITH (NOLOCK) 
                WHERE tdays BETWEEN @startdate AND @enddate 
                  AND tcode = @ccode
            ), 0)
        END AS diff_value
)

2. 改造为批量处理架构 #

放弃前端循环逐条调用的逻辑，改用表值参数（TVP）批量传入所有需要处理的数据，仅需一次数据库请求即可完成全部更新：

-- 提前定义用户自定义表类型，用于接收前端批量传入的参数
CREATE TYPE ut_Scheduler_Update AS TABLE
(
    id INT PRIMARY KEY,
    strd1 date,
    strd2 date,
    strd3 date,
    strd4 date,
    strd5 date,
    strd6 date,
    strd7 date,
    strd8 date,
    strd9 date,
    strd10 date,
    strd11 date
)
GO

-- 批量更新存储过程
CREATE PROCEDURE sp_BatchUpdateScheduler
@BatchData ut_Scheduler_Update READONLY
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE s
    SET 
        col_dif1 = ISNULL(d1.diff_value, 0),
        col_dif2 = ISNULL(d2.diff_value, 0),
        col_dif3 = ISNULL(d3.diff_value, 0),
        col_dif4 = ISNULL(d4.diff_value, 0),
        col_dif5 = ISNULL(d5.diff_value, 0)
    FROM tbl_Scheduler s
    INNER JOIN @BatchData b ON s.id = b.id
    OUTER APPLY fn_getdiff_inline(b.strd1, b.strd2, b.strd3) d1
    OUTER APPLY fn_getdiff_inline(b.strd2, b.strd4, b.strd5) d2
    OUTER APPLY fn_getdiff_inline(b.strd4, b.strd5, b.strd6) d3
    OUTER APPLY fn_getdiff_inline(b.strd5, b.strd7, b.strd8) d4
    OUTER APPLY fn_getdiff_inline(b.strd9, b.strd10, b.strd11) d5
END

前端VB.NET代码只需把100万条待处理数据填充到DataTable中，作为表值参数传入上述存储过程即可。如果担心单次事务过大，可以拆分为10次10万条的批次处理。

3. 新增必要索引降低查询开销 #

-- 给tbl_holidays添加覆盖索引，查询直接命中索引无需回表
CREATE NONCLUSTERED INDEX IX_tbl_holidays_tcode_tdays ON tbl_holidays (tcode, tdays)
-- 确认tbl_Scheduler的id字段为主键，如果不是新增主键约束
ALTER TABLE tbl_Scheduler ADD CONSTRAINT PK_tbl_Scheduler_id PRIMARY KEY CLUSTERED (id)

另外需要确认tbl_holidays的tdays字段为date类型，避免隐式类型转换导致索引失效。

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