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

← На главную

При разработке на платформе 1С программисты часто сталкиваются с ситуацией, когда простой, на первый взгляд, запрос начинает «тормозить» на реальных данных. Особенно коварными являются соединения со справочниками и виртуальными таблицами регистров. В этой статье мы разберем конкретный пример неэффективного кода, проанализируем его SQL-трассировку и выясним, как превратить «тяжелый» запрос в молниеносный.

Проблема: Левое соединение без фильтров

Рассмотрим исходный код, который вызвал вопросы. Программист пытается получить одну запись из справочника Номенклатура и соединить её с остатками из регистра накопления:


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

На первый взгляд кажется, что ВЫБРАТЬ ПЕРВЫЕ 1 ограничит выборку и всё отработает быстро. Однако, взглянув на трассировку SQL, мы увидим иную картину. Платформа генерирует сложный подзапрос для получения остатков, который выполняется до того, как будет применено ограничение по количеству записей в основной таблице.

Анализируем SQL-трассировку: что видит СУБД

В трассировке (команде sp_executesql) мы видим, что обращение к виртуальной таблице .Остатки() превращается в LEFT OUTER JOIN (SELECT ...). Давайте проанализируем ключевые проблемные места этого SQL-кода:

Материализация подзапроса: СУБД вынуждена сначала выполнить всё, что находится внутри скобок LEFT OUTER JOIN. Поскольку параметры в виртуальную таблицу Остатки() не переданы, сервер начинает вычислять итоги по всем товарам, которые когда-либо были в регистре.
Конструкция HAVING: В трассировке присутствует блок HAVING (SUM(...) <> 0). Это стандартная механика 1С для отсечения нулевых остатков. На уровне базы данных это означает, что серверу нужно просуммировать все движения по каждой номенклатуре, чтобы проверить, не равен ли итог нулю. Если в базе миллионы записей движений, это вызовет огромную нагрузку на дисковую подсистему и процессор.
Отсутствие детерминированности: Использование TOP 1 (аналог ПЕРВЫЕ 1 в SQL) без ORDER BY (УПОРЯДОЧИТЬ ПО) заставляет SQL-сервер возвращать «любую» первую попавшуюся запись. Это мешает оптимизатору построить эффективный план, так как он не понимает, по какому индексу лучше всего искать эту «первую» строку.

Разбираем ситуацию: почему так происходит?

Выясним причину, почему соединение работает именно так. Платформа 1С проектировалась как универсальный инструмент. Когда мы пишем ЛЕВОЕ СОЕДИНЕНИЕ, мы просим систему: «Возьми все записи из левой таблицы и попытайся найти соответствия в правой». Но когда правая таблица — это виртуальные Остатки, система не может просто «заглянуть» в неё. Ей нужно сначала построить эту таблицу в памяти (или во временных структурах СУБД) и только потом соединять.

Проанализируем план запроса: если в справочнике Номенклатура 100 000 элементов, а в регистре — 500 000 записей итогов, то ради одной строки результата сервер может прочитать сотни тысяч записей. Это классический пример нерационального использования ресурсов.

Решение №1: Использование параметров виртуальной таблицы

Самый простой и эффективный способ оптимизации — перенос фильтров внутрь скобок виртуальной таблицы. Рассмотрим, как изменится код, если нам нужны остатки по конкретной номенклатуре или списку:


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

В этом случае в SQL-запросе внутри подзапроса появится условие WHERE T3._Fld8220RRef = @P1. СУБД мгновенно найдет нужные данные по индексу, не сканируя весь регистр. Запомните правило: условия в ПО и ГДЕ для виртуальных таблиц — это плохо, условия в параметрах () — это хорошо.

Решение №2: Временные таблицы (Золотой стандарт)

Если задача стоит выбрать «первую попавшуюся» номенклатуру (хотя в реальности такая задача встречается редко, обычно есть какой-то критерий выбора), правильнее будет разделить запрос на этапы. Разберем по шагам:

Выберем нужную нам номенклатуру во временную таблицу.
Проиндексируем временную таблицу.
Соединим временную таблицу с виртуальной таблицей остатков, передав временную таблицу как фильтр в параметры.

Посмотрим на пример реализации:


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

Такой подход заставляет 1С генерировать максимально оптимальный SQL. СУБД сначала выберет одну запись, а затем выполнит поиск в таблице итогов только по этому одному значению. Это исключает «раздувание» итогов и использование тяжелых HAVING по всей базе.

Дополнительные факторы: итоги и индексы

Если даже с фильтрами запрос работает медленно, проанализируем состояние базы данных:

Актуальность итогов: 1С рассчитывает остатки, суммируя данные от последней точки рассчитанных итогов. Если итоги в регистре не пересчитывались полгода, система будет вынуждена суммировать все движения за эти полгода «на лету» при каждом запросе. Проверьте актуальность итогов в консоли управления.
Индексация: Убедитесь, что измерение Номенклатура в регистре является индексируемым (в стандартных регистрах остатков это так по умолчанию).
Стандарты разработки: Рекомендуем ознакомиться с разделом «Типовые проблемы производительности» на ИТС. Там подробно описано, почему соединение с подзапросами (которыми являются виртуальные таблицы) — это антипаттерн для SQL-оптимизатора.

Подводя итог, можно сказать: трассировка — это отличный способ «заглянуть под капот» 1С. Видя в ней SUM, GROUP BY и HAVING по огромным таблицам без условий WHERE, вы сразу поймете, где именно зарыта проблема производительности вашего кода.