Почему тормозит левое соединение с условиями ИЛИ и обращением через точку в 1С?

← На главную

При разработке сложных аналитических отчетов в 1С разработчики нередко сталкиваются с ситуацией, когда запрос к таблицам объемом в сотни тысяч записей выполняется десятки минут или намертво вешает сервер СУБД. Проанализируем типичный пример такого проблемного запроса, выясним физические причины замедления на уровне СУБД и платформы 1С, а также подробно разберем правильные методы оптимизации.

Рассмотрим исходный фрагмент кода, вызвавший критическое падение производительности (время выполнения около 50 минут при объемах таблиц 200 тыс. и 850 тыс. строк):


СотрудникиИВендоры КАК СотрудникиИВендоры
    ЛЕВОЕ СОЕДИНЕНИЕ Итоговый КАК Итоговый
    ПО СотрудникиИВендоры.Месяц = Итоговый.Период
        И (СотрудникиИВендоры.Вендор = Итоговый.Номенклатура
            ИЛИ СотрудникиИВендоры.Вендор = Итоговый.НоменклатураРодитель
            ИЛИ СотрудникиИВендоры.Вендор = Итоговый.НоменклатураРодительРодитель
            ИЛИ СотрудникиИВендоры.Вендор = Итоговый.НоменклатураРодительРодительРодитель
            ИЛИ СотрудникиИВендоры.Вендор = Итоговый.НоменклатураРодительРодительРодительРодитель
            ИЛИ СотрудникиИВендоры.Вендор = Итоговый.НоменклатураРодительРодительРодительРодительРодитель)
        И (СотрудникиИВендоры.Проект = Итоговый.Проект
            ИЛИ СотрудникиИВендоры.Проект = Итоговый.Проект.Родитель
            ИЛИ СотрудникиИВендоры.Проект = Итоговый.Проект.РодительУровень2
            ИЛИ СотрудникиИВендоры.Проект = Итоговый.Проект.РодительУровень3
            ИЛИ СотрудникиИВендоры.Проект = Итоговый.Проект.РодительУровень4)
        И (СотрудникиИВендоры.Регион = Итоговый.Регион
            ИЛИ СотрудникиИВендоры.Регион = Итоговый.Регион.Родитель
            ИЛИ СотрудникиИВендоры.Регион = Итоговый.Регион.РодительУровень2
            ИЛИ СотрудникиИВендоры.Регион = Итоговый.Регион.РодительУровень3
            ИЛИ СотрудникиИВендоры.Регион = Итоговый.Регион.РодительУровень4)

Причины катастрофического падения производительности

Разберем детально, что происходит «под капотом» платформы 1С и СУБД (MS SQL Server или PostgreSQL) при выполнении подобного кода:

Неявные соединения при обращении через точку: Конструкции вида Итоговый.Проект.Родитель и Итоговый.Регион.РодительУровень4 означают разыменование ссылочных полей. Платформа 1С для каждой такой точки генерирует отдельное неявное соединение LEFT JOIN к физической таблице соответствующего справочника. В данном запросе формируются десятки скрытых соединений, что колоссально усложняет план выполнения.
Предикаты «ИЛИ» в условии соединения (Non-sargable joins): СУБД умеет эффективно соединять большие объемы данных методами Hash Match или Merge Join только при условии строгого равенства ключей (эквисоединение). Наличие логических условий ИЛИ между полями разных уровней иерархии блокирует использование хеш-соединений. Оптимизатор СУБД вынужден применять алгоритм вложенных циклов (Nested Loops) либо строить декартово произведение с последующей фильтрацией. Для 200 000 и 850 000 строк это означает сотни миллиардов операций сравнения в памяти и на диске в tempdb.
Проблема задвоения данных: Попытка соединить строку сразу со всеми уровнями иерархии через ИЛИ логически некорректна: если в правилах присутствуют и родительская группа, и дочерний элемент, одна строка исходных данных сдублируется, исказив итоговые числовые показатели отчета.

Способ 1. Нормализация иерархии через временные таблицы и объединение

Рассмотрим первый путь решения: устранение неявных точек и разворот иерархии в плоский вид перед операцией соединения. Наша цель — привести структуру данных к виду, где одному физическому факту соответствует связь без условий ИЛИ.

Разберем по шагам, как подготовить данные справочника или виртуальной таблицы:

Выбираем уникальные ссылки и их родителей без обращений через точку непосредственно в секции ПО.
Разворачиваем иерархию через ОБЪЕДИНИТЬ ВСЕ, приводя все уровни к единому полю.
Индексируем полученную временную таблицу.

Посмотрим на пример формирования плоской таблицы связей для справочника:


ВЫБРАТЬ РАЗЛИЧНЫЕ
    Спр.Ссылка КАК Элемент,
    Спр.Ссылка КАК УзелИерархии
