时间戳

首页 > 解决方案 > 条件存储过程性能与代码构建语句

sql-server - 条件存储过程性能与代码构建语句

问题描述

目前我正在为我们的数据存档开发一个报告系统。目的是为每个月的第一天、每个整小时等选择数据。所以我有一堆参数可以选择一个小时内的数据。

为了实现这一点，我使用 CASE 语句来调整选择，如下所示：

SELECT
    MIN(cd.Timestamp) as Mintime,
    --Hours
    CASE
        WHEN
            @SelHour IS NOT NULL
        THEN
            DATEPART(HOUR, cd.Timestamp)
    END as Hour,
    ... -- more CASES up to DATEPART(YEAR, cd.Timestamp)
    FROM dbo.CustomerData cd
    ... -- filter data and other stuff

到目前为止，这条语句对我很有用，但我有点担心存储过程的性能。因为我不知道服务器将如何处理这个“变化”的语句。结果可以在 20 行结果到 250.000 行或更多行之间变化。取决于给定的参数。据我所知，sql server 会保存查询计划并将其重用于将来的执行。当它保存 20 行结果的计划时，250.000 结果的性能可能很差。

现在我想知道什么是更好的方法。使用这个存储过程还是在我的 c# 后端创建语句并将“调整后的”语句传递给 sql server？

谢谢和问候

标签： sql-servertsqlstored-procedurescase

解决方案

对于 20 行结果集，它在任何地方都可以正常工作。但是对于将 250k 记录返回到 c# 代码，此代码的设计似乎发生了变化，因为在内存中加载 250k 记录和循环也会消耗大量内存，并且来自不同会话/用户的此类并发请求将成倍增加负载。

无论如何，要解决 SQL Server 重用相同查询计划的问题，您可以有选择地或每次重新编译查询计划。这些是可用于重新编译执行计划的选项：

  1. 选项（重新编译）

    SELECT MIN(cd.Timestamp) as Mintime, --Hours CASE WHEN @SelHour IS NOT NULL THEN DATEPART(HOUR, cd.Timestamp) END as Hour, ... -- more CASES up to DATEPART(YEAR, cd.Timestamp) FROM dbo.CustomerData cd ... -- filter data and other stuff OPTION(RECOMPILE)

  2. WITH RECOMPILE 选项这将每次重新编译执行计划

    CREATE PROCEDURE dbo.uspStoredPrcName @ParamName varchar(30) = 'abc'
    WITH RECOMPILE
    AS
    ...

  3. RECOMPILE Query HintWITH RECOMPILE在执行中提供

注意：这将需要数据库中的 CREATE PROCEDURE 权限和正在创建过程的模式的 ALTER 权限。

EXECUTE uspStoredPrcName WITH RECOMPILE;
GO

  1. sp_recompile系统存储过程

注意：需要指定过程的 ALTER 权限。

EXEC sp_recompile N'dbo.uspStoredPrcName ';
GO

有关重新编译的更多详细信息，请参阅 Microsoft Docs： https ://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/recompile-a-stored-procedure?view=sql-server-ver15

推荐阅读