优化SQL Server存储过程调用：单次请求实现月度数据批量统计

老哥，你现在的问题是循环调用存储过程25次，这数据库往返的开销实在太没必要了——尤其是以后产品月龄变大，比如到50个月，那就要调用50次，性能肯定拉胯。咱们直接把逻辑改成一次数据库请求搞定所有月份的统计，简单又高效。

核心思路 #

先生成从产品入库月份到当前月份的所有年月列表，然后一次性对每个月份执行聚合统计，最后把所有结果返回。这样只需要一次数据库调用，彻底消除循环调用的开销。

第一步：重构存储过程 #

我给你写了优化后的存储过程，用CTE生成需要统计的月份范围，然后通过LEFT JOIN关联所有需要的表，一次性计算每个月的统计值：

CREATE PROCEDURE usp_GetAllProductDataByAppId
    @AppId INT
AS
BEGIN
    SET NOCOUNT ON;

    -- 获取产品入库日期
    DECLARE @ProductAddDate DATE;
    SELECT @ProductAddDate = [DateAdded] FROM dbo.[Application] WHERE [AppId] = @AppId;

    -- 用CTE生成连续的月份列表（从入库月到当前月）
    WITH MonthRange AS (
        SELECT 
            DATEFROMPARTS(YEAR(@ProductAddDate), MONTH(@ProductAddDate), 1) AS MonthStart
        UNION ALL
        SELECT 
            DATEADD(MONTH, 1, MonthStart)
        FROM MonthRange
        WHERE MonthStart < DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
    )

    -- 一次性统计所有月份的数据
    SELECT
        YEAR(mr.MonthStart) AS [Year],
        MONTH(mr.MonthStart) AS [Month],
        -- 计算总安装数（处理NULL值，确保无数据时返回0）
        ISNULL(SUM(P.[AutoInstalls] + P.[ITInstalls]), 0) AS ProductInstall,
        -- 计算产品平均值（同样处理NULL）
        ISNULL(AVG(A.[TotalRequests] - A.[TotalInstalls]) * 100, 0) AS ProductAverage,
        -- 原逻辑中的@Month/@Year，这里转换为浮点避免整数除法
        CAST(MONTH(mr.MonthStart) AS FLOAT) / YEAR(mr.MonthStart) AS MonthYearRatio
    FROM MonthRange mr
    -- 关联产品安装表，匹配对应月份（用SARGABLE写法确保索引可用）
    LEFT JOIN dbo.[ProductInstalls] P 
        ON DATEADD(MONTH, DATEDIFF(MONTH, 0, P.[InstallDate]), 0) = mr.MonthStart
    LEFT JOIN [User] U 
        ON U.[UserId] = P.[UserId]
        AND U.LicenseRequired = 1 -- 把用户过滤条件移到JOIN，避免丢失无数据的月份
    -- 关联应用安装表（这里可以添加你提到的其他JOIN和业务逻辑）
    LEFT JOIN dbo.[ApplicationInstalls] A
        ON DATEADD(MONTH, DATEDIFF(MONTH, 0, A.[InstallDate]), 0) = mr.MonthStart
    -- 这里补充你的其他关联表和业务条件
    GROUP BY mr.MonthStart, YEAR(mr.MonthStart), MONTH(mr.MonthStart)
    ORDER BY mr.MonthStart;
END

几个关键优化点：

• CTE生成月份范围：自动覆盖从入库到现在的所有月份，不用手动计算月龄。
• SARGABLE日期匹配：用DATEADD(MONTH, DATEDIFF(MONTH, 0, InstallDate), 0)匹配月份，避免使用DATEPART导致索引失效（如果InstallDate有索引的话，查询速度会快很多）。
• LEFT JOIN替代INNER JOIN：确保即使某个月份没有数据，也会返回该月份的记录（统计值为0），和你原来循环调用的行为保持一致。
• ISNULL处理NULL：避免无数据时返回NULL，保证结果的一致性。

第二步：修改C#代码 #

原来的循环直接删掉，改成一次调用存储过程，获取所有月份的结果：

// 替换成你的数据库连接字符串
var connectionString = "YourDatabaseConnectionString";
int targetAppId = 123; // 你的目标AppId

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (var cmd = new SqlCommand("usp_GetAllProductDataByAppId", connection))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@AppId", targetAppId);

        // 读取所有月份的统计结果
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                var year = reader.GetInt32(reader.GetOrdinal("Year"));
                var month = reader.GetInt32(reader.GetOrdinal("Month"));
                var productInstall = reader.GetInt32(reader.GetOrdinal("ProductInstall"));
                var productAverage = reader.GetDecimal(reader.GetOrdinal("ProductAverage"));
                var monthYearRatio = reader.GetDouble(reader.GetOrdinal("MonthYearRatio"));

                // 这里处理每个月份的数据，比如存入集合或执行业务逻辑
                Console.WriteLine($"[{year}-{month}] 安装数：{productInstall}，平均值：{productAverage:F2}");
            }
        }
    }
}

这样一来，不管产品是25个月还是100个月，都只需要一次数据库请求，性能提升非常明显，代码也更简洁。

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