ПОМЕСТИТЬ ВТ_ИерархияНоменклатуры
ИЗ
    Справочник.Номенклатура КАК Спр

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ РАЗЛИЧНЫЕ
    Спр.Ссылка,
    Спр.Родитель
ИЗ
    Справочник.Номенклатура КАК Спр
ГДЕ
    Спр.Родитель <> ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка)

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ РАЗЛИЧНЫЕ
    Спр.Ссылка,
    Спр.Родитель.Родитель
ИЗ
    Справочник.Номенклатура КАК Спр
ГДЕ
    Спр.Родитель.Родитель <> ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка)
;

ИНДЕКСИРОВАТЬ ПО
    Элемент,
    УзелИерархии
;

После подготовки таких таблиц для Номенклатуры, Проектов и Регионов основное соединение переписывается на строгое равенство (=). Это позволяет СУБД задействовать Hash Join, сокращая время работы с десятков минут до нескольких секунд.

Способ 2. Пошаговое (каскадное) сопоставление от частного к общему

Если в таблице СотрудникиИВендоры хранятся правила распределения (например, правило может быть задано на конкретный товар, а при его отсутствии — на группу товаров), логику сопоставления необходимо разделить по приоритетам. Это полностью исключает и операторы ИЛИ, и задвоение сумм.

Проанализируем алгоритм каскадного сопоставления:

Шаг 1: Выполняем точное соединение по конечному элементу (уровень 0) со строгим условием Факты.Номенклатура = Правила.Номенклатура. Найденные строки фиксируем во временной таблице результатов.
Шаг 2: Отбираем строки, для которых правило не определилось (Правило ЕСТЬ NULL). Для этого остатка выполняем соединение с правилами уже по Родитель 1-го уровня.
Шаг 3: Повторяем процедуру для оставшихся несопоставленных строк по родителям следующих уровней.
Шаг 4: Объединяем полученные порции через ОБЪЕДИНИТЬ ВСЕ.

Посмотрим на пример структуры такого запроса:


// Шаг 1: Точное сопоставление
ВЫБРАТЬ
    Итог.Период КАК Период,
    Итог.Номенклатура КАК Номенклатура,
    Итог.НоменклатураРодитель КАК НоменклатураРодитель,
    Итог.Сумма КАК Сумма,
    Правила.Сотрудник КАК Сотрудник
ПОМЕСТИТЬ ВТ_Сопоставлено_Уровень0
ИЗ
    ВТ_Итоговый КАК Итог
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Правила КАК Правила
        ПО Итог.Период = Правила.Период
        И Итог.Номенклатура = Правила.Вендор
;

// Шаг 2: Сопоставление остатка по родителю
ВЫБРАТЬ
    Ур0.Период КАК Период,
    Ур0.Номенклатура КАК Номенклатура,
    Ур0.НоменклатураРодитель КАК НоменклатураРодитель,
    Ур0.Сумма КАК Сумма,
    Правила.Сотрудник КАК Сотрудник
ПОМЕСТИТЬ ВТ_Сопоставлено_Уровень1
ИЗ
    ВТ_Сопоставлено_Уровень0 КАК Ур0
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Правила КАК Правила
        ПО Ур0.Период = Правила.Период
        И Ур0.НоменклатураРодитель = Правила.Вендор
ГДЕ
    Ур0.Сотрудник ЕСТЬ NULL
;

Способ 3. Архитектурное решение через транзитивное замыкание

Если задача сопоставления по иерархии возникает на регулярной основе в высоконагруженной базе данных, динамический обход уровней родителей через точки является архитектурной ошибкой. Наиболее надежный промышленный подход — реализация механизма транзитивного замыкания (Closure Table).

Разберем принципы реализации транзитивного замыкания:

В конфигурации создается непериодический независимый регистр сведений, например, СоставИерархииНоменклатуры.
Регистр содержит два ключевых измерения: Элемент (ссылка на конечный товар или группу) и РодительскийУзел (ссылка на любого предка по дереву иерархии, включая сам элемент).
Регистр заполняется автоматически в модуле менеджера или в обработчике ПриЗаписи справочника при создании или переносе элементов.

В этом случае в целевом аналитическом запросе полностью исчезают конструкции РодительРодитель... и условия ИЛИ. Запрос соединяется с физической предрассчитанной таблицей регистра:


СотрудникиИВендоры КАК СотрудникиИВендоры
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.СоставИерархииНоменклатуры КАК СвязиИерархии
        ПО СотрудникиИВендоры.Вендор = СвязиИерархии.РодительскийУзел
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Итоговый КАК Итоговый
        ПО СотрудникиИВендоры.Месяц = Итоговый.Период
        И СвязиИерархии.Элемент = Итоговый.Номенклатура

Такой запрос опирается на стандартные индексы СУБД по измерениям регистра сведений и отрабатывает за доли секунды даже на миллионных массивах данных.