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

При проведении документов взаиморасчетов (например, документа ВозвратОтПокупателя) одной из самых ресурсоемких операций нередко становится получение остатков по счетам расчетов с контрагентами. Запрос к регистру бухгалтерии может занимать значительную долю общего времени проведения, вызывая заметные задержки у пользователей. Проанализируем типичные ошибки при написании запросов к виртуальной таблице РегистрБухгалтерии.Хозрасчетный.Остатки и пошагово разберем методы ускорения выборки данных.

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

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

ВЫБРАТЬ
    Остатки.Счет,
    Остатки.Субконто1,
    Остатки.Субконто2,
    Остатки.Субконто3,
    ВЫРАЗИТЬ(СУММА(ВЫБОР
        КОГДА Остатки.СуммаОстаток ЕСТЬ NULL ТОГДА 0
        ИНАЧЕ Остатки.СуммаОстаток
    КОНЕЦ) КАК ЧИСЛО) КАК СуммаОстаток,
    ВЫБОР
        КОГДА Остатки.Счет.Валютный = ИСТИНА ТОГДА Остатки.Валюта
        ИНАЧЕ НЕОПРЕДЕЛЕНО
    КОНЕЦ КАК Валюта,
    ВЫРАЗИТЬ(СУММА(ВЫБОР
        КОГДА Остатки.ВалютнаяСуммаОстаток ЕСТЬ NULL ТОГДА 0
        ИНАЧЕ Остатки.ВалютнаяСуммаОстаток
    КОНЕЦ) КАК ЧИСЛО) КАК ВалютнаяСуммаОстаток
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Остатки(
        &Период,
        Счет = &Счет,
        ,
        Организация = &Организация
            И Субконто1 = &Субконто1
            И Субконто2 = &Субконто2
    ) КАК Остатки
СГРУППИРОВАТЬ ПО
    Остатки.Счет,
    Остатки.Субконто1,
    Остатки.Субконто2,
    Остатки.Субконто3,
    Остатки.Валюта,
    ВЫБОР
        КОГДА Остатки.Счет.Валютный = ИСТИНА ТОГДА Остатки.Валюта
        ИНАЧЕ НЕОПРЕДЕЛЕНО
    КОНЕЦ
УПОРЯДОЧИТЬ ПО
    Остатки.Счет,
    Остатки.Субконто1,
    Остатки.Субконто2,
    Остатки.Субконто3,
    Валюта

Выясним причину низкой производительности. В данном тексте допущен целый ряд распространенных ошибок оптимизации:

1. Лишняя группировка (СГРУППИРОВАТЬ ПО): Виртуальная таблица остатков регистра бухгалтерии сама по себе является агрегирующей. Она автоматически сворачивает остатки по тем измерениям и субконто, которые выбраны в секции ВЫБРАТЬ. Наложение дополнительной группировки силами СУБД создает бессмысленную нагрузку на сервер баз данных.
2. Избыточные конструкции проверки на NULL: В полях ресурсов виртуальной таблицы остатков (СуммаОстаток, ВалютнаяСуммаОстаток) значение NULL появиться не может в принципе, если к таблице не применяется внешнее левое соединение. Конструкции СУММА(ВЫБОР КОГДА ... ЕСТЬ NULL ...) избыточны и нагружают план выполнения запроса.
3. Пропущенный параметр видов субконто: Третий параметр виртуальной таблицы остатков предназначен для передачи массива или списка видов субконто. Если он не заполнен, платформа обрабатывает субконто как поля неограниченного составного типа данных, что существенно осложняет построение плана запроса на уровне СУБД.
4. Неявное соединение через точку (Остатки.Счет.Валютный): Обращение через точку к реквизиту виртуальной таблицы порождает неявное соединение (LEFT JOIN) с таблицей плана счетов. Поскольку счет уже передается жестким параметром &Счет, выяснять валютность счета внутри запроса нерационально.
5. Бессмысленная сортировка (УПОРЯДОЧИТЬ ПО): Сортировка ссылочных полей без использования механизма автоупорядочивания упорядочивает записи по их внутренним идентификаторам (GUID), что лишено бизнес-логики и тратит время процессора СУБД. Кроме того, по конкретному договору и контрагенту на счете взаиморасчетов результат содержит единичную запись.

Шаги по оптимизации запроса

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

Шаг 1: Исключаем группировку и лишние проверки на NULL.

Удаляем конструкцию СГРУППИРОВАТЬ ПО и убираем агрегатную функцию СУММА() вместе с громоздкими выражениями ВЫБОР. Ресурсы СуммаОстаток и ВалютнаяСуммаОстаток выбираем напрямую.

Шаг 2: Задаем параметр ВидыСубконто.

В коде перед выполнением запроса формируем массив с конкретными видами субконто и передаем его в третий параметр виртуальной таблицы. Это обеспечивает строгую типизацию полей Субконто1, Субконто2 и кардинально ускоряет работу СУБД:

МассивСубконто = Новый Массив;
МассивСубконто.Добавить(ПланыВидовХарактеристик.ВидыСубконтоХозрасчетные.Контрагенты);
МассивСубконто.Добавить(ПланыВидовХарактеристик.ВидыСубконтоХозрасчетные.Договоры);

Запрос.УстановитьПараметр("ВидыСубконто", МассивСубконто);

Шаг 3: Упрощаем работу с валютой.

Если необходимо защититься от неопределенного значения поля валюты при невалютном счете, достаточно использовать функцию ЕСТЬNULL(Остатки.Валюта, НЕОПРЕДЕЛЕНО) вместо обращения к реквизиту Остатки.Счет.Валютный, исключив неявный LEFT JOIN.

Шаг 4: Удаляем секцию УПОРЯДОЧИТЬ ПО.

Удаление ненужной сортировки исключает операцию сортировки наборов данных на сервере СУБД.

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

Посмотрим на пример запроса после внесенных корректировок:

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

Применение данных исправлений позволяет сократить время выполнения запроса более чем в 3 раза.

Дополнительные факторы, влияющие на производительность остатков

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

1. Актуальность рассчитанных итогов регистра бухгалтерии: Если итоги по регистру не рассчитаны на текущую дату, платформа вынуждена вычислять остатки динамически, последовательно считывая все записи физической таблицы движений (проводок) от последней зафиксированной точки итогов. Для исправления ситуации необходимо выполнить пересчет итогов и настроить регулярное регламентное задание по расчету итогов вперед на следующий расчетный период.
2. Передача периода в виртуальную таблицу при оперативном проведении: Если остатки считываются текущим моментом и движения документа еще не сформированы, передача в параметр &Период значения Неопределено заставляет платформу считывать данные напрямую из таблицы актуальных итогов, минуя анализ таблицы движений. Если же остаток берется строго на момент времени документа, следует использовать объект Граница с параметром ВидГраницы.Исключая, чтобы исключить собственные проводки документа при повторном проведении.
3. Блокировки и параллельность: При параллельном проведении документов запросы к таблице остатков могут ожидать освобождения блокировок. Убедимся, что для регистра бухгалтерии включено свойство Разрешить разделение итогов, а перед чтением данных в транзакции устанавливаются явные управляемые блокировки по ключевым измерениям (Организация, Счет).
4. Статистика СУБД: Таблицы итогов регистра бухгалтерии подвержены частым изменениям при активном документообороте. Неактуальная статистика на сервере баз данных приводит к выбору ошибочного плана выполнения (например, сканированию всей таблицы итогов вместо индексного поиска). Регулярный пересчет статистики СУБД с высокой долей выборки предотвращает деградацию скорости чтения.