Возможно ли обойтись без вспомогательной таблицы?

Есть таблица с иерархическими записями:И есть запрос, который сначала по исходной таблице собирает информацию обо всех предках каждой записи и потом для каждой исходной записи собирает какую-то вычисляемую информацию по всем её предкам:Но работает медленно.

В плане запроса при этом восклицательным знаком подсвечиваются два блока, с уведомлением, что используется 'tempdb'. Хотя, если я правильно понимаю цифры из плана, именно эти блоки не отнимают слишком много ресурсов (cost: 1-2%):

Operator used tempdb to spill data during execution with spill level 2 and 1 spilled thread(s); Hash wrote 888 pages to and read 888 pages from tempdb with granted memory 1088KB and used memory 1032KB

Если вместо рекурсивного запроса, формирующего 'smp_hierarchy', использовать предварительно заполненную и проиндексированную реальную таблицу 'smp_hierarchy (smp_id, ancestor_id)', то работает в десятки раз быстрее, но вот поддерживать эту таблицу та ещё морока, т.к. приходится её перестраивать при любом добавлении/удалении записи и при любом изменении master_id в исходной таблице.

--
Сейчас используется вариант именно с реальной вспомогательной таблицей, но, может, есть какие-то другие инструменты для этого, не уступающие по скорости?

Почитал про 'hierarchyid', но это, если я правильно понял, просто вариант замены 'master_smp_id' на особый тип поля. И для выполнения обозначенного выше запроса мне всё равно придётся предварительно составлять список всех предков для каждой записи (и оформлять его в виде виртуальной или реальной таблицы).

0

Сообщение от kotelok использовать предварительно заполненную и проиндексированную реальную таблицу

Постройте view with schemabinding + индексы вместо таблички

1

Сообщение от PaulWist Постройте view with schemabinding

приветствую!
Расскажите, пожалуйста, в чём профит? Я видел такое только в рамках таблиц и ХП, оптимизированных для памяти.

0

Сообщение от PaulWist Постройте view with schemabinding

Не, не получается.

Чтобы добавить индекс на View, надо сначала добавить ему уникальный кластеризованный индекс.

Cannot create index on view 'smp_hierarchy_view'. It does not have a unique clustered index.

The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create more nonclustered indexes. https://learn.microsoft.com/en... exed-views

А уникальный кластеризованный индекс в данном случае не добавить, т.к. их нельзя добавлять на view, в которых CTE используется (а как без CTE получить тот же результат я пока не знаю):

Cannot create index on view because it references common table expression "smp_hierarchy". Views referencing common table expressions cannot be indexed. Consider not indexing the view, or removing the common table expression from the view definition.

0

Сообщение от Jack Famous Расскажите, пожалуйста, в чём профит?

кажется, это материализованное представление. И тогда я ещё больше не понимаю, в чём смысл, если можно просто сделать таблицу…

kotelok, я в иерархиях не разбираюсь, т.к. не использую в силу, как раз, проблем с длительностью их обработки.
Я бы заменил иерархию на плоский список. Если заменить нельзя, то настроил бы синхронизацию с такой плоской таблицей, чтобы она изменялась вместе с иерархической. И работал бы с плоской всегда.

UPD:

Сообщение от Jack Famous кажется, это материализованное представление

индексированное. Думал, это одно и то же, ан нет)

UPD2:

Сообщение от Jack Famous индексированное. Думал, это одно и то же, ан нет)

грёбаные мелкомягкие. Совсем запутали. MATERIALIZED VIEW только для Azure Synapse Analytics. Так что да — это материализованное (оно же индексированное) представление (с кучей ограничений по созданию/возможностям). И не забыть With (NoExpand) при использовании.

0

Сообщение от Jack Famous настроил бы синхронизацию

Сейчас так и сделано - есть таблица с иерархическими записями, есть "плоская" таблица (с индексами), в которой, по сути, лежит то же, что выдаёт рекурсивный запрос.

И есть триггеры на исходной таблице (insert/update/delete), в рамках которых "плоская" таблица поддерживается в согласованном состоянии. Вот попытался уйти от этой сложной логики, но достаточно быстрого варианта пока не нашёл.

0

Сообщение от kotelok Не, не получается.

Блин, зарапортовался ... виноват, склероз

У вас нормальное решение, чем оно не устраивает? долго дерево перестраивается?

