Dynamic SQL and stored procedure optimization

Question

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?

Solution 1

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

Solution 2

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

Comments

[Finster]

The case I'm considering is using dynamic SQL to determine what kind of where clause to use in my SELECT. AFAIK, I can't parameterize my query, since I'm not using the parameters as field values. Or is there a way to parameterize a where clause?

[DeanOC]

No, I don't think where clauses can be parameterised as the where clause is part of the SQL command. It sounds like your dynamic SQL is essentially static, but there are just different flavours of it. I imagine that you are just trying to cut down on code repetition. I guess the only way to be sure is to speed test the two styles, but in your case I would expect negligible difference.

[SQLnbe]

@DeanOC why did you use the Name = Value method (ex. statement = sqlcommand etc.) at the execute level? Wouldn't this be still the same exec sp_executesql sqlcommand, paramList, Param1, Param2 what you're referring to as properly build Dynamic SQL where params aren't hard coded which is the case here. Just curious!

[DeanOC]

There isn't any difference; it's just personal preference. I find I make less mistakes if I explicitly use @param = value rather than just enumerating the values. Feel free to use the shortcut version if you prefer.