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