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 может снижать производительность. Когда у нас на каждый запрос свой план выполнения создается:
то все хорошо:
А теперь ситуация из жизни. По первому запросу построился оптимальный план — проблем нет. При запуске второго запроса значение параметра другое, но план используется от первого:
что может сильно портить жизнь в реальной жизни:
Лучшее, что я читал про Parameter Sniffing:
aboutsqlserver.com/2014/08/05/plan-cache-parameter-sniffing
Это все что я хотел сказать. Спасибо Вам за пост.
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
Это все что я хотел сказать. Спасибо Вам за пост.
Судя по структуре БД, предоставленной в запросе, есть таблица с продуктами, таблица со справочником различных параметров для продукта, и таблица со значениями параметров.
Вопрос: а куда делся вариант с CROSS APPLY + UNPIVOT? Мне почему-то кажется, что он будет значительно быстрее чем вариант с Dynamic SQL.
Вопрос: а куда делся вариант с CROSS APPLY + UNPIVOT? Мне почему-то кажется, что он будет значительно быстрее чем вариант с Dynamic SQL.
Sign up to leave a comment.
Dynamic T-SQL и как он может быть полезен