使用动态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)

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

Answer 1

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

然而，没有比在您自己的系统上测试更好的性能指标了。你已经有了6个测试用例，所以尝试每一个应该不难。

在这个控制器SP中，我不能（在任何实际意义上）知道并声明每个可能被称为的存储过程所需的特定物理参数（及其所需的数据类型）

为什么不呢？我不明白为什么不能根据以下任一查询的输出动态生成方法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';

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