Что делает "В ИЕРАРХИИ" в запросе?

16.07.19

Разработка - Математика и алгоритмы

Описание действий платформы 1С при использовании конструкции "В ИЕРАРХИИ" в запросах.

Начало

Конструкция "В ИЕРАРХИИ" в запросах позволяет получить подчиненные элементы иерархического объекта конфигурации по заданному отбору. Это может быть иерархический справочник, план счетов, план видов характеристик и др. Сегодня в статье рассмотрим пример использования, а также действия платформы на стороне СУБД, влияние на производительность.

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

Использование

Рассмотрим простой пример использования конструкции "В ИЕРАРХИИ". При выполнении следующего запроса будут получены подчиненные элементы иерархического справочника "Товары" для переданного значения параметра "Ссылка".

ТекстЗапроса = 
"ВЫБРАТЬ
| Товары.Ссылка,
| Товары.Артикул
|ИЗ
| Справочник.Товары КАК Товары
|ГДЕ
| Товары.Ссылка В ИЕРАРХИИ(&Ссылка)"

В качестве значения параметра "Ссылка" передается ссылка на группу справочника. Пример выполняем на "самописной" тестовой базе, но структура справочника "Номенклатура" очень напоминает урезанную версию из типовых конфигураций. Справочник содержит следующие данные:

Конечно, на изображении показаны не все записи справочника. Скриншот показывает лишь структуру хранения данных в иерархическом справочнике. В таблице хранятся 10 групп верхнего уровня, в каждой из них содержится 5 вложенных групп с 200 элементами в каждой.

Вернемся к тестовому запросу. Передадим в параметр "Ссылка" ссылку на группу "Группа - 1" (см. скриншот выше). Тогда результат выполнения запроса будет выглядеть следующим образом:

Как мы видим, запрос вернул ссылку на саму верхнюю группу (переданную параметром), а также вложенные группы с находящимися в них элементами. Таким образом, использование конструкции "В ИЕРАРХИИ" позволяет удобным образом получать иерархически подчиненные данные. 

Синтаксис языка запросов 1C:Предприятия и классического SQL очень похожи во многих моментах. Но для выражения "В ИЕРАРХИИ" нет аналога в языке запросов SQL как, например, для выражения языка запросов платформы "В" есть аналогичный SQL-оператор "IN". Поэтому интересной является работа платформы с СУБД при использовании данного оператора.

За кулисами

Итак, приступим. Для примера будем использовать написанный ранее запрос к справочнику "Товары". Анализировать действия платформы будем для ситуации, когда в качестве параметра передана группа верхнего уровня "Группа 1", что мы уже сделали ранее.

Теперь по порядку. В первом случае платформа выполнит следующие действия на SQL-сервере:

1. Сначала выполняется запрос на получение ссылки на группу справочника, переданную в качестве параметра, и всех подчиненных ей групп и элементов. Результат помещается во временную таблицу '#tt1'.

2. На втором этапе дважды выполняется запрос:

На скриншоте подробно прокомментирован текст SQL-запроса. Если кратко, то запрос позволяет выбрать подчиненные элементы для групп, ссылки на которые находятся во временной таблице. Остается вопрос: "Зачем запрос выполняется дважды?". Тут ответ простой: сначала запрос получает подчиненные элементы для групп первого уровня, которые уже содержатся во временной таблице (см. пункт 1). Затем второй запрос получает подчиненные элементы для подчиненных групп второго уровня. Поскольку на третьем уровне иерархии не присутствует ни одна группа справочника, то данный запрос более не выполняется.

В нашем случае, второй запрос вернет пустой результат, так как для записей, находящихся на 3-м уровне иерархии, нет подчиненных элементов (там нет ни одной группы). 

3. Для получения конечного результата платформа формирует следующий SQL-запрос:

Результат именно этого запроса в дальнейшем может обрабатываться алгоритмами на встроенном языке платформы. Таким образом, записи во временной таблице '#tt1' используются для установки условия выборки из таблицы справочника "_Reference41".

4. На последнем шаге платформа очищает и удаляет временную таблицу '#tt1', поскольку в дальнейшем она уже не будет использоваться.

Пример посложнее

