动态SQL和存储过程优化 [英] Dynamic SQL and stored procedure optimization

问题描述

我已阅读到在存储过程中使用Dynamic SQL会损害存储过程的性能。我猜想这是因为存储过程不会存储通过EXEC或sp_executesql执行的SQL的执行计划。

I've read that using Dynamic SQL in a stored procedure can hurt performance of your stored procedures. I guess the theory is that the store procedure won't store an execution plan for SQL executed via EXEC or sp_executesql.

我想知道这是否正确。如果是这样，那么我是否对多个嵌套的IF块有相同的问题，每个IF块的SQL语句都有不同的版本？

I want to know if this is true. If it is true, do I have the same problem with multiple nested IF blocks, each one with a different "version" of my SQL statement?

推荐答案

如果您有多个嵌套的IF块，则SQL Server将能够存储执行计划。
我假设这些IF很简单，例如如果@ Parameter1不为空

If you have multiple nested IF blocks then SQL Server will be able to store execution plans. I'm assuming that the IFs are straightforward, eg. IF @Parameter1 IS NOT NULL

SchmitzIT的回答是正确的，即SQL Server也可以存储动态SQL的执行路径。但是，只有在正确构建和执行sql的情况下，这才是正确的。

SchmitzIT's answer is correct that SQL Server can also store execution paths for Dynamic SQL. However this is only true if the sql is properly built and executed.

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

By properly built, I mean explicitly declaring the parameters and passing them to sp_executesql. For example

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文本未对要使用的参数值进行硬编码。它们分别在exec sp_executesql中传递。

As you can see the sqlcommand text does not hardcode the paramer values to use. They are passed separately in the exec sp_executesql

如果您编写了不好的旧动态sqL

If you write bad old dynamic sqL

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

exec sp_executesql @sqlcommand

然后SQL Server将无法存储执行计划

then SQL Server won't be able to store execution plans

这篇关于动态SQL和存储过程优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！