动态SQL和存储过程优化

Question

我已经读到，在存储过程中使用动态SQL可能会损害存储过程的性能。我猜该理论是，商店过程不会存储通过exec或sp_executesql执行的SQL的执行计划。

我想知道这是否是真的。如果是真的，如果块，我是否有相同的问题，每个嵌套，每个嵌套，我的sql语句的"版本"不同？

Answer 1

如果您有多个嵌套，则SQL Server将能够存储执行计划。我假设IFS很简单，例如。如果 @parameter1不是null

Schmitzit的答案是正确的，SQL Server还可以存储动态SQL的执行路径。但是，只有在正确构建和执行SQL时，这才是正确的。

通过正确构建，我的意思是明确声明参数并将其传递给SP_EXECUTESQL。例如

declare @Param1 nvarchar(255) = 'foo'
        ,@Param2 nvarchar(255) = 'bar'
        ,@sqlcommand nvarchar(max)
        ,@paramList nvarchar(max)
set @paramList = '@Param1 nvarchar(255), @Param2 nvarchar(255)'
set @sqlcommand = N'Select Something from Table where Field1 = @Param1 AND Field2 = @Param2'
exec sp_executesql @statement = @sqlcommand
                  ,@params = @paramList
                  ,@Param1 = @Param1
                  ,@Param2 = @Param2

您可以看到SQLCommand Text不会将要使用的参数值进行硬编码。它们在Exec sp_executesql

中分别通过

如果您写了不好的旧动态SQL

set @sqlcommand = N'Select Something from Table where Field1 = ' + @Param1  + ' AND Field2 = ' + @Param2
exec sp_executesql @sqlcommand

那么SQL Server将无法存储执行计划

Answer 2

这是MSDN对此所说的。我强调了您的问题的相关位

sp_executesql具有与批处理相同的行为， 名称的范围和数据库上下文。 Transact-SQL语句 或在SP_EXECUTESQL中的批处理@STMT参数直到直到 SP_EXECUTESQL语句执行。@STMT的内容是 然后编译并执行为执行计划，与 称为sp_executesql的批量执行计划。这 sp_executesql批次不能参考批处理中声明的变量 该调用sp_executesql。本地光标或变量 sp_executesql批次对调用的批次看不见 sp_executesql。数据库上下文的更改仅持续到末尾 sp_executesql语句。

sp_executesql可以使用代替存储过程来执行 Transact-SQL语句多次参数值的更改 陈述是唯一的变化。因为Transact-SQL 语句本身保持恒定，只有参数值 更改，SQL Server查询优化器可能会重复使用 它为第一个执行生成的执行计划。

http://msdn.microsoft.com/en-us/library/ms188001.aspx