При написании запросов в системе «1С:Предприятие» разработчики часто сталкиваются с ситуацией, когда использование ЛЕВОЕ СОЕДИНЕНИЕ неожиданно приводит к потере строк в итоговой выборке. Кажется, что запрос написан верно, логика соединения соблюдена, но результат получается таким же, как при использовании ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Сегодня мы подробно разберем, почему возникает эта проблема, рассмотрим классическую ошибку в секции ГДЕ, а также проанализируем целый ряд сопутствующих архитектурных ошибок в запросах и научимся их оптимизировать в соответствии со стандартами разработки фирмы «1С».
Рассмотрим ситуацию, с которой столкнулся автор темы. Ему требовалось вывести всю номенклатуру, по которой был оборот за определенный период (даже если ее сейчас нет на остатке), и левым соединением подтянуть актуальные остатки и цены по конкретному складу. Однако при добавлении условия по складу остатки пропали, и вывелись только те товары, которые физически есть на складе.
Давайте проанализируем фрагмент исходного запроса в секции связи и фильтрации:
ИЗ
ТОВАРЫ КАК ТОВАРЫ
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиНоменклатуры.Остатки КАК ОстаткиНоменклатурыОстатки
ПО ТОВАРЫ.Товар.Ссылка = ОстаткиНоменклатурыОстатки.Номенклатура
ГДЕ
ОстаткиНоменклатурыОстатки.Склад = &Склад
В чем же здесь кроется критическая ошибка? СУБД обрабатывает этот запрос следующим образом:
ТОВАРЫ с виртуальной таблицей остатков. Для тех товаров, которых нет на складе, поля таблицы остатков (включая поле Склад и КоличествоОстаток) заполняются значением NULL.ГДЕ. Она проверяет условие: ОстаткиНоменклатурыОстатки.Склад = &Склад.Склад равно NULL. Условие NULL = &Склад возвращает значение Ложь. В результате все строки, где остатков не было, безжалостно отбрасываются из итогового результата.Таким образом, наложение любого жесткого условия на поля правой (присоединяемой) таблицы в секции ГДЕ автоматически превращает ЛЕВОЕ СОЕДИНЕНИЕ во ВНУТРЕННЕЕ. Нам необходимо запомнить это правило как одно из фундаментальных правил работы с реляционными базами данных.
Самым правильным и наиболее производительным решением в среде «1С» является наложение отборов непосредственно в параметрах виртуальной таблицы. Вместо того чтобы выбирать остатки по всей базе данных, а затем соединять их и фильтровать в секции ГДЕ, мы должны сразу сказать системе выбирать остатки только по нужному складу.
Давайте перепишем это соединение правильно. Мы убираем условие из секции ГДЕ и передаем параметр Склад в круглые скобки виртуальной таблицы остатков:
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиНоменклатуры.Остатки(, Склад = &Склад) КАК ОстаткиНоменклатурыОстатки
ПО ТОВАРЫ.Товар = ОстаткиНоменклатурыОстатки.Номенклатура
При таком подходе СУБД выберет из базы данных остатки только по целевому складу. Для товаров, у которых остатков на этом складе нет, соединение вернет пустые значения (NULL), но эти строки больше не будут отброшены, так как в секции ГДЕ отсутствует фильтр по правой таблице.
Если внимательно посмотреть на исходный запрос автора, то помимо проблемы с левым соединением, в нем можно обнаружить еще как минимум пять грубых нарушений стандартов разработки «1С», которые могут привести к критическому падению производительности и ошибкам в бизнес-логике. Давайте разберем их по шагам и исправим.
В тексте запроса автора постоянно встречаются конструкции вида Товар.Ссылка, Номенклатура.Ссылка и Контрагент.Ссылка. Нам важно понимать, что реквизиты Товар и Номенклатура в регистрах уже сами по себе являются ссылками. Обращение к свойству .Ссылка заставляет платформу выполнять лишнее неявное соединение (Implicit JOIN) с таблицей соответствующего справочника СУБД только ради того, чтобы получить тот же самый идентификатор ссылки. Это создает абсолютно ненужную нагрузку на базу данных.
Правило: Всегда обращайтесь к ссылочным полям напрямую, без использования .Ссылка на конце (например, просто ОстаткиНоменклатуры.Номенклатура).
В первом запросе пакета автор получает список товаров следующим образом:
ВЫБРАТЬ
ОстаткиНоменклатуры.Номенклатура КАК Товар
ПОМЕСТИТЬ ТОВАРЫ
ИЗ
РегистрНакопления.ОстаткиНоменклатуры КАК ОстаткиНоменклатуры
ГДЕ
ОстаткиНоменклатуры.Период > &Период
И ОстаткиНоменклатуры.Склад = &Склад
Обращение идет к реальной (физической) таблице движений регистра накопления. Если по какому-то товару за указанный период было совершено 50 продаж или поступлений, то этот товар запишется во временную таблицу ТОВАРЫ ровно 50 раз! В дальнейшем при соединении с остатками и ценами мы получим колоссальное дублирование строк и неверные результаты.
Для решения этой проблемы можно использовать ключевое слово РАЗЛИЧНЫЕ, но гораздо эффективнее и быстрее использовать виртуальную таблицу оборотов, которая уже сгруппирована СУБД на уровне итогов:
ВЫБРАТЬ
ОстаткиНоменклатурыОбороты.Номенклатура КАК Товар
ПОМЕСТИТЬ ТОВАРЫ
ИЗ
РегистрНакопления.ОстаткиНоменклатуры.Обороты(&Период, , , Склад = &Склад) КАК ОстаткиНоменклатурыОбороты
Наша временная таблица ТОВАРЫ далее используется в качестве ведущей (левой) таблицы сразу для трех соединений. Если в ней будет содержаться значительное количество записей (более нескольких десятков), отсутствие индекса приведет к тому, что СУБД будет вынуждена производить полное сканирование таблиц при поиске соответствий. Чтобы оптимизатор СУБД мог использовать быстрые алгоритмы поиска (например, Merge Join), мы обязательно должны проиндексировать временную таблицу по полю связи.
Сделать это очень просто, достаточно добавить инструкцию ИНДЕКСИРОВАТЬ при создании временной таблицы:
ПОМЕСТИТЬ ТОВАРЫ
ИНДЕКСИРОВАТЬ
Товар
Автор использует следующую конструкцию:
ЕСТЬNULL(ОстаткиНоменклатурыОстатки.Партия.Контрагент.Ссылка, "") КАК Контрагент
Это классический пример крайне опасного кода. Измерение Партия в регистрах накопления, как правило, имеет составной тип данных (в него могут входить документы «Поступление товаров услуг», «Авансовый отчет», «Возврат от покупателя» и т.д.). Когда мы пишем конструкцию с точкой от поля составного типа (Партия.Контрагент), платформа 1С на уровне СУБД генерирует левые соединения со ВСЕМИ физическими таблицами документов, входящими в этот тип. Запрос превращается в тяжелого монстра со множеством неявных соединений, что приводит к критическому падению производительности.
Как сделать правильно: Мы должны явно ограничить тип поля с помощью функции ВЫРАЗИТЬ, указав конкретный документ, из которого нам нужно получить контрагента:
ЕСТЬNULL(ВЫРАЗИТЬ(ОстаткиНоменклатурыОстатки.Партия КАК Документ.ПоступлениеТоваровУслуг).Контрагент, ЗНАЧЕНИЕ(Справочник.Контрагенты.ПустаяСсылка)) КАК Контрагент
Обратите внимание на значение по умолчанию в функции ЕСТЬNULL(..., ""). В качестве альтернативы для отсутствующей ссылки контрагента автор подставляет пустую строку "". Это приводит к тому, что колонка итоговой выборки приобретает составной тип данных (Ссылка + Строка). Наличие полей составного типа в результатах запроса усложняет дальнейшую работу с выборкой и может негативно сказываться на производительности СУБД при построении планов запросов. Вместо пустой строки всегда следует возвращать типизированную пустую ссылку с помощью конструкции ЗНАЧЕНИЕ(Справочник.ИмяСправочника.ПустаяСсылка), как показано в примере выше.
Теперь, когда мы детально разобрали все ошибки и узкие места, давайте объединим наши знания и построим красивый, быстрый и полностью соответствующий стандартам «1С» пакетный запрос.
ВЫБРАТЬ РАЗЛИЧНЫЕ
ОстаткиНоменклатурыОбороты.Номенклатура КАК Товар
ПОМЕСТИТЬ ТОВАРЫ
ИЗ
РегистрНакопления.ОстаткиНоменклатуры.Обороты(&Период, , , Склад = &Склад) КАК ОстаткиНоменклатурыОбороты
ИНДЕКСИРОВАТЬ
Товар
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ТОВАРЫ.Товар КАК Товар,
ЕСТЬNULL(ЦеныКомпанииСрезПоследних_Розн.Цена, 0) КАК ЦенаРозн,
ЕСТЬNULL(ЦеныКомпанииСрезПоследних_Закуп.Цена, 0) КАК ЦенаЗакуп,
ЕСТЬNULL(ОстаткиНоменклатурыОстатки.КоличествоОстаток, 0) КАК Остаток,
ЕСТЬNULL(ВЫРАЗИТЬ(ОстаткиНоменклатурыОстатки.Партия КАК Документ.ПоступлениеТоваровУслуг).Контрагент, ЗНАЧЕНИЕ(Справочник.Контрагенты.ПустаяСсылка)) КАК Контрагент
ИЗ
ТОВАРЫ КАК ТОВАРЫ
// Левое соединение для получения розничных цен с отбором внутри виртуальной таблицы
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныКомпании.СрезПоследних(&Период, ТипЦен = &ТипРозн И Склад = &Склад) КАК ЦеныКомпанииСрезПоследних_Розн
ПО ТОВАРЫ.Товар = ЦеныКомпанииСрезПоследних_Розн.Номенклатура
// Левое соединение для получения закупочных цен с отбором внутри виртуальной таблицы
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныКомпании.СрезПоследних(&Период, ТипЦен = &ТипЗакуп И Склад = &Склад) КАК ЦеныКомпанииСрезПоследних_Закуп
ПО ТОВАРЫ.Товар = ЦеныКомпанииСрезПоследних_Закуп.Номенклатура
// Левое соединение для получения остатков с фильтрацией по складу в параметрах таблицы
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиНоменклатуры.Остатки(&Период, Склад = &Склад) КАК ОстаткиНоменклатурыОстатки
ПО ТОВАРЫ.Товар = ОстаткиНоменклатурыОстатки.Номенклатура
Мы успешно решили проблему исчезновения строк при левом соединении. Давайте кратко зафиксируем основные правила, которые уберегут вас от подобных ошибок в будущем:
ГДЕ при левом соединении. Это превращает соединение во внутреннее. Все условия должны быть перенесены в параметры виртуальных таблиц или, в крайнем случае, в секцию связи ПО..Ссылка для полей, которые уже являются ссылочными. Это избавляет систему от генерации лишних JOIN на уровне СУБД.ВЫРАЗИТЬ.ЕСТЬNULL в качестве значения по умолчанию для пустых ссылок возвращайте типизированную пустую ссылку через ЗНАЧЕНИЕ(...), а не пустую строку.Соблюдение этих простых стандартов гарантирует стабильную, быструю и предсказуемую работу ваших отчетов и обработок даже на базах данных объемом в терабайты!