Как правильно оптимизировать запрос к регистру накопления и движениям регистра бухгалтерии в 1С?

← На главную

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

Рассмотрим исходный текст запроса, который требовалось оптимизировать:


ВЫБРАТЬ
    ЗакупкиОбороты.Контрагент
ПОМЕСТИТЬ ВТ_Контрагенты
ИЗ
    РегистрНакопления.Закупки.Обороты(
            &ДатаНачала,
            &ДатаОкончания,
            ,
            Организация = &Организация
                И Валюта = &Валюта) КАК ЗакупкиОбороты
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВЫБОР
        КОГДА УправленческийДвиженияССубконто.СчетДт В ИЕРАРХИИ (&СчетГруппа)
            ТОГДА УправленческийДвиженияССубконто.СубконтоДт1
        ИНАЧЕ УправленческийДвиженияССубконто.СубконтоКт1
    КОНЕЦ КАК Контрагент,
    ВЫБОР
        КОГДА УправленческийДвиженияССубконто.СчетДт В ИЕРАРХИИ (&СчетГруппа)
            ТОГДА УправленческийДвиженияССубконто.СубконтоДт1.Описание
        ИНАЧЕ УправленческийДвиженияССубконто.СубконтоКт1.Описание
    КОНЕЦ КАК ОписаниеКонтрагента,
    УправленческийДвиженияССубконто.Сумма,
    УправленческийДвиженияССубконто.Регистратор,
    УправленческийДвиженияССубконто.Содержание
ИЗ
    РегистрБухгалтерии.Управленческий.ДвиженияССубконто(
            &ДатаНачала,
            &ДатаОкончания,
            Организация = &Организация
                И ((СчетДт В ИЕРАРХИИ (&СчетГруппа)
                        И СубконтоДт1 В
                            (ВЫБРАТЬ
                                ВТ_Контрагенты.Контрагент
                            ИЗ
                                ВТ_Контрагенты))
                    ИЛИ (СчетКт В ИЕРАРХИИ (&СчетГруппа)
                        И СубконтоКт1 В
                            (ВЫБРАТЬ
                                ВТ_Контрагенты.Контрагент
                            ИЗ
                                ВТ_Контрагенты))),
            ,
            ) КАК УправленческийДвиженияССубконто

Анализ проблем производительности в исходном запросе

Проанализируем структуру запроса и выделим критические ошибки, приводящие к падению скорости выполнения в СУБД:

Неявное разыменование составного типа данных через точку: поле СубконтоДт1 (и СубконтоКт1) имеет составной тип. Конструкция вида СубконтоДт1.Описание заставляет платформу генерировать множество соединений (LEFT JOIN) со всеми таблицами типов, входящих в данный план видов характеристик. Это создает колоссальную нагрузку на СУБД.
Логическое условие ИЛИ в параметрах виртуальной таблицы: при объединении условий по дебету и кредиту через ИЛИ оптимизатор SQL не может применить сканирование по индексам и переходит к полному сканированию таблиц (Table Scan / Index Scan).
Вложенные полусоединения в фильтре виртуальной таблицы: конструкции В (ВЫБРАТЬ Контрагент ИЗ ВТ_Контрагенты) внутри сложного условия с ИЛИ мешают оптимизатору эффективно оценить кардинальность и план запроса.
Избыточное использование РАЗЛИЧНЫЕ: часто разработчики ошибочно пытаются добавить РАЗЛИЧНЫЕ в первый запрос пакета. Виртуальная таблица оборотов (РегистрНакопления.Закупки.Обороты) по умолчанию возвращает записи, свернутые по выбранным измерениям, поэтому дублирование записей по измерению Контрагент исключено.
Многократное вычисление выражения ВЫБОР: одинаковая логика определения контрагента и его реквизитов дублируется в секции ВЫБРАТЬ и в условиях.

Шаг 1. Устранение неявных соединений для составного типа

Выясним причину замедления при обращении к реквизитам субконто. Чтобы ограничить список соединяемых таблиц конкретным справочником, необходимо использовать явное приведение типа через функцию ВЫРАЗИТЬ() или вынести получение описания в отдельное соединение со справочником контрагентов.

Рассмотрим применение ВЫРАЗИТЬ в выражении:


ВЫРАЗИТЬ(УправленческийДвиженияССубконто.СубконтоДт1 КАК Справочник.Контрагенты).Описание

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

Шаг 2. Разделение условия «ИЛИ» на объединение запросов («ОБЪЕДИНИТЬ ВСЕ»)

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

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


// Ветка 1: Проводки по Дебету
ВЫБРАТЬ
    Движения.СубконтоДт1 КАК Контрагент,
    Движения.Сумма КАК Сумма,
    Движения.Регистратор КАК Регистратор,
    Движения.Содержание КАК Содержание
ИЗ
    РегистрБухгалтерии.Управленческий.ДвиженияССубконто(
            &ДатаНачала,
            &ДатаОкончания,
            Организация = &Организация
                И СчетДт В ИЕРАРХИИ (&СчетГруппа)
                И СубконтоДт1 В (ВЫБРАТЬ ВТ.Контрагент ИЗ ВТ_Контрагенты КАК ВТ),
            ,
            ) КАК Движения

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

