Dynamic T-SQL и как он может быть полезен

[BelAnt]

Использовать систему отчетности, например, MS Sql Reporting Service. Создать матричный отчет, а в качестве запроса у нас будет «простой» Select. Почему мы так не сделали? В проекте не так много было отчетов, чтобы внедрять туда SSRS.

Внедрение SSRS занимает не так много времени, но даёт много доп. возможностей — подписки на отчеты, выгрузку в разных форматах и т.п. Плюс пользователи смогут самостоятельно задавать параметры для отчета (выбирать группу объектов).

Со временем количество отчетов будет только расти, потратив время сейчас вы сэкономите его в будущем.

[akotelevets]

Конечное решение принимали не мы, клиенту довели досведения все возможные решения

[AlanDenton]

Для обоих подходов планы запросов кэшируются, но они отличаются.

Не хватает слова «параметризация», чтобы неподготовленные читатели смогли увидеть фундаментальную разницу. Вот хороший пример:

USE AdventureWorks2012
GO

DBCC FREEPROCCACHE

DECLARE
      @str VARCHAR(MAX) = 'SELECT * FROM Person.Person WHERE FirstName = '
    , @param VARCHAR(50) = 'David'

EXEC (@str + '''' + @param + '''')

SET @param = 'Tom'

EXEC (@str + '''' + @param + '''')

SELECT st.[text], cp.plan_handle
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.[text] LIKE '%SELECT * FROM Person.Person%'
    AND st.[text] NOT LIKE '%select st.text%'

GO

DBCC FREEPROCCACHE

EXEC sys.sp_executesql
    N'SELECT * FROM Person.Person WHERE FirstName = @val',
    N'@val VARCHAR(200)',
    'David'

EXEC sys.sp_executesql
    N'SELECT * FROM Person.Person WHERE FirstName = @val',
    N'@val VARCHAR(200)',
    'Tom'

SELECT st.[text], cp.plan_handle
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.[text] LIKE '%SELECT * FROM Person.Person%'
    AND st.[text] NOT LIKE '%select st.text%'

Если кратко, то когда параметризации нет (пример EXEC), с каждым новым значением параметра будет свой план генерироваться:

text                                                         plan_handle
------------------------------------------------------------ ----------------------------------------------
SELECT * FROM Person.Person WHERE FirstName = 'Tom'          0x06000B00CA31691380824B12020000000100000000
SELECT * FROM Person.Person WHERE FirstName = 'David'        0x06000B007BF4203740804B12020000000100000000

и когда параметризация есть (один план на «все случаи жизни», который формируется на основе значений при первом выполнении):

text                                                                        plan_handle
--------------------------------------------------------------------------- ----------------------------------------
(@val VARCHAR(200))SELECT * FROM Person.Person WHERE FirstName = @val       0x06000B00632CDD0440804B12020000000100

Такие вот нюансы сильно на производительность влияют.

[akotelevets]

Спасибо за дополнение.

[AlanDenton]

Первый коммент слишком рано нажал опубликовать… Еще не хватает сказать про Parameter Sniffing, который при использовании sp_executesql может снижать производительность. Когда у нас на каждый запрос свой план выполнения создается:

DBCC FREEPROCCACHE
SET STATISTICS IO ON

SELECT * FROM Person.[Address] WHERE City = 'Bothell'
SELECT * FROM Person.[Address] WHERE City = 'Seattle'

то все хорошо:

(26 row(s) affected)
Table 'Address'. Scan count 1, logical reads 268, ...

(141 row(s) affected)
Table 'Address'. Scan count 1, logical reads 346, ...

А теперь ситуация из жизни. По первому запросу построился оптимальный план — проблем нет. При запуске второго запроса значение параметра другое, но план используется от первого:

DBCC FREEPROCCACHE
SET STATISTICS IO ON

EXEC sys.sp_executesql
    N'SELECT * FROM Person.[Address] WHERE City = @val',
    N'@val VARCHAR(200)',
    'Bothell'

EXEC sys.sp_executesql
    N'SELECT * FROM Person.[Address] WHERE City = @val',
    N'@val VARCHAR(200)',
    'Seattle'

что может сильно портить жизнь в реальной жизни:

(26 row(s) affected)
Table 'Address'. Scan count 1, logical reads 268, ...

(141 row(s) affected)
Table 'Address'. Scan count 1, logical reads 498, ... логических чтений больше

Лучшее, что я читал про Parameter Sniffing:

aboutsqlserver.com/2014/08/05/plan-cache-parameter-sniffing

Это все что я хотел сказать. Спасибо Вам за пост.

[akotelevets]

Спасибо Вам за отличные дополнения. Хотя Parameter Sniffing проблема актуально, не только для запрос dynamic sql. Такая же проблема существует и для хранимых процедур.

[pportnoy]

Судя по структуре БД, предоставленной в запросе, есть таблица с продуктами, таблица со справочником различных параметров для продукта, и таблица со значениями параметров.
Вопрос: а куда делся вариант с CROSS APPLY + UNPIVOT? Мне почему-то кажется, что он будет значительно быстрее чем вариант с Dynamic SQL.