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

← На главную

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

Разберем детально, почему это происходит, как платформа 1С и СУБД (например, MS SQL Server) обрабатывают подобные конструкции, и выясним, какими способами можно ускорить получение данных.

Сравнение двух вариантов запроса

Проанализируем ситуацию на конкретном примере выборки розничных цен для иерархического списка номенклатуры. Сначала формируется временная таблица с отобранными товарами (около 6000 записей):


ВЫБРАТЬ
    Номенклатура.Ссылка КАК Номенклатура
ПОМЕСТИТЬ ВТ_Товары
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    Номенклатура.Ссылка В ИЕРАРХИИ(&Ссылка);

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

Вариант 1 (с подзапросом в параметрах виртуальной таблицы):


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

Вариант 2 (без отбора по номенклатуре в параметрах среза):


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

На первый взгляд кажется, что Вариант 1 должен работать быстрее, так как в регистр сразу передается фильтр по нужным товарам. Однако на практике Вариант 1 работает существенно медленнее Варианта 2. Выясним причину такого поведения.

Механизм трансляции виртуальной таблицы платформой 1С

Виртуальная таблица СрезПоследних для непериодического или периодического регистра сведений не существует в СУБД в виде отдельной физической таблицы. Платформа 1С на лету преобразует обращение к ней в составной SQL-запрос, который решает две подзадачи:

Вычисляет срез максимальных дат (MAX(Период)) по уникальным измерениям регистра с группировкой GROUP BY на указанный момент времени.
Соединяет полученную выборку максимальных дат с основной физической таблицей регистра для извлечения ресурсов (в данном случае поля Цена).

Когда мы передаем в параметры виртуальной таблицы условие Номенклатура В (ВЫБРАТЬ ...), платформа 1С вставляет этот подзапрос дважды: сначала во внутренний группировочный подзапрос для расчета максимальной даты, а затем во внешнее соединение для получения ресурсов. В плане выполнения SQL появляется повторное полное сканирование (FULL SCAN (TWICE)) временной таблицы:


AND T4._Fld3402RRef IN (
    SELECT Fields: (T5._Q_001_F_000RRef)
    #Tb4cca3385f8c4e9c9dd93e5118f84150 (T5)(TWICE) FULL SCAN WITHOUT DUPLICATES
)

Посмотрим, к чему это приводит на уровне СУБД:

Отсутствие статистики по временной таблице: Для временных таблиц СУБД часто не имеет актуальной гистограммы распределения данных. При конструкции IN (SELECT ...) оптимизатор нередко ошибается с оценкой кардинальности (ожидает возврата одной строки вместо 6000) и выбирает неэффективные алгоритмы соединения (например, вложенные циклы Nested Loops вместо соединения хэшированием Hash Match).
Множественные чтения и Table Spool: При расчете среза СУБД вынуждена многократно сканировать временную таблицу или помещать ее в буфер, выполняя тысячи лишних логических чтений.
Эффективность скалярного индекса во втором варианте: Во втором варианте в параметрах среза передается только точное скалярное условие ТипЦен = .... СУБД сразу делает быстрый поиск по индексу (Index Seek) по первому измерению регистра, вычисляет срез по розничным ценам, а затем за один проход связывает результат с ВТ_Товары через эффективный Hash Join.

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

Способы решения и оптимизации

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

Решение 1. Исключение подзапроса при селективном основном фильтре

Если в регистре сведений первое измерение (например, ТипЦен) обладает высокой селективностью и отсекает подавляющую часть записей, наиболее простым и быстрым решением будет оставить условие по временной таблице только в секции связи ПО:


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

Этот вариант исключает дублирование подзапросов в сгенерированном SQL-коде, позволяя оптимизатору построить оптимальный плоский план выполнения.

Решение 2. Расчет среза вручную через физическую таблицу регистра

Если в регистре миллионы записей, а список товаров во временной таблице невелик по сравнению со всей базой (например, срез по одному типу цен строит выборку на сотни тысяч строк), расчет всего среза может оказаться накладным. В таких случаях виртуальную таблицу СрезПоследних заменяют ручным расчетом среза через физическую таблицу регистра в два шага.

Разберем по шагам реализацию такого пакетного запроса:

Определяем максимальные даты актуальности цен только для нужного набора товаров из ВТ_Товары и сохраняем их в отдельную промежуточную таблицу с индексацией:


ВЫБРАТЬ
    ВТ_Товары.Номенклатура КАК Номенклатура,
    МАКСИМУМ(Цены.Период) КАК Период
ПОМЕСТИТЬ ВТ_МаксПериоды
ИЗ
    ВТ_Товары КАК ВТ_Товары
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК Цены
        ПО ВТ_Товары.Номенклатура = Цены.Номенклатура
        И Цены.ТипЦен = &ТипЦен
        И Цены.Период <= &Дата
СГРУППИРОВАТЬ ПО
    ВТ_Товары.Номенклатура;

ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    Период;

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


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

Этот подход гарантирует использование физических индексов регистра (поиск по точному совпадению ключевых полей ТипЦен, Номенклатура, Период) и полностью устраняет непредсказуемость трансляции виртуальной таблицы среза.