Рассмотрим пример посложнее, чем простой запрос к справочнику. Допустим, у нас есть запрос к регистру бухгалтерии "Хозрасчетный".

ВЫБРАТЬ
	ХозрасчетныйОстатки.Организация КАК Организация,
	ХозрасчетныйОстатки.Валюта КАК Валюта,
	ХозрасчетныйОстатки.Подразделение КАК Подразделение,
	ХозрасчетныйОстатки.Счет КАК Счет,
	ХозрасчетныйОстатки.Субконто1 КАК Субконто1,
	ХозрасчетныйОстатки.Субконто2 КАК Субконто2,
	ХозрасчетныйОстатки.Субконто3 КАК Субконто3,
	ХозрасчетныйОстатки.СуммаОстаток КАК СуммаОстаток
ИЗ
	РегистрБухгалтерии.Хозрасчетный.Остатки(, Счет В ИЕРАРХИИ (&Счет), , ) КАК ХозрасчетныйОстатки

Условие по счету установлены с помощью инструкции "В ИЕРАРХИИ" по значению "10". На сервере СУБД платформа выполнит ряд запросов.

 
 Запросы платформы при использовании "В ИЕРАРХИИ"

Таким образом, инструкция "В ИЕРАРХИИ" работает практически во всех случаях одинаково в несколько этапов:

  1. Получаем ссылку на текущий элемент и подчиненные ему элементы.
  2. В несколько запросов получает элементы для всех последующих уровней иерархии.
  3. Полученные данные используются в последующих запросах платформы. Как они будут использоваться сильно зависит от конкретного запроса в конфигурации (запрос к справочнику, виртуальным таблицам и др.).

Вроде, все не так уж и сложно.

Влияние на производительность

В примере выше мы видели, что платформа сгенерировала несколько запросов для получения элементов на каждом уровне иерархии. Но что, если иерархия будет содержать не 2, 3, 5 уровней, а 10, 20, 50! Чем больше уровней, тем больше запросов будет сформировано.

Плюс ко всему, в нашем примере запрос был очень простой. Но что, если это будет запрос тяжелого отчета, где условие "В ИЕРАРХИИ" будет применено множество раз. Если справочник очень большой, а пользователь в отчете случайно сделал отбор по пустой ссылке? Тогда будет выбран весь справочник!

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

Вот некоторые материалы, в которых Вы можете найти информацию о влиянии инструкции "В ИЕРАРХИИ" на производительность:

Сам по себе оператор "В ИЕРАРХИИ" очень мощный, но требует осторожного использования.

Делайте выводы!

Выводы делать Вам. Скажу лишь, что оператор "В ИЕРАРХИИ" используется платформой для системы компоновки данных, когда в условиях отбора присутствуют "В ГРУППЕ", "В ГРУППЕ ИЗ СПИСКА" и прочие. Думаю, не стоит объяснять, что при особых манипуляциях пользователи могу поставить очень сложный отбор в отчете или динамическом списке. Это может стать причиной значительного повышения нагрузки на всю информационную систему, "тормозам" или даже зависанию.

Ну и, разумеется, при разработке обращайте внимание на оператор "В ИЕРАРХИИ". Очень удобный с одной стороны, и опасный с другой.

Другие ссылки

запросы в иерархии производительность платформа внутренние механизмы

См. также

Метод Дугласа-Пойкера для эффективного хранения метрик

Математика и алгоритмы Платформа 1C v8.2 Конфигурации 1cv8 Россия Абонемент ($m)

На написание данной работы меня вдохновила работа @glassman «Переход на ClickHouse для анализа метрик». Автор анализирует большой объем данных, много миллионов строк, и убедительно доказывает, что ClickHouse справляется лучше PostgreSQL. Я же покажу как можно сократить объем данных в 49.9 раз при этом: 1. Сохранить значения локальных экстремумов 2. Отклонения от реальных значений имеют наперед заданную допустимую погрешность.

1 стартмани

30.01.2024    1714    stopa85    12    

33

Алгоритм симплекс-метода для решения задачи раскроя

Математика и алгоритмы Бесплатно (free)

Разработка алгоритма, построенного на модели симплекс-метода, для нахождения оптимального раскроя.

19.10.2023    4315    user1959478    50    

34

Регулярные выражения на 1С