Когда-то TaPaK говорил, что используется развернутая табличка для дерева

у нас вторая таблица аля ParentId | ChildId где для каждого вашего ID = 1 будет 3 записи 1-1 1-2 1-3

Сообщение от Jack Famous Расскажите, пожалуйста, в чём профит?

schemabinding + кластерный индекс на view позволяет/создаёт табличку (определенную во view) на диске, те хранится результат запроса, + доп. бонус, если Engeen видит, что где-то используется запрос повторяющий определение view, то обращение идёт к view В следующих двух запросах показано, как можно использовать индексированное представление, даже если представление не указано в предложении FROM .

1

PaulWist, всё равно непонятно…

1. У ТСа иерархическая табличка.
2. Он собирает из неё данные по по всем предкам. Это работает долго.
3. Если иметь табличку на основе иерархической, но "плоскую" то всё летает. Проблема в поддержке такой вспомогательной таблицы.

Индексированное представление — это вычисленный запрос. То есть, можно заранее, например, сгруппировать данные в исходной таблицы и это вычисленное состояние будет доступно на диске. Плюс, оно само будет актуализироваться на основе всех задействованных источников при их изменении. Это удобно. Платим за это удобство увеличением времени изменения источников.

Но главная проблема в огромном количестве ограничений таких индексированных преставлений — в них запрещено практически всё. Именно поэтому я не понимаю, как такая вьюха поможет ТСу, ведь сделать её в виде плоской таблицы на основе иерархии не представляется возможным. Во всяком случае, я не понимаю, как.

Я бы написал ХП для Delete/Insert/Update иерархической таблички и встроил в неё параллельное изменение плоской таблицы. Если уж от иерархии избавиться по каким-то причинам нельзя.
Можно, конечно. и на триггерах построить, но, один хрен, придётся (уже) вычислять (а не иметь на входе), какие данные изменились и производить параллельные правки плоской.

0

Сообщение от Jack Famous 3. Если иметь табличку на основе иерархической, но "плоскую" то всё летает. Проблема в поддержке такой вспомогательной таблицы.

Пока ТС не написал, что конкретно "плохо" при поддержании плоской таблицы.

Сообщение от Jack Famous Но главная проблема в огромном количестве ограничений таких индексированных преставлений — в них запрещено практически всё.

У ТСа формирование рекурсии занимает много времени, т.е. ему рекурсивная вьюха (конечно если бы это было возможно) нужна только для выборки, поэтому все ограничения в общем случае не важны.

Сообщение от Jack Famous Я бы написал ХП для Delete/Insert/Update иерархической таблички и встроил в неё параллельное изменение плоской таблицы. Если уж от иерархии избавиться по каким-то причинам нельзя. Можно, конечно. и на триггерах построить, но, один хрен, придётся (уже) вычислять (а не иметь на входе), какие данные изменились и производить параллельные правки плоской.

Опять же, мы не знаем как табличка изменятся (OLAP/OLTP) с какой периодичностью, ТС использует плоскую табличку - его устраивает.

0

Сообщение от PaulWist Опять же, мы не знаем как табличка изменятся

Иерархическая таблица с пользовательскими данными. Пользователи, через API, читают, добавляют, редактируют и удаляют данные (в т.ч. у любой записи может измениться 'master_id').

При этом информация обо всех предках записи нужна при любой CRUD-операции (в т.ч. при считывании коллекции по условию). И информация должна быть актуальной в любой момент времени, потому поддерживать "плоскую" таблицу в согласованном состоянии приходится через триггеры исходной таблицы (а не актуализировать раз в час/сутки по расписанию).

Ну и просто есть сомнения, насколько адекватно в триггере на, скажем, редактирование записи, реализовывать достаточно тяжёлую логику анализа/модификации другой таблицы.

0

Ограничения на глубину есть?

0

Сообщение от Аватар Ограничения на глубину есть?

В явном виде нет. Но с точки зрения логики предметной области, наверное, можно допустить определённую максимальную вложенность в пределах пары десятков уровней.

0

это много

Добавлено через 7 минут

Сообщение от kotelok Но работает медленно

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

0

Сообщение от kotelok потому поддерживать "плоскую" таблицу в согласованном состоянии приходится через триггеры исходной таблицы

триггеры дают все 3 набора строк: Delete, Insert, Update(As Delete+Insert).
Анализируя эти наборы можно точечно и быстро актуализировать плоскую.

0