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

Начало

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

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

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

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

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

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

Конечно, на изображении показаны не все записи справочника. Скриншот показывает лишь структуру хранения данных в иерархическом справочнике. В таблице хранятся 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". На сервере СУБД платформа выполнит ряд запросов.

INSERT INTO #tt8 WITH(TABLOCK) (_REFFIELDRRef) 

SELECT
    T1._IDRRef
FROM dbo._Acc22 T1
WHERE ((T1._Fld774 = @P1)) AND ((T1._ParentIDRRef IN (@P2)))

UNION 

SELECT
    T2._IDRRef
FROM dbo._Acc22 T2
WHERE T2._IDRRef IN (@P4)

INSERT INTO #tt8 WITH(TABLOCK) (_REFFIELDRRef)

SELECT
    T1._IDRRef
FROM dbo._Acc22 T1
    INNER JOIN #tt8 T2 WITH(NOLOCK)
        ON T1._ParentIDRRef = T2._REFFIELDRRef
    LEFT OUTER JOIN #tt8 T3 WITH(NOLOCK)
        ON T1._IDRRef = T3._REFFIELDRRef
    WHERE T3._REFFIELDRRef IS NULL

INSERT INTO #tt16 WITH(TABLOCK) (_IDRRef, _Cnt)

SELECT
    T1._IDRRef, -- Ссылка на счет
    CAST(COUNT_BIG(T2._Acc22_IDRRef) AS NUMERIC(12)) -- Количество видов субконто
FROM dbo._Acc22 T1
    LEFT OUTER JOIN dbo._Acc22_ExtDim779 T2
        ON T2._Acc22_IDRRef = T1._IDRRef AND T2._Fld774 = T1._Fld774
-- Отбор по найденным ранее счетам с помощью выражения "В ИЕРАРХИИ"
WHERE (((T1._IDRRef IN (@P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13, @P14, @P15))))
GROUP BY T1._IDRRef

SELECT DISTINCT
T1._Cnt
FROM #tt16 T1 WITH(NOLOCK)

SELECT
    T1.Fld787RRef,
    T1.Fld788RRef,
    T1.Fld789RRef,
    T1.AccountRRef,
    T1.Value1_TYPE,
    T1.Value1_RTRef,
    T1.Value1_RRRef,
    T1.Value2_TYPE,
    T1.Value2_RTRef,
    T1.Value2_RRRef,
    T1.Value3_TYPE,
    T1.Value3_RTRef,
    T1.Value3_RRRef,
    T1.Fld790Balance_
