При работе с запросами в платформе «1С:Предприятие» разработчики часто сталкиваются с ситуацией, когда классическое ЛЕВОЕ СОЕДИНЕНИЕ внезапно начинает вести себя как ВНУТРЕННЕЕ СОЕДИНЕНИЕ. То есть из результирующей выборки пропадают те строки левой (основной) таблицы, для которых не нашлось соответствия в правой (присоединяемой) таблице. Давайте подробно разберем, почему возникает это странное поведение, как работает СУБД на логическом уровне, и как написать эффективный и быстродействующий запрос в соответствии со стандартами разработки фирмы «1С».
Рассмотрим ситуацию на примере запроса из сообщения автора. Ему потребовалось соединить данные по продукции из документов ЗаказНаПроизводство с данными по весу из документов ЦиклПроизводства за определенный период:
ВЫБРАТЬ
ЗаказНаПроизводствоПродукция.Номенклатура,
СУММА(ЗаказНаПроизводствоПродукция.Количество) КАК Количество,
СУММА(ЦиклПроизводстваГотоваяПродукция.ВесНоменклатуры) КАК ВесНоменклатуры,
СУММА(ЦиклПроизводстваГотоваяПродукция.ВесБрутто) КАК ВесБрутто
ИЗ
Документ.ЗаказНаПроизводство.Продукция КАК ЗаказНаПроизводствоПродукция
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЦиклПроизводства.ГотоваяПродукция КАК ЦиклПроизводстваГотоваяПродукция
ПО ЗаказНаПроизводствоПродукция.Номенклатура = ЦиклПроизводстваГотоваяПродукция.Номенклатура
ГДЕ
ЗаказНаПроизводствоПродукция.Ссылка.Дата МЕЖДУ &ДатаН И &ДатаК
И ЗаказНаПроизводствоПродукция.Ссылка.Проведен
И ЦиклПроизводстваГотоваяПродукция.Ссылка.Дата МЕЖДУ &ДатаН И &ДатаК
И ЦиклПроизводстваГотоваяПродукция.Ссылка.Проведен
СГРУППИРОВАТЬ ПО
ЗаказНаПроизводствоПродукция.Номенклатура
По задумке автора, этот запрос должен вывести всю номенклатуру из заказов на производство за период, а если по этой номенклатуре были документы цикла производства — заполнить вес. Если циклов производства не было, поля веса должны остаться пустыми. Однако на практике запрос возвращает только те строки заказов, для которых обязательно нашлись соответствующие документы цикла производства. Левое соединение превратилось во внутреннее.
Чтобы понять, почему так происходит, нам необходимо рассмотреть, в каком порядке СУБД (MS SQL Server, PostgreSQL или др.) обрабатывает секции нашего запроса. Логический порядок выполнения выглядит следующим образом:
ЗаказНаПроизводствоПродукция, у которых нет пары в правой таблице ЦиклПроизводстваГотоваяПродукция, во всех полях правой таблицы генерируется виртуальное значение NULL.СУММА).Теперь посмотрим на наши условия в секции ГДЕ:
И ЦиклПроизводстваГотоваяПродукция.Ссылка.Дата МЕЖДУ &ДатаН И &ДатаК
И ЦиклПроизводстваГотоваяПродукция.Ссылка.Проведен
Для тех строк, по которым не нашлось производства, поле Ссылка (а значит, и его реквизиты Дата и Проведен) равно NULL. Согласно правилам трехзначной логики SQL (3VL), любое сравнение значения NULL с конкретным значением (датой, булевым флагом и т.д.) возвращает результат UNKNOWN (неизвестно), который СУБД трактует как ЛОЖЬ.
Поскольку условие в секции ГДЕ для пустых строк правой таблицы возвращает ложь, СУБД безжалостно отфильтровывает эти строки из итогового результата. В итоге мы получаем классическое внутреннее соединение.
Самый простой и быстрый способ исправить эту ошибку — перенести условия фильтрации правой таблицы непосредственно в условие связи ПО. В этом случае СУБД сначала отфильтрует строки правой таблицы по дате и проведению, и только затем выполнит левое соединение. Те строки левой таблицы, для которых не нашлось подходящих записей справа, сохранятся в результирующей таблице.
Посмотрим на исправленный вариант запроса:
ВЫБРАТЬ
ЗаказНаПроизводствоПродукция.Номенклатура,
СУММА(ЗаказНаПроизводствоПродукция.Количество) КАК Количество,
СУММА(ЦиклПроизводстваГотоваяПродукция.ВесНоменклатуры) КАК ВесНоменклатуры,
СУММА(ЦиклПроизводстваГотоваяПродукция.ВесБрутто) КАК ВесБрутто
ИЗ
Документ.ЗаказНаПроизводство.Продукция КАК ЗаказНаПроизводствоПродукция
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЦиклПроизводства.ГотоваяПродукция КАК ЦиклПроизводстваГотоваяПродукция
ПО ЗаказНаПроизводствоПродукция.Номенклатура = ЦиклПроизводстваГотоваяПродукция.Номенклатура
И ЦиклПроизводстваГотоваяПродукция.Ссылка.Дата МЕЖДУ &ДатаН И &ДатаК
И ЦиклПроизводстваГотоваяПродукция.Ссылка.Проведен = ИСТИНА
ГДЕ
ЗаказНаПроизводствоПродукция.Ссылка.Дата МЕЖДУ &ДатаН И &ДатаК
И ЗаказНаПроизводствоПродукция.Ссылка.Проведен = ИСТИНА
СГРУППИРОВАТЬ ПО
ЗаказНаПроизводствоПродукция.Номенклатура
Важный момент: Обратите внимание, что условия на левую таблицу (ЗаказНаПроизводствоПродукция.Ссылка.Дата и Проведен) мы оставляем в секции ГДЕ, так как нам нужно жестко ограничить выборку именно по левой таблице.
В Сообщении 6 было предложено решить проблему через вложенный запрос в правой части левого соединения. В этом случае мы сначала выбираем отфильтрованные данные по циклам производства во вложенном запросе, а затем соединяем их с заказами:
ВЫБРАТЬ
ЗаказНаПроизводствоПродукция.Номенклатура,
СУММА(ЗаказНаПроизводствоПродукция.Количество) КАК Количество,
СУММА(Подзапрос.ВесНоменклатуры) КАК ВесНоменклатуры,
СУММА(Подзапрос.ВесБрутто) КАК ВесБрутто
ИЗ
Документ.ЗаказНаПроизводство.Продукция КАК ЗаказНаПроизводствоПродукция
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ
ЦиклПроизводстваГотоваяПродукция.Номенклатура КАК Номенклатура,
ЦиклПроизводстваГотоваяПродукция.ВесНоменклатуры КАК ВесНоменклатуры,
ЦиклПроизводстваГотоваяПродукция.ВесБрутто КАК ВесБрутто
ИЗ
Документ.ЦиклПроизводства.ГотоваяПродукция КАК ЦиклПроизводстваГотоваяПродукция
ГДЕ
ЦиклПроизводстваГотоваяПродукция.Ссылка.Дата МЕЖДУ &ДатаН И &ДатаК
И ЦиклПроизводстваГотоваяПродукция.Ссылка.Проведен = ИСТИНА
) КАК Подзапрос
ПО ЗаказНаПроизводствоПродукция.Номенклатура = Подзапрос.Номенклатура
ГДЕ
ЗаказНаПроизводствоПродукция.Ссылка.Дата МЕЖДУ &ДатаН И &ДатаК
И ЗаказНаПроизводствоПродукция.Ссылка.Проведен = ИСТИНА
СГРУППИРОВАТЬ ПО
ЗаказНаПроизводствоПродукция.Номенклатура
Хотя этот запрос вернет абсолютно корректные данные, с точки зрения стандартов разработки фирмы «1С» использовать вложенные запросы в соединениях крайне не рекомендуется. Причина кроется в производительности: оптимизаторы СУБД часто не могут построить эффективный план выполнения для динамических подзапросов в левых соединениях, что на больших базах данных приводит к полному сканированию таблиц (Table Scan) и колоссальным задержкам.
Чтобы написать максимально быстрый и профессиональный запрос, мы должны использовать временные таблицы. Этот подход позволяет оптимизатору СУБД точно рассчитать статистику и построить оптимальный план выполнения.
Разберем создание правильного пакетного запроса по шагам:
Шаг 1. Сначала мы выбираем данные по циклам производства, группируем их, фильтруем по дате и проведению и помещаем во временную таблицу ВТ_ДанныеПроизводства. Также крайне важно проиндексировать временную таблицу по полю связи.
Шаг 2. Во втором запросе пакета мы выбираем данные из заказов и левым соединением подключаем нашу временную таблицу по проиндексированному полю.
Посмотрим, как выглядит этот идеальный вариант запроса:
// Шаг 1: Подготовка данных во временной таблице
ВЫБРАТЬ
ЦиклПроизводстваГотоваяПродукция.Номенклатура КАК Номенклатура,
СУММА(ЦиклПроизводстваГотоваяПродукция.ВесНоменклатуры) КАК ВесНоменклатуры,
СУММА(ЦиклПроизводстваГотоваяПродукция.ВесБрутто) КАК ВесБрутто
ПОМЕСТИТЬ ВТ_ДанныеПроизводства
ИЗ
Документ.ЦиклПроизводства.ГотоваяПродукция КАК ЦиклПроизводстваГотоваяПродукция
ГДЕ
ЦиклПроизводстваГотоваяПродукция.Ссылка.Дата МЕЖДУ &ДатаН И &ДатаК
И ЦиклПроизводстваГотоваяПродукция.Ссылка.Проведен = ИСТИНА
СГРУППИРОВАТЬ ПО
ЦиклПроизводстваГотоваяПродукция.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Основная выборка с использованием левого соединения
ВЫБРАТЬ
ЗаказНаПроизводствоПродукция.Номенклатура КАК Номенклатура,
СУММА(ЗаказНаПроизводствоПродукция.Количество) КАК Количество,
ЕСТЬNULL(СУММА(ВТ_ДанныеПроизводства.ВесНоменклатуры), 0) КАК ВесНоменклатуры,
ЕСТЬNULL(СУММА(ВТ_ДанныеПроизводства.ВесБрутто), 0) КАК ВесБрутто
ИЗ
Документ.ЗаказНаПроизводство.Продукция КАК ЗаказНаПроизводствоПродукция
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ДанныеПроизводства КАК ВТ_ДанныеПроизводства
ПО ЗаказНаПроизводствоПродукция.Номенклатура = ВТ_ДанныеПроизводства.Номенклатура
ГДЕ
ЗаказНаПроизводствоПродукция.Ссылка.Дата МЕЖДУ &ДатаН И &ДатаК
И ЗаказНаПроизводствоПродукция.Ссылка.Проведен = ИСТИНА
СГРУППИРОВАТЬ ПО
ЗаказНаПроизводствоПродукция.Номенклатура
В Сообщении 10 справедливо затронут вопрос: что произойдет с полями веса, если по номенклатуре из заказов не было производства? Как мы знаем, СУБД вернет значение NULL. В отчете это приведет к пустым ячейкам, а попытка использовать эти значения в математических расчетах в коде 1С вызовет ошибку.
Для замены значений NULL на 0 мы используем встроенную функцию ЕСТЬNULL. Но здесь кроется еще одна ловушка производительности. Существует два способа написания этой функции в сочетании с агрегацией:
СУММА(ЕСТЬNULL(Поле, 0)) — НЕОПТИМАЛЬНО. В этом случае СУБД будет вызывать функцию ЕСТЬNULL для каждой строки детальных записей еще до того, как сгруппирует их. Если у вас в табличной части миллионы строк, это приведет к сильному замедлению работы.ЕСТЬNULL(СУММА(Поле), 0) — ОПТИМАЛЬНО. Функция СУММА по умолчанию игнорирует значения NULL при сложении. Намного эффективнее сначала выполнить группировку и суммирование средствами СУБД, а затем применить функцию ЕСТЬNULL всего один раз к итоговому сгруппированному результату.Подведем итоги нашего разбора и составим краткий чек-лист, который поможет вам избежать подобных ошибок в будущем:
ГДЕ, если используете левое соединение. Все условия фильтрации правой таблицы должны быть в секции ПО.NULL на числовые значения всегда используйте конструкцию ЕСТЬNULL(СУММА(Поле), 0) вместо обратной, чтобы поберечь ресурсы сервера.