sql-server - 使用动态 SQL 与参数调用 sp_executesql 的性能差异

Question

给定:

CREATE PROCEDURE [dbo].[my_storedproc]
  @param1 int, @param2 varchar(100)
AS 
<<whatever>>
GO

这些不同的执行方法之间是否存在已知的性能差异？:

-- Method #1:
declare @param1 int = 1
declare @param2 varchar(100) = 'hello'  
exec my_storedproc @param1, @param2

-- Method #2:  
exec my_storedproc @param1=1, @param2='hello'

-- Method #3:  
declare @param1 int = 1
declare @param2 varchar(100) = 'hello'  
declare @procname nvarchar(100) = N'my_storedproc @param1, @param2'
declare @params nvarchar(4000) = N'@param1 int, @param2 varchar(100)'  
exec sp_executesql @procname, @params, @param1, @param2

-- Method #4:  
declare @procname nvarchar(4000) = N'my_storedproc @param1=1, @param2=''hello'''
exec sp_executesql @procname

-- Method #5:  
declare @procname nvarchar(4000) = N'my_storedproc 1, ''hello'''
exec sp_executesql @procname

-- Method #6:  
declare @procname nvarchar(4000) = N'my_storedproc 1, ''hello'''
exec (@procname)

“你为什么这么问？”你问？我正在尝试找到一种方法来完全基于元数据执行存储过程，控制存储过程将物理执行所有其他配置的(在元数据中)存储过程，除了元数据中定义的内容之外，对它们一无所知。在这个 Controller SP 中，我无法(在任何实际意义上)知道并声明可能必须调用的每个可能的存储过程所需的特定物理参数(及其所需的数据类型) - 我正在尝试找到一种执行它们的方法完全通用，同时仍然希望保持良好的性能(重用查询计划等)。

Answer 1

这 6 个选项之间确实不应该有性能差异，因为它们都在执行存储过程而不是直接执行任何 SQL 语句。

但是，没有比在您自己的系统上进行测试更好的性能指标了。您已经有了 6 个测试用例，因此尝试每一个应该不难。

Within this controller SP, I cannot (in any practical sense) know and declare the specific physical parameters (with their required data types) required for every possible stored proc that might have to be called

为什么不呢？我不明白为什么您不能根据以下任一查询的输出为方法 2 和 3 动态生成 SQL:

SELECT OBJECT_NAME(sp.[object_id]), *
FROM   sys.parameters sp
WHERE  sp.[object_id] = OBJECT_ID(N'dbo.my_storedproc');

SELECT isp.*
FROM   INFORMATION_SCHEMA.PARAMETERS isp
WHERE  isp.[SPECIFIC_NAME] = N'my_storedproc'
AND    isp.[SPECIFIC_SCHEMA] = N'dbo';

利用这些信息，您可以创建一个表来包含每个过程的每个参数的各种参数值。事实上，您甚至可以将其设置为具有所有变体的“全局”值的一些参数，然后一些参数值是特定过程的变体。