Ни для кого не секрет, я люблю оптимизировать вещи. В каком-то смысле я одновременно Оптимизатор (в буквальном смысле) и оптимизатор. И сегодня мы вернемся к основам – оптимизации хитрого SQL-запроса.
Рассматриваемый запрос – это конкретная хранимая процедура. ecf_CatalogNode_GetAllChildNodes, это используется для получения всех дочерних узлов определенных узлов. Он используется между ними для поиска всех записей, которые являются прямыми или косвенными дочерними элементами определенных узлов. Почему, спросите вы, ведь когда вы меняете сегмент URL-адреса узла, вы хотите быть уверены, что для всех записей, находящихся под этим узлом, будет обновлен индексированный объект.
Давайте посмотрим на эту хранимую процедуру, вот как она выглядит
CREATE PROCEDURE [dbo].[ecf_CatalogNode_GetAllChildNodes]
@catalogNodeIds udttCatalogNodeList readonly
AS
BEGIN
WITH all_node_relations AS
(
SELECT ParentNodeId, CatalogNodeId AS ChildNodeId FROM CatalogNode
WHERE ParentNodeId > 0
UNION
SELECT ParentNodeId, ChildNodeId FROM CatalogNodeRelation
),
hierarchy AS
(
SELECT
n.CatalogNodeId,
'|' + CAST(n.CatalogNodeId AS nvarchar(4000)) + '|' AS CyclePrevention
FROM @catalogNodeIds n
UNION ALL
SELECT
children.ChildNodeId AS CatalogNodeId,
parent.CyclePrevention + CAST(children.ChildNodeId AS nvarchar(4000)) + '|' AS CyclePrevention
FROM hierarchy parent
JOIN all_node_relations children ON parent.CatalogNodeId = children.ParentNodeId
WHERE CHARINDEX('|' + CAST(children.ChildNodeId AS nvarchar(4000)) + '|', parent.CyclePrevention) = 0
)
SELECT CatalogNodeId FROM hierarchy
END
Ранее я писал об отношениях между сущностями в каталоге Commerce, здесь Коммерческие отношения (корабль), история – блог Куан Мая (vimvq1987.com) поэтому отношения между узлами могут быть немного сложными — узел может иметь одного истинного родителя, определенного в CatalogNode таблицу, а затем другие «связанные» узлы в CatalogNodeRelation . Таким образом, чтобы найти всех дочерних элементов и внуков узла, вам нужно получить данные от обоих.
Получение дочерних узлов узла из CatalogNode или CatalogNodeRelation Это просто, но все усложняется, когда нужно обзавестись внуками, потом правнуками и так далее, и тому подобное. при этом CTE необходимо использовать рекурсивно. Но тут возникает проблема — есть шанс, пусть небольшой, но всё же, что данные были добавлены корректно, поэтому возможна циклическая ссылка. т.е. A является родительским элементом B, который является родительским элементом C, и сам является родительским элементом A. Чтобы остановить бесконечную работу SP, необходимо добавить проверку, чтобы убедиться, что любая циклическая ссылка прерывается.
Это возвращает память, поскольку первый случай поддержки, над которым я работал в Optimizely (тогда Episerver), был с циклической ссылкой. Сайт аварийно завершал работу всякий раз, когда кто-то посещал управление каталогом в Commerce Manager. Это было где-то в июне 2012 года (теперь чувствуешь себя старым?). Мой тогдашний «начальник» невольно вызвал меня на это дело. Посмотри, что ты заставил меня сделать, босс.
Теперь вы можете понять суть того, что делает SP – давайте вернемся к исходной проблеме. он работает медленно, особенно с большим каталогом и сложной структурой узлов. Как всегда, чтобы оптимизировать все, что вам нужно, чтобы найти узкое место — самое время запустить SQL Server Management Studio и включить фактический план выполнения.
Я решил использовать 66, «корневой» узел каталога. этот запрос дает около 18 тыс. строк
declare @Nodes udttCatalogNodeList
insert into @Nodes (CatalogNodeId) select 66
exec ecf_CatalogNode_GetAllChildNodes @Nodesа также 18с исполнения.
Имейте в виду, это на моей машине с довольно мощным процессором (AMD Ryzen 7 5800x, 8 ядер, 16 потоков) и очень быстрым SSD-накопителем nvme PCIe (Western Digital Black SN850 2 ТБ). Если это было выполнено, например, в базе данных Azure Sql, тайм-аут почти наверняка гарантирован. Поэтому время выполнения следует сравнивать только относительно друг друга.
Если мы посмотрим на план выполнения, то станет совершенно очевидно, где находится узкое место. Сканирование включено CatalogNode таблица тяжелая (в этой операции было прочитано 79 миллионов строк). По предложению Андерса из Тайм-аут при удалении CatalogNodes из большого каталога (optimizely.com)добавив некластеризованный индекс на ParentNodeId колонка значительно улучшила бы его. И это действительно так. Время выполнения сокращается до 5 секунд.
И количество строк, читаемых дальше CatalogNode снижено до 17 тыс.
Это, конечно, очень хорошее улучшение. Но заказчик сообщил, что этого недостаточно и ИП все еще дает таймаут, т.е. необходима дальнейшая оптимизация.
Естественно, следующим шагом будет проверка, можем ли мы пропустить циклическую проверку. Это было добавлено в качестве безопасной меры, чтобы избежать плохих данных. Его там быть не должно, так как проверка должна производиться при изменении данных. Но оно существует по историческим причинам, и мы не можем просто изменить его, причем нетривиально. Итак, давайте попробуем ради нашего любопытства.
Измененный запрос выглядит следующим образом (по сути, он просто закомментировал любой код, связанный с CyclePrevention
ALTER PROCEDURE [dbo].[ecf_CatalogNode_GetAllChildNodes]
@catalogNodeIds udttCatalogNodeList readonly
AS
BEGIN
WITH all_node_relations AS
(
SELECT ParentNodeId, CatalogNodeId AS ChildNodeId FROM CatalogNode
WHERE ParentNodeId > 0
UNION
SELECT ParentNodeId, ChildNodeId FROM CatalogNodeRelation
),
hierarchy AS
(
SELECT
n.CatalogNodeId
--, '|' + CAST(n.CatalogNodeId AS nvarchar(4000)) + '|' AS CyclePrevention
FROM @catalogNodeIds n
UNION ALL
SELECT
children.ChildNodeId AS CatalogNodeId
--, parent.CyclePrevention + CAST(children.ChildNodeId AS nvarchar(4000)) + '|' AS CyclePrevention
FROM hierarchy parent
JOIN all_node_relations children ON parent.CatalogNodeId = children.ParentNodeId
--WHERE CHARINDEX('|' + CAST(children.ChildNodeId AS nvarchar(4000)) + '|', parent.CyclePrevention) = 0
)
SELECT CatalogNodeId FROM hierarchy
ENDИ улучшение весьма впечатляющее (больше, чем я ожидал), запрос выполняется почти мгновенно (менее 1 с). Чтение CatalogNodeRelation значительно сократилось.
Предупреждение: план выполнения нельзя просто сравнивать как есть. Если я запускаю две версии бок о бок, это дает весьма вводящее в заблуждение сравнение.
Несмотря на то, что верхний (без циклической проверки ссылок) намного быстрее исходного (нижнего), по оценкам SQL Server, первый медленнее (почти в 2 раза медленнее, чем второй). Поэтому следует использовать план выполнения, чтобы увидеть, что было сделано и что является вероятным узким местом. внутри запрос, его не следует использовать для сравнения между запросами. В большинстве случаев сравнение статистики с помощью set statistics io on это лучший способ сравнить.
Если бы не тот факт, что мы меняем поведение хранимой процедуры, я был бы рад такому подходу. Вероятность столкнуться с циклической ссылкой невелика, но не равна нулю. Как мы уже говорили, теоретически мы можем ограничить отношение во время вставки/обновления, но для начала это было бы слишком большим изменением. Это одно из ограничений, поскольку мы работаем на уровне фреймворка — нам нужно действовать осторожно, чтобы ничего не сломать. Критическое изменение — это плохо, но повреждение данных просто неприемлемо. Я потратил несколько часов (вероятно, больше, чем следовало бы), пытаясь оптимизировать циклическую проверку ссылок, но лучшего решения не нашел.
Следующим подходом будет – как мы можем догадаться – убедиться, что мы избавились от Clustered Index Scan произошло на CatalogNodeRelation стол. Решение было бы довольно простым: некластеризованного индекса для ParentNodeId должно быть достаточно.
Большой успех. Производительность сравнима с подходом «нециклической проверки ссылок».
Поскольку добавление индекса является некритичным изменением (хотя в некоторых случаях оно может привести к снижению производительности, как в Любопытный случай плана выполнения SQL – блог Куан Май (vimvq1987.com) но редко и в этом случае мощность ParentNodeId скорее всего, довольно хорошо распределено).
Это все на сегодня. Надеюсь, вы узнаете кое-что об оптимизации запросов в своей повседневной работе.