Математика и алгоритмы Инструментарий разработчика Платформа 1С v8.3 Мобильная платформа Россия Абонемент ($m)

Что ж... лучше поздно, чем никогда. Подсистема 1С для работы с регулярными выражениями: разбор выражения, проверка на соответствие шаблону, поиск вхождений в тексте.

1 стартмани

09.06.2023    7343    4    SpaceOfMyHead    17    

56

Модель распределения суммы по базе

Математика и алгоритмы Платформа 1С v8.3 Россия Абонемент ($m)

Обычно под распределением понимают определение сумм пропорционально коэффициентам. Предлагаю включить сюда также распределение по порядку (FIFO, LIFO) и повысить уровень размерности до 2-х. 1-ое означает, что распределение может быть не только пропорциональным, но и по порядку, а 2-ое - это вариант реализации матричного распределения: по строкам и столбцам. Возможно вас заинтересует также необычное решение этой задачи через создание DSL на базе реализации текучего интерфейса

1 стартмани

21.03.2022    7817    7    kalyaka    11    

44

Изменения формата файлов конфигурации (CF) в 8.3.16

Математика и алгоритмы Платформа 1С v8.3 Бесплатно (free)

Дополнение по формату файлов конфигурации (*.cf) в версии 8.3.16.

16.12.2021    4414    fishca    13    

36

Интересная задача на Yandex cup 2021

Математика и алгоритмы Бесплатно (free)

Мое решение задачи на Yandex cup 2021 (frontend). Лабиринт. JavaScript.

12.10.2021    8793    John_d    73    

46

Механизм анализа данных. Кластеризация.

Математика и алгоритмы Анализ учета Платформа 1С v8.3 Анализ и прогнозирование Бесплатно (free)

Подробный разбор, с примером использования, встроенного механизма кластеризации 1С.

31.08.2021    7713    dusha0020    8    

70
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. insurgut 207 16.07.19 13:26 Сейчас в теме
А в чем мораль? В ИЕРАРХИИ работает ровно так, как задумано, и любые попытки оптимизации обычно только снижают производительность.

Ну а то, что может сделать пользователь прямого отношения к этому оператору не имеет никакого. Хуже или лучше сам оператор "В ИЕРАРХИИ" в зависимости от действий пользователя не становится.
3. пользователь 16.07.19 13:36
(1) морали в моих статьях нет :)

Оператор все же может влиять на производительность. Посмотрите ссылки из статьи. Наверное, самый наглядный пример - это его появление в отчете на СКД. Пример на сайте gilev.ru.
6. kolya_tlt 85 16.07.19 13:47 Сейчас в теме
(3) мораль такова что данную конструкцию лучше запретить и выпилить из платформы. от неё одни не счастья.
8. insurgut 207 16.07.19 14:59 Сейчас в теме
(6) точно так же как и иерархию в справочниках/счетах учета. Дерево значений - тоже грохнуть. :)
12. for_sale 970 16.07.19 16:52 Сейчас в теме
(6)
Да-да, и ножи кухонные запретить - по новостям говорили, что одна женщина мужа таким ножом зарезала! И телеграм тоже запретить - там одни террористы! И вообще - давайте всё запретим!!! Тогда ничего не будет происходить и не будет никаких несчастий))
Innuil; Tikoven; Merkalov; WellMaster; w.r.; korppinen; +6 Ответить
9. insurgut 207 16.07.19 15:03 Сейчас в теме
(3) но это же очевидно, как и то, что альтернативы ей для иерархических справочников нет.
30. Chai Nic 160 13.11.19 08:00 Сейчас в теме
(9) Альтернатива - "закат солнца вручную", то есть реализация иерархии через ссылки, с последующим всем)
32. AlexPC 13.11.19 11:38 Сейчас в теме
(9)
но это же очевидно, как и то, что альтернативы ей для иерархических справочников нет.


