Как правильно составить и оптимизировать запрос для получения остатков по табличной части документа в 1С

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

Анализ типичных ошибок в исходном запросе

Рассмотрим распространенный вариант запроса, который часто пишут без учета стандартов разработки 1С:

ВЫБРАТЬ
    ВозвратМатериаловИзЭксплуатацииМатериалы.Ссылка.ПодразделениеОрганизации,
    ВозвратМатериаловИзЭксплуатацииМатериалы.Номенклатура,
    ВозвратМатериаловИзЭксплуатацииМатериалы.ХарактеристикаНоменклатуры,
    ВозвратМатериаловИзЭксплуатацииМатериалы.СерияНоменклатуры,
    ВозвратМатериаловИзЭксплуатацииМатериалы.ФизЛицо,
    ВозвратМатериаловИзЭксплуатацииМатериалы.Количество,
    ЕСТЬNULL(МатериалыВЭксплуатацииОстатки.КоличествоОстаток, 0) КАК ОстатокВЭксплуатации
ИЗ
    Документ.ВозвратМатериаловИзЭксплуатации.Материалы КАК ВозвратМатериаловИзЭксплуатацииМатериалы
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.МатериалыВЭксплуатации.Остатки(&МоментВремени, ) КАК МатериалыВЭксплуатацииОстатки
        ПО ВозвратМатериаловИзЭксплуатацииМатериалы.Номенклатура = МатериалыВЭксплуатацииОстатки.Номенклатура
        И ВозвратМатериаловИзЭксплуатацииМатериалы.ФизЛицо = МатериалыВЭксплуатацииОстатки.ФизЛицо
        И ВозвратМатериаловИзЭксплуатацииМатериалы.Ссылка.ПодразделениеОрганизации = МатериалыВЭксплуатацииОстатки.Подразделение
        И ВозвратМатериаловИзЭксплуатацииМатериалы.ХарактеристикаНоменклатуры = МатериалыВЭксплуатацииОстатки.ХарактеристикаНоменклатуры
ГДЕ
    ВозвратМатериаловИзЭксплуатацииМатериалы.Ссылка = &Ссылка

Проанализируем основные узкие места и недочеты этой конструкции:

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

Оптимальная схема: временная таблица и фильтрация в виртуальной таблице

Разберем по шагам правильный алгоритм построения запроса:

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

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

ВЫБРАТЬ
    ТабЧасть.Номенклатура КАК Номенклатура,
    ТабЧасть.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    ТабЧасть.ФизЛицо КАК ФизЛицо,
    &Подразделение КАК Подразделение,
    СУММА(ТабЧасть.Количество) КАК Количество
ПОМЕСТИТЬ ВТ_КСписанию
ИЗ
    Документ.ВозвратМатериаловИзЭксплуатации.Материалы КАК ТабЧасть
ГДЕ
    ТабЧасть.Ссылка = &Ссылка
СГРУППИРОВАТЬ ПО
    ТабЧасть.Номенклатура,
    ТабЧасть.ХарактеристикаНоменклатуры,
    ТабЧасть.ФизЛицо
ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    ХарактеристикаНоменклатуры,
    ФизЛицо,
    Подразделение
;

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

Рекомендации по дальнейшей оптимизации

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

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

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

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