SQL Server 2016存储过程中日期范围列表生成的性能优化求助

针对你遇到的这个存储过程里日期列表生成耗时占比高、频繁调用导致系统负载大的问题，我给你几个实际项目中验证过的优化思路：

1. 预生成并复用日期维度表 #

既然日期列表是重复生成的，最彻底的解决办法是提前创建一个日期维度表，把业务常用的日期范围（比如过去10年到未来5年）一次性生成好，包含你需要的所有附加字段。后续存储过程直接查询这个表，完全避免重复计算。

示例创建和填充脚本：

-- 创建日期维度表
CREATE TABLE DateDimension (
    DateKey DATE PRIMARY KEY,
    Year INT NOT NULL,
    Quarter INT NOT NULL,
    Month INT NOT NULL,
    DayOfMonth INT NOT NULL,
    DayOfWeek INT NOT NULL,
    IsWeekend BIT NOT NULL,
    -- 按需添加你的附加业务字段
    IsHoliday BIT NOT NULL DEFAULT 0
)

-- 用数字表生成日期数据（比递归CTE效率更高）
WITH Numbers AS (
    SELECT TOP 10950 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Num
    FROM sys.all_columns c1
    CROSS JOIN sys.all_columns c2
)
INSERT INTO DateDimension (DateKey, Year, Quarter, Month, DayOfMonth, DayOfWeek, IsWeekend)
SELECT
    DATEADD(DAY, Num - 1, '2010-01-01') AS DateKey,
    YEAR(DATEADD(DAY, Num - 1, '2010-01-01')),
    DATEPART(QUARTER, DATEADD(DAY, Num - 1, '2010-01-01')),
    MONTH(DATEADD(DAY, Num - 1, '2010-01-01')),
    DAY(DATEADD(DAY, Num - 1, '2010-01-01')),
    DATEPART(WEEKDAY, DATEADD(DAY, Num - 1, '2010-01-01')),
    CASE WHEN DATEPART(WEEKDAY, DATEADD(DAY, Num - 1, '2010-01-01')) IN (1,7) THEN 1 ELSE 0 END
FROM Numbers
WHERE DATEADD(DAY, Num - 1, '2010-01-01') <= '2030-12-31'

之后存储过程里只需一行查询就能拿到日期列表：

SELECT DateKey, Year, IsWeekend -- 按需选择字段
FROM DateDimension
WHERE DateKey BETWEEN @StartDate AND @EndDate

2. 优化动态日期生成逻辑（如果无法用预生成表） #

如果业务日期范围无法提前确定，那就要优化生成日期的方式：

• 用数字表替代递归CTE：递归CTE在生成大日期范围时容易产生性能瓶颈，而数字表的扫描效率更高。你可以提前创建一个永久的数字表（比如包含1到10000条记录），之后用它快速生成日期。
• 示例代码：

  -- 假设已有Numbers表，Num字段从1开始递增
  SELECT DATEADD(DAY, Num - 1, @StartDate) AS DateValue
  FROM Numbers
  WHERE DATEADD(DAY, Num - 1, @StartDate) <= @EndDate
  

3. 利用内存优化表存储临时日期数据 #

如果必须每次生成日期列表，可以把生成的日期存储到内存优化表中，内存表的读写速度远快于传统磁盘表，能显著降低每次生成的耗时。示例：

-- 创建内存优化的临时表（需提前启用内存优化文件组）
CREATE TABLE #TempDates (
    DateValue DATE PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000)
) WITH (MEMORY_OPTIMIZED = ON)

-- 生成日期并插入内存表
INSERT INTO #TempDates (DateValue)
SELECT DATEADD(DAY, Num - 1, @StartDate)
FROM Numbers
WHERE DATEADD(DAY, Num - 1, @StartDate) <= @EndDate

-- 后续业务逻辑直接使用#TempDates

4. 确保存储过程执行计划重用 #

检查存储过程的参数是否正确参数化，避免SQL Server每次调用都重新编译执行计划。比如存储过程定义要明确参数类型：

CREATE PROCEDURE GenerateShippingOptions
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    -- 业务逻辑
END

这些方法里，预生成日期维度表是最推荐的方案，它能从根本上消除重复计算的开销，后续所有需要日期列表的业务逻辑都可以复用这个表。

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