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

← На главную

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

Анализ причин зависания запроса

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

Разница в индексах и физическом хранении полей Регистратор и Субконто. Поле Регистратор хранится в базе данных напрямую и входит в состав кластерных и основных индексов таблиц регистра бухгалтерии. Поле Субконто имеет составной тип данных. В зависимости от режима регистра оно либо хранится в отдельной таблице значений субконто, либо разворачивается в набор служебных колонок. Соединение по нетипизированному полю субконто транслируется в SQL с использованием конструкций CASE и множественных условий, из-за чего СУБД не может применить индекс и переходит к полному сканированию таблиц (Table Scan).
Коррелированный подзапрос в секции соединения. Конструкция В (ВЫБРАТЬ МИНИМУМ(Период) ... ГДЕ ...) внутри условия ПО заставляет СУБД выполнять внутренний запрос заново для каждой строки внешней таблицы. В сочетании с тяжелой виртуальной таблицей оборотов и соединением по нетипизированному полю оптимизатор выбирает крайне неэффективный план (вложенные циклы Nested Loops), что вешает сервер.
Неявные соединения при разыменовании составного типа. Обращения вида ХозрасчетныйОстатки.Субконто3.Дата и ХозрасчетныйОстатки.Субконто3.Номер без явного ограничения типа приводят к тому, что платформа генерирует LEFT JOIN со всеми возможными таблицами документов конфигурации, которые могут выступать в роли третьего субконто.
Сравнение счетов по коду через цепочку ИЛИ. Условие вида Счет.Код = "60.01" ИЛИ Счет.Код = "60.21" ... в параметрах виртуальной таблицы препятствует эффективной фильтрации. Счета необходимо передавать через параметры в виде массива ссылок либо использовать иерархический отбор Счет В ИЕРАРХИИ (&Счет60).

Пошаговый план оптимизации

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

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

Практическая реализация оптимального запроса

Посмотрим на пример правильной структуры запроса в 1С:


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

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

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

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

Настройка параметров запроса во встроенном языке

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


Запрос = Новый Запрос;
Запрос.Текст = "..."; // Текст запроса приведен выше

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

// Заполняем массив счетов
МассивСчетов = Новый Массив;
МассивСчетов.Добавить(ПланыСчетов.Хозрасчетный.РасчетыСПоставщиками);
МассивСчетов.Добавить(ПланыСчетов.Хозрасчетный.РасчетыСПоставщикамиВал);
МассивСчетов.Добавить(ПланыСчетов.Хозрасчетный.РасчетыСПоставщикамиУЕ);

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

Результат = Запрос.Выполнить();

Альтернативные способы решения

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

Прямое обращение к данным документа: если третье субконто гарантированно хранит документ расчетов (например, Документ.ПоступлениеТоваровУслуг), зачастую быстрее соединить полученную временную таблицу остатков напрямую с табличной частью или шапкой документа, чем повторно рассчитывать виртуальную таблицу оборотов регистра за весь период работы базы.
Использование специализированных регистров накопления: в типовых конфигурациях («1С:ERP», «Комплексная автоматизация», «Управление торговлей») взаиморасчеты ведутся по специализированным регистрам накопления (например, РасчетыСКлиентами, РасчетыСПоставщиками). Запросы к регистрам накопления выполняются значительно быстрее за счет более простой структуры индексов по сравнению с регистром бухгалтерии.