Как правильно оптимизировать запрос получения остатков из регистра накопления

← На главную

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

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

Отвечая на вопрос о механике соединения двух таблиц: соединение не является обязательным построчным циклом. В реляционных СУБД, таких как Microsoft SQL Server или PostgreSQL, физическая реализация логического оператора соединения (JOIN) выбирается оптимизатором запросов на основе статистических данных, объема таблиц и наличия индексов. Проанализируем три основных алгоритма соединения, используемых СУБД:

Nested Loops (Вложенные циклы). Это действительно построчный перебор. Для каждой строки внешней (ведущей) таблицы СУБД ищет соответствующие строки во внутренней таблице. Данный алгоритм выбирается оптимизатором только тогда, когда ведущая таблица содержит небольшое количество строк, а ведомая таблица имеет эффективный индекс по полям связи (Index Seek). Если данных много, а индекса нет, вложенные циклы приводят к критическому падению производительности.
Hash Match (Хэш-соединение). СУБД сканирует меньшую таблицу, строит по полям связи хэш-таблицу в оперативной памяти, а затем один раз сканирует вторую таблицу, вычисляя хэш и находя совпадения. Этот алгоритм превосходно масштабируется на больших объемах несортированных данных.
Merge Join (Соединение слиянием). Наиболее эффективный алгоритм, когда обе таблицы уже отсортированы по полям соединения (например, с использованием кластерных индексов). СУБД одновременно проходит по двум наборам данных за один линейный проход.

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

Особенности работы виртуальных таблиц остатков

Чтобы понять, как оптимизировать запрос, выясним внутреннюю механику работы регистра накопления. В физической структуре базы данных 1С остатки хранятся в таблице итогов (например, _AccumRgT), а детальные записи — в таблице движений (_AccumRg). Виртуальная таблица Остатки представляет собой готовый подзапрос, формируемый платформой.

Критически важно помнить следующее правило: условия отбора всегда должны передаваться в параметры самой виртуальной таблицы. Если передать условие номенклатуры в секцию ГДЕ основного запроса или только в условие связи ПО, платформа и СУБД сначала рассчитают остатки по абсолютно всем позициям регистра в базе, и лишь после этого отсекут ненужное. Это порождает гигантский объем чтений с диска и перегрузку процессора.

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

Когда нужна временная таблица номенклатуры

Рассмотрим сценарии работы со списком номенклатуры. Нужно ли выбирать справочник Номенклатура во временную таблицу только ради передачи в виртуальную таблицу остатков?

Если требуется получить остатки по всему справочнику: помещать справочник во временную таблицу бессмысленно и вредно. Это создаст лишнюю нагрузку на служебную базу tempdb (запись и последующее удаление данных). В такой ситуации достаточно обратиться напрямую к виртуальной таблице остатков без условий отбора.
Если список позиций ограничен (например, строки документа или выбранный массив): в этом случае целесообразно загрузить список во временную таблицу, обязательно указав инструкцию ИНДЕКСИРОВАТЬ ПО. Без явного индекса временная таблица создается без первичного ключа, и СУБД будет выполнять ее полное сканирование (Table Scan).

При этом важно учитывать способ передачи списка. Конструкция вида В (&МассивНоменклатуры) транслируется в SQL-запрос как перечисление параметров IN (@P1, @P2, ...). В Microsoft SQL Server действует жесткое ограничение на 2100 параметров в одном запросе. Кроме того, при передаче сотен параметров фаза компиляции плана запроса существенно замедляется. Передача фильтра через временную таблицу лишена этого недостатка.

Оптимальные шаблоны запросов

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

Вариант 1. Получение остатков только по позициям документа (ненулевые остатки)

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


ВЫБРАТЬ РАЗЛИЧНЫЕ
    ДокументТабЧасть.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_Товары
ИЗ
    Документ.РеализацияТоваровУслуг.Товары КАК ДокументТабЧасть
ГДЕ
    ДокументТабЧасть.Ссылка = &Ссылка
ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;

ВЫБРАТЬ
    Остатки.Номенклатура КАК Номенклатура,
    Остатки.ВНаличииОстаток КАК Количество
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(
        &Период,
        Номенклатура В (ВЫБРАТЬ ВТ_Товары.Номенклатура ИЗ ВТ_Товары КАК ВТ_Товары)
    ) КАК Остатки

Вариант 2. Полный список позиций с остатками (включая нулевые остатки)

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


ВЫБРАТЬ РАЗЛИЧНЫЕ
    ДокументТабЧасть.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_Товары
ИЗ
    Документ.РеализацияТоваровУслуг.Товары КАК ДокументТабЧасть
ГДЕ
    ДокументТабЧасть.Ссылка = &Ссылка
ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;

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

Чек-лист для анализа узких мест производительности

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

Проверяем актуальность рассчитанных итогов. Если дата остатков не совпадает с границей рассчитанных итогов регистра, платформа читает не только таблицу _AccumRgT, но и миллионы записей движений из _AccumRg за весь период смещения. Регулярный пересчет итогов регламентным заданием критически важен.
Проверяем наполненность списка во встроенном языке. Если временная таблица отбора оказывается пустой, запрос все равно отправится в СУБД. Перед вызовом запроса всегда стоит проверить коллекцию на заполненность.
Анализируем план выполнения. Если производительность вызывает вопросы, необходимо снять реальный план запроса в СУБД (через трассировку или технологический журнал) и убедиться, что оптимизатор не выполняет сканирование таблиц вместо индексного поиска.