1. Отказ от нормальных форм sql позволяет увеличить производительность чтения, в т.ч. и при работе с иерархическими справочниками.
2. Явное хранение иерархии в объектах БД позволяет отказаться от конструкции "В ИЕРАРХИИ".
и т.п.
2. VmvLer 16.07.19 13:27 Сейчас в теме
Спасибо, кэп
tka4enk0; YPermitin; +2 6 Ответить
4. пользователь 16.07.19 13:36
(2) спасибо и Вам :)))
Date; Deslime; tw1ster_ok; +3 Ответить
5. ellavs 1021 16.07.19 13:40 Сейчас в теме
Спасибо. Приятно смотреть, когда скриншоты с такими хорошими пояснениями.
Innuil; taiwanchik; kuzyara; YPermitin; +4 Ответить
7. ids79 8275 16.07.19 14:35 Сейчас в теме
Спасибо.
Как всегда все понятно и доходчиво.
Я как раз в процессе написания статьи про иерархию в СКД.
YPermitin; +1 Ответить
17. пользователь 16.07.19 20:37
(7) ждем с нетерпением!

Спасибо!
10. Dach 371 16.07.19 15:15 Сейчас в теме
Просто оставлю это здесь:

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

https://infostart.ru/public/158512/

"В ИЕРАРХИИ" и "УПОРЯДОЧИТЬ ПО ИЕРАРХИЯ" / "ИТОГИ ПО ИЕРАРХИЯ"

https://infostart.ru/public/417128/
YPermitin; +1 Ответить
28. ildarovich 7846 26.08.19 11:28 Сейчас в теме
В (10) уже давали ссылку на идею, позволяющую обойтись без конструкции "В ИЕРАРХИИ" при решении многих задач на иерархию. Теперь нашел возможность несколько усовершенствовать тот подход и написал новую статью, связанную с затронутой темой. Статья называется "Иерархия без "В ИЕРАРХИИ".
user1906361; Somebody1; Lapitskiy; YPermitin; +4 Ответить
29. пользователь 26.08.19 18:45
(28) спасибо Вам огромное!

Плюсы уже доставлены :)
11. s22 19 16.07.19 15:56 Сейчас в теме
Лучше бы сделали в конфе теневую таблицу

Родитель | подчинены(любого уровня вложенности)

тогда все сведется к внутреннему соединению.
В запросах будет использоваться прозрачно.
Chai Nic; FAMыч; SlavaKron; YPermitin; +4 Ответить
14. kote 536 16.07.19 17:38 Сейчас в теме
(11) не вариант, т.к. будет нужна будет еще одна промежуточная таблица подчиненности.. и сложности с выводом одного уровня подчиненности, не?
18. s22 19 16.07.19 20:39 Сейчас в теме
(14)
) не вариант, т.к. будет нужна будет еще одна промежуточная таблица подчиненности.. и сложности с выводом одного уровня подчиненности, не?

какие сложности?
"промежуточная таблица подчиненности"? зачем Есть справочник. Есть эта таблица.
22. kote 536 17.07.19 16:48 Сейчас в теме
(18) Как это реализуется в таблицах: одна запись (потомок) ссылается на одну запись (родитель)

Теперь, Вы пишите

Родитель | подчинены(любого уровня вложенности)


Т.е. тут либо родитель должен содержать все ссылки на потомков, либо потомки - на всех родителей

Теперь вопрос: как можно реализовать то, что Вы предлагаете без промежуточных таблиц?
Для каждого уровня вложенности создать еще одно поле? Тогда иерархические запросы будут совсем неудобны для использования..

Или есть еще какой-то способ?

Расскажите, если не сложно - как это должно выглядеть, пожалуйста.
23. s22 19 17.07.19 16:56 Сейчас в теме
(22)
Т.е. тут либо родитель должен содержать все ссылки на потомков, либо потомки - на всех родителей

2 поля.
Родитель и все его потомки
таблица теневая, т.е. делается на уровне платформы.

