Оптимизация динамического SQL и хранимых процедур

Я читал, что использование Dynamic SQL в хранимой процедуре может снизить производительность ваших хранимых процедур. Я предполагаю, что теория заключается в том, что процедура хранения не будет хранить план выполнения SQL, выполняемого через EXEC или sp_executesql.

Я хочу знать, правда ли это. Если это правда, есть ли у меня та же проблема с несколькими вложенными блоками IF, каждый из которых имеет другую «версию» моего оператора SQL?


sql-server stored-procedures sql-server-2008 dynamic-sql
person Finster    schedule 06.11.2012    source источник

Ответы (2)


arrow_upward
7
arrow_downward

Если у вас есть несколько вложенных блоков IF, SQL Server сможет хранить планы выполнения. Я предполагаю, что IF просты, например. ЕСЛИ @Parameter1 НЕ НУЛЬ

Ответ 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 нет жесткого кода используемых значений параметров. Они передаются отдельно в exec sp_executesql.

Если вы пишете плохой старый динамический SQL

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

exec sp_executesql @sqlcommand

тогда SQL Server не сможет хранить планы выполнения

person DeanOC    schedule 07.11.2012
comment
Случай, который я рассматриваю, использует динамический SQL, чтобы определить, какое предложение where использовать в моем SELECT. Насколько я знаю, я не могу параметризовать свой запрос, так как я не использую параметры в качестве значений поля. Или есть способ параметризовать предложение where? - person Finster; 08.11.2012
comment
Нет, я не думаю, что предложения where могут быть параметризованы, поскольку предложение where является частью команды SQL. Похоже, ваш динамический SQL по существу статичен, но есть только разные его разновидности. Я предполагаю, что вы просто пытаетесь сократить повторение кода. Я думаю, единственный способ убедиться в этом — проверить скорость двух стилей, но в вашем случае я ожидаю незначительной разницы. - person DeanOC; 08.11.2012
comment
@DeanOC, почему вы использовали метод Name = Value (например, оператор = sqlcommand и т. д.) на уровне выполнения? Разве это не будет все тот же exec sp_executesql sqlcommand, paramList, Param1, Param2, на который вы ссылаетесь, как на правильную сборку динамического SQL, где параметры не жестко закодированы, что имеет место здесь. Просто любопытно! - person SQLnbe; 13.11.2014
comment
Нет никакой разницы; это просто личные предпочтения. Я обнаружил, что делаю меньше ошибок, если явно использую @param = value, а не просто перечисляю значения. Не стесняйтесь использовать сокращенную версию, если хотите. - person DeanOC; 13.11.2014

arrow_upward
3
arrow_downward

Вот что MSDN должен сказать об этом. Я выделил соответствующие биты на ваш вопрос

sp_executesql имеет то же поведение, что и EXECUTE, в отношении пакетов, области имен и контекста базы данных. Инструкция или пакет 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

person SchmitzIT    schedule 06.11.2012