FROM (SELECT
        T2.Value1_TYPE AS Value1_TYPE,
        T2.Value1_RTRef AS Value1_RTRef,
        T2.Value1_RRRef AS Value1_RRRef,
        T2.AccountRRef AS AccountRRef,
        T2.Value3_TYPE AS Value3_TYPE,
        T2.Value3_RTRef AS Value3_RTRef,
        T2.Value3_RRRef AS Value3_RRRef,
        T2.Fld787RRef AS Fld787RRef,
        T2.Fld788RRef AS Fld788RRef,
        T2.Fld789RRef AS Fld789RRef,
        T2.Value2_TYPE AS Value2_TYPE,
        T2.Value2_RTRef AS Value2_RTRef,
        T2.Value2_RRRef AS Value2_RRRef,
        CASE WHEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) IS NULL THEN 0.0 ELSE CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) END AS Fld790Balance_
    FROM (                                        
            SELECT
                T3._Value1_TYPE AS Value1_TYPE,
                T3._Value1_RTRef AS Value1_RTRef,
                T3._Value1_RRRef AS Value1_RRRef,
                T3._AccountRRef AS AccountRRef,
                CASE WHEN CAST(NULL AS BINARY(1)) IN (0x01) THEN CAST(NULL AS BINARY(1)) END AS Value3_TYPE,
                CASE WHEN CAST(NULL AS BINARY(1)) IS NOT NULL THEN 0x00000000 END AS Value3_RTRef,
                CASE WHEN CAST(NULL AS BINARY(1)) IS NOT NULL THEN @P1 END AS Value3_RRRef,
                T3._Fld787RRef AS Fld787RRef,
                T3._Fld788RRef AS Fld788RRef,
                T3._Fld789RRef AS Fld789RRef,
                CASE WHEN CAST(NULL AS BINARY(1)) IN (0x01) THEN CAST(NULL AS BINARY(1)) END AS Value2_TYPE,
                CASE WHEN CAST(NULL AS BINARY(1)) IS NOT NULL THEN 0x00000000 END AS Value2_RTRef,
                CASE WHEN CAST(NULL AS BINARY(1)) IS NOT NULL THEN @P2 END AS Value2_RRRef,
                T3._Fld790 AS Fld790Balance_
            FROM dbo._AccRgAT1819 T3
                -- Фильтр по счетам
                INNER JOIN #tt16 T4 WITH(NOLOCK)
                ON T4._IDRRef = T3._AccountRRef AND T4._Cnt = @P3
            WHERE ((T3._Fld774 = @P4)) AND (T3._Period = @P5 AND (T3._Fld790 <> @P6))

        UNION ALL

            SELECT
                T5._Value1_TYPE AS Value1_TYPE,
                T5._Value1_RTRef AS Value1_RTRef,
                T5._Value1_RRRef AS Value1_RRRef,
                T5._AccountRRef AS AccountRRef,
                CASE WHEN CAST(NULL AS BINARY(1)) IN (0x01) THEN CAST(NULL AS BINARY(1)) END AS Value3_TYPE,
                CASE WHEN CAST(NULL AS BINARY(1)) IS NOT NULL THEN 0x00000000 END AS Value3_RTRef,
                CASE WHEN CAST(NULL AS BINARY(1)) IS NOT NULL THEN @P7 END AS Value3_RRRef,
                T5._Fld787RRef AS Fld787RRef,
                T5._Fld788RRef AS Fld788RRef,
                T5._Fld789RRef AS Fld789RRef,
                T5._Value2_TYPE AS Value2_TYPE,
                T5._Value2_RTRef AS Value2_RTRef,
                T5._Value2_RRRef AS Value2_RRRef,
                T5._Fld790 AS Fld790Balance_
            FROM dbo._AccRgAT2820 T5
                -- Фильтр по счетам
                INNER JOIN #tt16 T6 WITH(NOLOCK)
                ON T6._IDRRef = T5._AccountRRef AND T6._Cnt = @P8
            WHERE ((T5._Fld774 = @P9)) AND (T5._Period = @P10 AND (T5._Fld790 <> @P11))

        UNION ALL

            SELECT
                T7._Value1_TYPE AS Value1_TYPE,
                T7._Value1_RTRef AS Value1_RTRef,
                T7._Value1_RRRef AS Value1_RRRef,
                T7._AccountRRef AS AccountRRef,
                T7._Value3_TYPE AS Value3_TYPE,
                T7._Value3_RTRef AS Value3_RTRef,
                T7._Value3_RRRef AS Value3_RRRef,
                T7._Fld787RRef AS Fld787RRef,
                T7._Fld788RRef AS Fld788RRef,
                T7._Fld789RRef AS Fld789RRef,
                T7._Value2_TYPE AS Value2_TYPE,
                T7._Value2_RTRef AS Value2_RTRef,
                T7._Value2_RRRef AS Value2_RRRef,
                T7._Fld790 AS Fld790Balance_
            FROM dbo._AccRgAT3821 T7
                -- Фильтр по счетам
                INNER JOIN #tt16 T8 WITH(NOLOCK)
                ON T8._IDRRef = T7._AccountRRef AND T8._Cnt = @P12
            WHERE ((T7._Fld774 = @P13)) AND (T7._Period = @P14 AND (T7._Fld790 <> @P15))) T2
    GROUP BY 
            T2.Value1_TYPE,
            T2.Value1_RTRef,
            T2.Value1_RRRef,
            T2.AccountRRef,
            T2.Value3_TYPE,
            T2.Value3_RTRef,
            T2.Value3_RRRef,
            T2.Fld787RRef,
            T2.Fld788RRef,
            T2.Fld789RRef,
            T2.Value2_TYPE,
            T2.Value2_RTRef,
            T2.Value2_RRRef
    HAVING (CASE WHEN CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) IS NULL THEN 0.0 ELSE CAST(SUM(T2.Fld790Balance_) AS NUMERIC(27, 2)) END) <> 0.0) T1

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

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

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

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

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

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

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

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

• Некоторые полезные новичкам платформы 1С8.х приёмы работы с запросами - 2 часть

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

• Как мы ускорили обмены с 10 часов до нескольких минут в компания Сотовик

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

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

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

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

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

• Некоторые полезные новичкам платформы 1С8.х приёмы работы с запросами - 2 часть

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

• Как мы ускорили обмены с 10 часов до нескольких минут в компания Сотовик