Запрос будет сводиться к внутреннему соединению.
33. Chai Nic 160 13.11.19 16:53 Сейчас в теме
(11) Можно и дальше расширить идею. Заранее считать агрегаты регистров по иерархии. Чтобы остатки по группе например получать моментально.
34. s22 19 13.11.19 20:05 Сейчас в теме
Да. Вообще хорошо, если бы такие вещи можно было настраивать. Кроме того подобные таблицы должны быть в статусах актуальности. Если не актуальна, то не используется.
Актуальность восстанавливать в фоне.
13. Froloid 66 16.07.19 16:57 Сейчас в теме
Когда то крутилась в голове мысль "Как СУБД отрабатывает конструкцию "В иерархии"" (а ещё и конструкцию "Количество (Различные ..)". Но в условиях ненадобности интерес не довёл дело до трассировки запросов на стороне СУБД.

Однако гипотетически как получить иерархию было понятно и самым сложным нюансом казался "Как определить максимальную глубину справочника".

И вот в данном разборе я не вижу ответа на главный вопрос конструкции "В иерархии". В описании формулировка:
Поскольку на третьем уровне иерархии не присутствует ни одна группа справочника, то данный запрос более не выполняется


Чтобы выяснить это "Поскольку", на ум приходит только рекурсивные запросы начиная с корня для каждого последующего уровня, до тех пор, пока не получим пустой результат. То есть конструкция, которая не может быть отработана за одно обращение к СУБД (точнее одним запросом со стороны 1С).

Хорошо бы раскрыть этот ключевой нюанс данной конструкции.
Dnki; RickyTickyTok; ogoneksergei; YPermitin; +4 Ответить
15. Dach 371 16.07.19 18:01 Сейчас в теме
(13)

Платформа при трансляции текста запроса задает глубину вложенности исходя из настроек справочника

на ум приходит только рекурсивные запросы начиная с корня


посмотрите (10), в первой публикации как раз этот вопрос наиболее полно и раскрыт
YPermitin; +1 Ответить
20. Froloid 66 17.07.19 11:25 Сейчас в теме
(15) То есть в указанном примере в конфигурации установлено ограничение по максимальной глубине в 3 уровня? С трудом верится.
21. Dach 371 17.07.19 12:47 Сейчас в теме
(20) а Вы и не верьте)) возьмите профайлер и потрассируйте запросы, меняя уровень иерархии
16. Dorosh 176 16.07.19 18:47 Сейчас в теме
Принято ругать конструкцию В ИЕРАРХИИ за тормознутость, но родной для скуля CTE работает еще медленнее. Как-то движимый любопытством написал и протестировал скульный запрос с CTE и 1с с иерархией. Оба запроса возвращали одинаковый набор данных из спр Контрагенты. Иерархия победила, за счет параллелизма. Рекурсивный запрос принципиально выполняется в 1 поток.
YPermitin; +1 Ответить
19. logarifm 1116 17.07.19 08:41 Сейчас в теме
Наглядные примеры того как не стоит обращаться из избыточными операторами. Но иногда без этого не обойтись. Ведь пользователю глубоко пофигу на производительность системы. Это наши проблемы. По этому еще интересней, когда В ИЕРАРХИИ и список групп они указывают.

Вообще автор молодец уже много статей написано по производительности и моменты работы Движка SQL. Ждем последующих статей...

ЗЫ. От себя еще хочу добавить СКД серьезно теряет производительность при группировке колонок (ведь за кулиссами там происходит нечто таинственное)
24. FreeArcher 158 18.07.19 06:53 Сейчас в теме
А если мы используем конструкцию В ИЕРАРХИИ (&П), но в параметр подставляется не группа, а элемент. Запросы будут усложнятся или платформа поймет, что эта запись равнозначна =&П?
25. SlavaKron 18.07.19 09:31 Сейчас в теме
(24) Есть ведь справочники с иерархией элементов, то есть родителем одного элемента является другой элемент.
26. FreeArcher 158 18.07.19 10:20 Сейчас в теме
(25) Есть. Но у меня вопрос когда элемент не имеет подчиненных.
Я хочу понять делая универсальным вызовы в запросе используя В или В ИЕРАРХИИ предполагая, что параметры могут принять массив значений или группу утяжеляем ли мы план запросов или 1С "умная" и она поймет данную ситуацию?
sasgol; frkbvfnjh; +2 Ответить
27. Evil Beaver 8100 18.07.19 11:24 Сейчас в теме
(44) Смотришь на автора, сначала плюсуешь, потом читаешь.
31. frkbvfnjh 785 13.11.19 10:53 Сейчас в теме
Меня огорчает лишь одно - нельзя использовать конструкцию В ИЕРАРХИИ при соединении таблиц и мне пофигу как долго бы этот запрос выполнялся, жаль что 1С не хочет "замутить" такое :(
Оставьте свое сообщение