CODEWITHSUNDEEP
sql-serversql-server-2008stored-proceduresdynamic-sql
Finster
Finster

Reputation: 498

Dynamic SQL and stored procedure optimization

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.

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?

Upvotes: 2

Views: 12392

Answers (2)

DeanOC
DeanOC

Reputation: 7282

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

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

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

If you write bad old dynamic sqL

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

exec sp_executesql @sqlcommand

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

Upvotes: 7

SchmitzIT
SchmitzIT

Reputation: 9572

This is what MSDN has to say about it. I highlighted the relevant bits to your question

sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. The Transact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. The sp_executesql batch cannot reference variables declared in the batch that calls sp_executesql. Local cursors or variables in the sp_executesql batch are not visible to the batch that calls sp_executesql. Changes in database context last only to the end of the sp_executesql statement.

sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement many times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

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

Upvotes: 3

Related Questions