// Ветка 2: Проводки по Кредиту
ВЫБРАТЬ
    Движения.СубконтоКт1 КАК Контрагент,
    Движения.Сумма КАК Сумма,
    Движения.Регистратор КАК Регистратор,
    Движения.Содержание КАК Содержание
ИЗ
    РегистрБухгалтерии.Управленческий.ДвиженияССубконто(
            &ДатаНачала,
            &ДатаОкончания,
            Организация = &Организация
                И СчетКт В ИЕРАРХИИ (&СчетГруппа)
                И СубконтоКт1 В (ВЫБРАТЬ ВТ.Контрагент ИЗ ВТ_Контрагенты КАК ВТ),
            ,
            ) КАК Движения

Шаг 3. Итоговый оптимизированный вариант запроса

Соберем все рекомендации воедино: подготовим список контрагентов с их реквизитами во временной таблице, разобьем выборку движений на две независимые ветки без тяжелых конструкций ВЫБОР и соединим результат с временной таблицей контрагентов.


// 1. Получаем список контрагентов и сразу извлекаем реквизит Описание
ВЫБРАТЬ
    ЗакупкиОбороты.Контрагент КАК Контрагент,
    ЗакупкиОбороты.Контрагент.Описание КАК ОписаниеКонтрагента
ПОМЕСТИТЬ ВТ_Контрагенты
ИЗ
    РегистрНакопления.Закупки.Обороты(
            &ДатаНачала,
            &ДатаОкончания,
            ,
            Организация = &Организация
                И Валюта = &Валюта) КАК ЗакупкиОбороты

ИНДЕКСИРОВАТЬ ПО
    Контрагент
;

////////////////////////////////////////////////////////////////////////////////
// 2. Получаем движения и соединяем с подготовленной таблицей контрагентов
ВЫБРАТЬ
    Проводки.Контрагент КАК Контрагент,
    ВТ_Контрагенты.ОписаниеКонтрагента КАК ОписаниеКонтрагента,
    Проводки.Сумма КАК Сумма,
    Проводки.Регистратор КАК Регистратор,
    Проводки.Содержание КАК Содержание
ИЗ
    (
        // Дебетовые движения
        ВЫБРАТЬ
            ДвиженияДт.СубконтоДт1 КАК Контрагент,
            ДвиженияДт.Сумма КАК Сумма,
            ДвиженияДт.Регистратор КАК Регистратор,
            ДвиженияДт.Содержание КАК Содержание
        ИЗ
            РегистрБухгалтерии.Управленческий.ДвиженияССубконто(
                    &ДатаНачала,
                    &ДатаОкончания,
                    Организация = &Организация
                        И СчетДт В ИЕРАРХИИ (&СчетГруппа)
                        И СубконтоДт1 В
                            (ВЫБРАТЬ
                                ВТ.Контрагент
                            ИЗ
                                ВТ_Контрагенты КАК ВТ),
                    ,
                    ) КАК ДвиженияДт

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

        // Кредитовые движения
        ВЫБРАТЬ
            ДвиженияКт.СубконтоКт1 КАК Контрагент,
            ДвиженияКт.Сумма КАК Сумма,
            ДвиженияКт.Регистратор КАК Регистратор,
            ДвиженияКт.Содержание КАК Содержание
        ИЗ
            РегистрБухгалтерии.Управленческий.ДвиженияССубконто(
                    &ДатаНачала,
                    &ДатаОкончания,
                    Организация = &Организация
                        И СчетКт В ИЕРАРХИИ (&СчетГруппа)
                        И СубконтоКт1 В
                            (ВЫБРАТЬ
                                ВТ.Контрагент
                            ИЗ
                                ВТ_Контрагенты КАК ВТ),
                    ,
                    ) КАК ДвиженияКт
    ) КАК Проводки
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Контрагенты КАК ВТ_Контрагенты
        ПО Проводки.Контрагент = ВТ_Контрагенты.Контрагент

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

Невозможность замены на Обороты: в запросе требуются детальные поля Регистратор и Содержание. Виртуальные таблицы Обороты и ОстаткиИОбороты агрегируют данные, поэтому для сохранения регистратора использование таблицы ДвиженияССубконто (либо реальной таблицы регистра) является обязательным.
Фильтрация по субконто: обращение к СубконтоДт1 / СубконтоКт1 корректно только тогда, когда вид субконто «Контрагенты» назначен первым на всех счетах выбранной группы. Если номер субконто на счетах различается, необходимо использовать параметры фильтрации по видам субконто в самой виртуальной таблице.
Индексация временной таблицы: директива ИНДЕКСИРОВАТЬ ПО для временной таблицы ВТ_Контрагенты полезна при соединении с большими объемами проводок, так как помогает СУБД выбрать оптимальный план соединения (например, Index Seek или Merge Join).