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

При проектировании сложных запросов в системе «1С:Предприятие» разработчики часто сталкиваются с дилеммой: достаточно ли наложить фильтр в параметрах виртуальной таблицы, или же его необходимо продублировать в условии связи (секции ПО)? Этот вопрос напрямую затрагивает как логическую корректность получаемых данных, так и производительность всей системы на уровне СУБД (MS SQL Server, PostgreSQL и др.).

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

Постановка проблемы на практическом примере

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

Рассмотрим «наивный» вариант запроса, в котором условие по виду цены наложено только в параметрах виртуальной таблицы:

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

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

И ВременнаяТаблица.ВидЦены = ЦеныНоменклатурыСрезПоследних.ВидЦены

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

1. Логический аспект: предотвращение дублирования строк

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

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

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

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

2. Физический аспект: транзитивное замыкание и оптимизатор СУБД

Вторая причина кроется в особенностях работы планировщиков запросов современных СУБД (MS SQL Server, PostgreSQL, Oracle). Чтобы понять, как дублирование условий влияет на скорость выполнения запроса, разберем механизм транзитивного замыкания предикатов (Transitive Predicate Generation).

Математическое правило транзитивности гласит: если A = B и B = C, то A = C.

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

В параметрах виртуальной таблицы мы жестко ограничили правую таблицу фильтром: ЦеныНоменклатурыСрезПоследних.ВидЦены = &ВидЦены. Это наше условие B = C.

Если в секции ПО мы явно пишем условие связи: ВременнаяТаблица.ВидЦены = ЦеныНоменклатурыСрезПоследних.ВидЦены. Это наше условие A = B.

Получив эти два уравнения, оптимизатор СУБД автоматически делает вывод: ВременнаяТаблица.ВидЦены = &ВидЦены (условие A = C). Благодаря этому СУБД получает возможность:

• Применить индекс к левой таблице (ВременнаяТаблица) по полю ВидЦены еще до момента выполнения соединения.
• Отсечь лишние данные на самом раннем этапе, уменьшив количество строк, участвующих в соединении.
• Выбрать более эффективный алгоритм соединения таблиц (например, заменить тяжелый Hash Match на легкий и быстрый Nested Loops).

Если же мы уберем дублирующее условие из секции ПО, оптимизатор СУБД не сможет гарантированно связать параметр правой таблицы с полями левой. В сложных запросах с множеством соединений это часто приводит к тому, что оптимизатор ошибается в оценке количества строк (Cardinality Estimation), строит неоптимальный план выполнения и начинает сканировать таблицы целиком (Index Scan или Table Scan) вместо точечного поиска по индексу (Index Seek).

3. Стандарты разработки «1С»: почему прямое соединение с виртуальными таблицами — это ошибка?

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

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

Рекомендуемый канонический подход (рефакторинг запроса)

Чтобы гарантировать максимальную скорость работы и стабильность запроса при любых объемах данных, его необходимо разбить на несколько простых и понятных для СУБД шагов с использованием проиндексированных временных таблиц.

Выполним пошаговый рефакторинг нашего исходного запроса по стандартам 1С:

Шаг 1. Подготовка и индексация исходных данных

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

Шаг 2. Выборка среза последних в промежуточную временную таблицу

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

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

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

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

Шаг 3. Соединение двух физических временных таблиц

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

ВЫБРАТЬ
    ВременнаяТаблица.Номенклатура КАК Номенклатура,
    ВременнаяТаблица.Характеристика КАК Характеристика,
    ВременнаяТаблица.Витрина КАК Витрина,
    ЕСТЬNULL(ВТ_ЦеныНоменклатуры.Цена, 0) КАК Цена
ИЗ
    ВременнаяТаблица КАК ВременнаяТаблица
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ЦеныНоменклатуры КАК ВТ_ЦеныНоменклатуры
        ПО ВременнаяТаблица.Номенклатура = ВТ_ЦеныНоменклатуры.Номенклатура
            И ВременнаяТаблица.Характеристика = ВТ_ЦеныНоменклатуры.Характеристика

Обратите внимание: в финальном соединении нам больше не требуется накладывать условие по полю ВидЦены. Почему? Потому что на предыдущем шаге мы жестко ограничили выборку цен в таблицу ВТ_ЦеныНоменклатуры только одним конкретным видом цены. Возможность дублирования строк полностью исключена, а план запроса гарантированно будет оптимальным.

Резюме и правила хорошего тона для разработчика

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

1. Всегда связывайте таблицы по полному ключу уникальности. Если измерение (например, ВидЦены) участвует в формировании уникальности записи регистра, оно должно участвовать в связи, чтобы защитить систему от логических ошибок и дублирования строк при изменении бизнес-логики.
2. Избегайте прямых соединений с виртуальными таблицами. Прямые соединения заставляют СУБД строить сложные планы выполнения с непредсказуемой производительностью.
3. Используйте пакетные запросы и индексацию временных таблиц. Выгрузка среза последних во временную таблицу с предварительной фильтрацией в параметрах и последующее соединение проиндексированных таблиц — это стандарт разработки «1С», гарантирующий стабильно высокую скорость работы системы даже на базах данных объемом в терабайты.