Вопрос о SQL Server HierarchyID производительность в глубину
Я пытаюсь реализовать hierarchyID в таблице (dbo. [Сообщение]) содержащий примерно 50 000 строк (вырастет существенно в будущем). Однако требуется 30-40 секунд для получения приблизительно 25 результатов.
Корневой узел является заполнителем для обеспечения уникальности, для этого каждая последующая строка является ребенком той фиктивной строки.
Я должен смочь пересечь таблицу, в глубину, и сделал hierarchyID столбец (dbo. [Сообщение].MessageID) кластеризирующийся первичный ключ, также добавил вычисленный smallint (dbo. [Сообщение].Hierarchy), который хранит уровень узла.
Использование: A. Сетевое приложение проходит через значение hierarchyID в базу данных, и я хочу смочь получить все (если таковые имеются) дети И родители того узла (помимо корня, поскольку это - заполнитель).
Упрощенная версия запроса я использую:
@MessageID hierarchyID /* passed in from application */
SELECT
m.MessageID, m.MessageComment
FROM
dbo.[Message] as m
WHERE
m.Messageid.IsDescendantOf(@MessageID.GetAncestor((@MessageID.GetLevel()-1))) = 1
ORDER BY
m.MessageID
Из того, что я понимаю, индекс должен быть обнаружен автоматически без подсказки.
От ищущих форумов я видел, что люди используют индексные подсказки при контакте с индексами в ширину, но не наблюдал это приложение в ситуациях в глубину. Это было бы соответствующим подходом для моего сценария?
Я провел прошедшие несколько дней, пытаясь найти решение для этой проблемы, но напрасно. Я был бы очень признателен за любую помощь, и поскольку это - мое первое сообщение, я приношу извинения заранее, если это считали бы 'noobish' вопросом, я прочитал документацию MS и искал бесчисленные форумы, но не имею, столкнулся со сжатым описанием конкретного вопроса.
2 ответа
Обходной путь здесь: http://connect.microsoft.com/SQLServer/feedback/details/532406/performance-issue-with-hierarchyid-fun-isdescendantof-in-where-clause #
Напоминаю, что я начал с heirarchyID , переданного из приложения, и моя цель - получить всех без исключения родственников с этим значением (как предков, так и потомков).
В моем конкретном примере мне пришлось добавить следующие объявления перед оператором SELECT :
declare @topNode hierarchyid = (select @messageID.GetAncestor((@messageID.GetLevel()-1)))
declare @topNodeParent hierarchyid = (select @topNode.GetAncestor(1))
declare @leftNode hierarchyid= (select @topNodeParent.GetDescendant (null, @topNode))
declare @rightNode hierarchyid= (select @topNodeParent.GetDescendant (@topNode, null))
Предложение WHERE было изменено на:
messageid.IsDescendantOf(@topNode)=1 AND (messageid > @leftNode ) AND (messageid < @rightNode )
Повышение производительности запросов очень важно:
Для каждого переданного результата время поиска теперь составляет в среднем 20 мс (было от 120 до 420).
При запросе 25 значений ранее для возврата всех связанных узлов требовалось 25–35 секунд (в некоторых случаях у каждого значения было много родственников, в некоторых их не было). Теперь это занимает всего 2 секунды.
Большое спасибо всем, кто способствовал решению этой проблемы на этом и других сайтах.
Не совсем понятно, пытаетесь ли вы оптимизировать поиск в глубину или в ширину; вопрос предполагает, что сначала глубина, но комментарии в конце о ширине.
У вас есть все индексы, необходимые для определения глубины (просто проиндексируйте столбец ierarchyid ). В ширину,недостаточно просто создать вычисляемый столбец уровня , вы также должны проиндексировать его:
ALTER TABLE Message
ADD [Level] AS MessageID.GetLevel()
CREATE INDEX IX_Message_BreadthFirst
ON Message (Level, MessageID)
INCLUDE (...)
(Обратите внимание, что для некластеризованных индексов вам, скорее всего, понадобится ] INCLUDE - в противном случае SQL Server может вместо этого прибегнуть к сканированию кластерного индекса.)
Теперь, если вы пытаетесь найти всех предков узла, вы хотите взять немного другой прихват. Вы можете сделать эти поиски молниеносными, потому что - и вот что замечательно в ierarchyid - каждый узел уже "содержит" всех своих предков.
Я использую функцию CLR, чтобы сделать это как можно быстрее, но вы можете сделать это с помощью рекурсивного CTE:
CREATE FUNCTION dbo.GetAncestors
(
@h hierarchyid
)
RETURNS TABLE
AS RETURN
WITH Hierarchy_CTE AS
(
SELECT @h AS id
UNION ALL
SELECT h.id.GetAncestor(1)
FROM Hierarchy_CTE h
WHERE h.id <> hierarchyid::GetRoot()
)
SELECT id FROM Hierarchy_CTE
Теперь, чтобы получить всех предков и потомков, используйте это следующим образом:
DECLARE @MessageID hierarchyID /* passed in from application */
SELECT m.MessageID, m.MessageComment
FROM Message as m
WHERE m.MessageId.IsDescendantOf(@MessageID) = 1
OR m.MessageId IN (SELECT id FROM dbo.GetAncestors(@MessageID.GetAncestor(1)))
ORDER BY m.MessageID
Попробуйте out - это должно решить ваши проблемы с производительностью.