При разработке в среде 1С:Предприятие программисты регулярно сталкиваются с необходимостью сопоставить и вывести данные из двух разных табличных частей (ТЧ) одного и того же документа. На первый взгляд задача кажется простой — достаточно написать запрос с левым или полным соединением. Однако за этой простотой скрываются серьезные проблемы с производительностью, декартовым произведением строк и стандартами разработки 1С.
В этой статье мы подробно разберем, какие опасности таит в себе прямое соединение табличных частей, как ведет себя оптимизатор СУБД при наложении условий, и рассмотрим два эталонных решения этой задачи, которые одобряет сама фирма 1С.
Давайте проанализируем ситуацию, когда разработчик пытается соединить две табличные части напрямую по ссылке на документ:
ВЫБРАТЬ
А.Номенклатура,
А.Количество КАК Кол1,
Б.Количество КАК Кол2
ИЗ
Документ.РеализацияТоваровУслуг.Товары КАК А
ЛЕВОЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг.Услуги КАК Б
ПО А.Ссылка = Б.Ссылка
ГДЕ
А.Ссылка = &Ссылка
Что произойдет в СУБД при выполнении этого запроса? Если в табличной части Товары содержится 5 строк, а в табличной части Услуги — 10 строк, то результатом левого соединения по ссылке станет таблица из 50 строк (декартово произведение, или Cartesian Product). СУБД сопоставит каждую строку первой таблицы с каждой строкой второй таблицы.
Чтобы соединение было корректным, у строк должен быть уникальный ключ сопоставления. В типовых конфигурациях 1С (ERP, КА, УТ) для этого часто применяется специальный реквизит КлючСвязи. Если такого ключа нет (или таблицы не связаны логически строка-в-строку), прямое соединение по ссылке теряет всякий практический смысл и приводит к дублированию данных и некорректным расчетам.
Рассмотрим подробнее три основных способа решения этой задачи.
Если вам необходимо соединить ТЧ и вы хотите, чтобы ваш код без проблем прошел аудит или проверку на экзамене «1С:Специалист по платформе», соединять физические таблицы напрямую нельзя. Любая выборка из табличных частей большого объема должна производиться через предварительно подготовленные временные таблицы.
Разберем этот алгоритм по шагам:
Посмотрим на пример реализации этого подхода в коде:
// Шаг 1: Выбираем и индексируем данные первой ТЧ
ВЫБРАТЬ
Товары.Номенклатура КАК Номенклатура,
Товары.Количество КАК КоличествоТоваров
ПОМЕСТИТЬ ВТ_Товары
ИЗ
Документ.РеализацияТоваровУслуг.Товары КАК Товары
ГДЕ
Товары.Ссылка = &Ссылка
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
// Шаг 2: Выбираем и индексируем данные второй ТЧ
ВЫБРАТЬ
Скидки.Номенклатура КАК Номенклатура,
Скидки.СуммаСкидки КАК СуммаСкидки
ПОМЕСТИТЬ ВТ_Скидки
ИЗ
Документ.РеализацияТоваровУслуг.Скидки КАК Скидки
ГДЕ
Скидки.Ссылка = &Ссылка
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
// Шаг 3: Соединяем подготовленные временные таблицы
ВЫБРАТЬ
ЕСТЬNULL(ВТ_Товары.Номенклатура, ВТ_Скидки.Номенклатура) КАК Номенклатура,
ЕСТЬNULL(ВТ_Товары.КоличествоТоваров, 0) КАК КоличествоТоваров,
ЕСТЬNULL(ВТ_Скидки.СуммаСкидки, 0) КАК СуммаСкидки
ИЗ
ВТ_Товары КАК ВТ_Товары
ПОЛНОЕ СОЕДИНЕНИЕ ВТ_Скидки КАК ВТ_Скидки
ПО ВТ_Товары.Номенклатура = ВТ_Скидки.Номенклатура
Этот подход гарантирует моментальное выполнение запроса на СУБД любого объема, так как соединяться будут единичные строки конкретного документа, а поиск по индексам во временных таблицах произойдет за сотые доли секунды.
В стандартах разработки 1С (публикациях ИТС) содержится жесткое ограничение на использование конструкции ПОЛНОЕ СОЕДИНЕНИЕ, особенно при работе под управлением СУБД PostgreSQL. Данная СУБД транслирует полное соединение в тяжелую конструкцию с подзапросами, что может критически снизить производительность всей системы. Кроме того, стандарты запрещают одновременно использовать ПОЛНОЕ СОЕДИНЕНИЕ и обращаться к табличным частям в секции ВЫБРАТЬ.
Вместо полного соединения двух ТЧ золотым стандартом является использование конструкции ОБЪЕДИНИТЬ ВСЕ с последующей группировкой результатов. Выясним причину высокой скорости этого метода: СУБД выполняет линейное чтение таблиц по очереди, избегая ресурсоемкой операции сопоставления строк (Join).
Проанализируем пример такого запроса:
ВЫБРАТЬ
ОбъединенныеДанные.Номенклатура КАК Номенклатура,
СУММА(ОбъединенныеДанные.Количество) КАК Количество,
СУММА(ОбъединенныеДанные.СуммаСкидки) КАК СуммаСкидки
ИЗ
(
// Выбираем данные из первой ТЧ, поле второй ТЧ заполняем нулем
ВЫБРАТЬ
Товары.Номенклатура КАК Номенклатура,
Товары.Количество КАК Количество,
0 КАК СуммаСкидки
ИЗ
Документ.РеализацияТоваровУслуг.Товары КАК Товары
ГДЕ
Товары.Ссылка = &Ссылка
ОБЪЕДИНИТЬ ВСЕ
// Выбираем данные из второй ТЧ, поле первой ТЧ заполняем нулем
ВЫБРАТЬ
Скидки.Номенклатура КАК Номенклатура,
0 КАК Количество,
Скидки.СуммаСкидки КАК СуммаСкидки
ИЗ
Документ.РеализацияТоваровУслуг.Скидки КАК Скидки
ГДЕ
Скидки.Ссылка = &Ссылка
) КАК ОбъединенныеДанные
СГРУППИРОВАТЬ ПО
ОбъединенныеДанные.Номенклатура
Этот метод работает невероятно быстро, полностью исключает дублирование строк (декартово произведение) и является максимально безопасным для высоконагруженных систем.
В обсуждениях часто возникает вопрос: «Где правильнее накладывать условие отбора по Ссылке — в секции связи ПО или в секции фильтрации ГДЕ? Починит ли оптимизатор СУБД неоптимальный запрос?»
Давайте разберем поведение современных СУБД (MS SQL Server, PostgreSQL):
ГДЕ А.Ссылка = &Ссылка (для левой, основной таблицы), оптимизатор СУБД выполнит так называемый predicate pushdown (проталкивание условий). Это означает, что СУБД сначала применит фильтр по ссылке к левой таблице, а уже затем начнет соединять отфильтрованный результат с правой.ГДЕ (например, ГДЕ Б.Ссылка = &Ссылка), оптимизатор превратит ваше левое соединение во внутреннее (INNER JOIN). Это произойдет потому, что строки, для которых в правой таблице не нашлось соответствий (где значения полей равны NULL), будут отсеяны условием фильтрации.Вывод: Не стоит полагаться исключительно на «умный» оптимизатор СУБД. Написание явных, простых и предсказуемых запросов с использованием временных таблиц или объединений — залог стабильной работы вашей системы 1С.