Pull to refresh

Comments 7

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

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

Со временем количество отчетов будет только расти, потратив время сейчас вы сэкономите его в будущем.
Конечное решение принимали не мы, клиенту довели досведения все возможные решения
Для обоих подходов планы запросов кэшируются, но они отличаются.

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

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

Такие вот нюансы сильно на производительность влияют.
Первый коммент слишком рано нажал опубликовать… Еще не хватает сказать про 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

Это все что я хотел сказать. Спасибо Вам за пост.
Спасибо Вам за отличные дополнения. Хотя Parameter Sniffing проблема актуально, не только для запрос dynamic sql. Такая же проблема существует и для хранимых процедур.
Судя по структуре БД, предоставленной в запросе, есть таблица с продуктами, таблица со справочником различных параметров для продукта, и таблица со значениями параметров.
Вопрос: а куда делся вариант с CROSS APPLY + UNPIVOT? Мне почему-то кажется, что он будет значительно быстрее чем вариант с Dynamic SQL.
Only those users with full accounts are able to leave comments. Log in, please.