使用动态SQL vs参数调用sp_executesql的性能差异 [英] Performance differences calling sp_executesql with dynamic SQL vs parameters

问题描述

给出:

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

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

Are there known performance differences between these different execution methods?:

-- 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中，我无法(从任何实际意义上)知道并声明可能必须调用的每个可能存储的proc所需的特定物理参数(及其所需的数据类型)-我正在尝试找到一种执行它们的方法完全通用，同时仍然希望保持良好的性能(重复使用查询计划等).

"Why do you ask?" you ask? I am trying to find a way to generically execute stored procedures entirely based upon metadata, the controlling stored procedure that will physically execute all the other configured (in metadata) stored procedures knows nothing about them other than what is defined in the metadata. 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 - I am trying to find a way to execute them entirely generically, while still hopefully maintaining decent performance (reusing query plans, etc).

推荐答案

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

There really shouldn't be a performance difference between the 6 options since they are all executing the stored procedure and not any SQL statements directly.

但是，没有比在您自己的系统上测试性能更好的指示了.您已经有6个测试用例，因此不难尝试每个用例.

However, there is no better indication of performance than testing this on your own system. You already have the 6 test cases so it shouldn't be hard to try each one.

在此控制器SP中，我无法(从任何实际意义上)知道并声明可能必须调用的每个可能的存储过程所需的特定物理参数(及其必需的数据类型)

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:

Why not? I don't see why you couldn't dynamically generate the SQL for Methods 2 and 3 based on the output of either of the following queries:

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';

使用该信息，您可以创建一个表，以包含每个proc的每个参数的各种参数值.实际上，您甚至可以将其设置为对所有变体都具有一些带有全局"值的参数，然后某些参数值就是特定proc的变体.

And with that info, you could create a table to contain the various parameter values for each parameter for each proc. In fact, you could even set it up to have some parameters with "global" values for all variations and then some parameter values are variations for a particular proc.

这篇关于使用动态SQL vs参数调用sp_executesql的性能差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！