При разработке сложных отчетов в конфигурации «1С:Управление производственным предприятием» (УПП) или «1С:ERP» разработчики часто сталкиваются с проблемой резкого падения производительности запросов. В худших случаях сервер СУБД или рабочий процесс 1С аварийно завершают работу с ошибкой нехватки памяти (Out of Memory). В этой статье мы подробно разберем, почему простой с виду запрос по анализу незакрытых заказов на производство и остатков комплектующих на складах может «вешать» систему, и пошагово оптимизируем его.
Рассмотрим исходную ситуацию. Перед разработчиком стоит задача получить список незакрытых заказов на производство, определить по спецификациям состав необходимых комплектующих и вывести их текущие остатки по нескольким складам. Однако после добавления связи с регистром причин закрытия заказов запрос начинает выполняться бесконечно долго и вылетает по превышению лимита оперативной памяти.
---
Проанализируем первый временной пакет исходного запроса:
ВЫБРАТЬ РАЗРЕШЕННЫЕ
РегЗаказы.ЗаказНаПроизводство КАК ЗаказНаПроизводство,
...
ПОМЕСТИТЬ ЗаказыНаПроизводство
ИЗ
РегистрСведений.ПричиныЗакрытияЗаказов КАК ПричиныЗакрытияЗаказов
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ЗаказыНаПроизводство.Остатки(&ДатаКон, ) КАК РегЗаказы
ПО (ПричиныЗакрытияЗаказов.Заказ <> РегЗаказы.ЗаказНаПроизводство.Ссылка)
Выясним причину, почему эта конструкция буквально «убивает» производительность СУБД. При использовании оператора равенства (=) планировщик СУБД строит эффективный план выполнения на основе алгоритмов Hash Match или Merge Join, используя индексы таблиц. Но когда мы указываем оператор неравенства (<>), СУБД лишается возможности использовать эти механизмы и переходит к наиболее ресурсоемкому алгоритму — Nested Loops (вложенные циклы) с полным сканированием таблиц.
В результате происходит декартово умножение (Cartesian product): каждая строка из таблицы причин закрытия заказов сопоставляется со всеми строками остатков заказов, за исключением одной совпадающей. Если в таблице причин закрытия содержится 5 000 записей, а в таблице остатков — 10 000, то СУБД попытается построить во временной памяти виртуальную таблицу объемом около 50 миллионов строк! Это мгновенно переполняет выделенный рабочий лимит RAM.
Как сделать правильно: Для исключения закрытых заказов применим классический паттерн Anti-Join. Свяжем таблицы левым соединением по строгому равенству ключевых полей, а в секции ГДЕ отсечем те записи, для которых правая таблица вернула пустое значение (ЕСТЬ NULL).
Рассмотрим оптимизированный вариант первого пакета:
ВЫБРАТЬ РАЗРЕШЕННЫЕ
РегЗаказы.ЗаказНаПроизводство КАК ЗаказНаПроизводство,
РегЗаказы.Номенклатура КАК Продукция,
РегЗаказы.Спецификация КАК Спецификация,
РегЗаказы.ЕдиницаИзмерения КАК ЕдиницаИзмерения,
РегЗаказы.КоличествоОстаток КАК КоличествоОстаток
ПОМЕСТИТЬ ЗаказыНаПроизводство
ИЗ
РегистрНакопления.ЗаказыНаПроизводство.Остатки(&ДатаКон, ) КАК РегЗаказы
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ПричиныЗакрытияЗаказов КАК ПричиныЗакрытияЗаказов
ПО РегЗаказы.ЗаказНаПроизводство = ПричиныЗакрытияЗаказов.Заказ
ГДЕ
ПричиныЗакрытияЗаказов.Заказ ЕСТЬ NULL
ИНДЕКСИРОВАТЬ ПО
ЗаказНаПроизводство
Такой запрос отработает за сотые доли секунды, так как СУБД выполнит эффективное соединение по индексу и мгновенно отбросит закрытые заказы.
---
Обратим внимание на условие связи в исходном запросе: ПричиныЗакрытияЗаказов.Заказ <> РегЗаказы.ЗаказНаПроизводство.Ссылка. Использование конструкции .Ссылка для поля, которое и так является ссылочным типом данных, избыточно.
В платформе «1С:Предприятие 8» обращение через точку к реквизитам ссылочных полей в запросе приводит к автоматическому генерации неявных левых соединений (LEFT JOIN) с таблицами этих объектов на уровне СУБД. В случае составных типов данных количество таких скрытых соединений возрастает лавинообразно, перегружая оптимизатор СУБД лишней работой. Всегда обращайтесь к ссылочному полю напрямую: РегЗаказы.ЗаказНаПроизводство.
---
В исходном коде получение остатков по складам было реализовано через четыре левых соединения к виртуальной таблице одного и того же регистра сведений:
ИЗ
РегистрНакопления.ПартииТоваровНаСкладах.Остатки(&ДатаКон, Склад = &Склад) КАК СвободныеОстаткиОстатки
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ПартииТоваровНаСкладах.Остатки(&ДатаКон, Склад = &Склад2) КАК СвободныеОстаткиОстатки1
...
Каждое обращение к виртуальной таблице остатков заставляет систему выполнять тяжелые расчеты на лету с вычислением актуальных итогов. Выполнять эту процедуру четыре раза подряд для каждого склада — крайне неэффективно. К тому же, если бизнесу потребуется добавить пятый или шестой склад, вам придется вручную переписывать запрос и структуру метаданных отчета.
Разберем два правильных способа решения этой задачи.
Рекомендуем полностью отказаться от жесткого разделения складов в самом тексте запроса. Получим остатки одной плоской таблицей, передав список нужных складов в параметры виртуальной таблицы:
ВЫБРАТЬ
СвободныеОстатки.Номенклатура КАК НоменклатураСклад,
СвободныеОстатки.Склад КАК Склад,
СвободныеОстатки.КоличествоОстаток КАК ОстатокНаСкладе
ПОМЕСТИТЬ ОстаткиНаСкладах
ИЗ
РегистрНакопления.ПартииТоваровНаСкладах.Остатки(&ДатаКон, Склад В (&СписокСкладов)) КАК СвободныеОстатки
ГДЕ
СвободныеОстатки.КоличествоОстаток > 0
ИНДЕКСИРОВАТЬ ПО
НоменклатураСклад
В настройках компоновщика данных (СКД) на вкладке «Настройки» выведем структуру отчета в виде «Таблицы», где:
Склад.ОстатокНаСкладе.Платформа СКД автоматически транспонирует строки в колонки на этапе вывода отчета. Это решение работает максимально быстро, не нагружает СУБД лишними расчетами и легко масштабируется на любое количество складов без изменения схемы запроса.
Если вам все же жестко требуется иметь отдельные колонки под каждый склад непосредственно на уровне результирующей выборки запроса, воспользуемся конструкцией ВЫБОР в сочетании с группировкой. Это позволит прочитать таблицу остатков всего один раз:
ВЫБРАТЬ
СвободныеОстатки.Номенклатура КАК НоменклатураСклад,
СУММА(ВЫБОР КОГДА СвободныеОстатки.Склад = &Склад1 ТОГДА СвободныеОстатки.КоличествоОстаток ИНАЧЕ 0 КОНЕЦ) КАК ОстатокСклад1,
СУММА(ВЫБОР КОГДА СвободныеОстатки.Склад = &Склад2 ТОГДА СвободныеОстатки.КоличествоОстаток ИНАЧЕ 0 КОНЕЦ) КАК ОстатокСклад2,
СУММА(ВЫБОР КОГДА СвободныеОстатки.Склад = &Склад3 ТОГДА СвободныеОстатки.КоличествоОстаток ИНАЧЕ 0 КОНЕЦ) КАК ОстатокСклад3
ПОМЕСТИТЬ ОстаткиНаСкладах
ИЗ
РегистрНакопления.ПартииТоваровНаСкладах.Остатки(&ДатаКон, Склад В (&СписокСкладов)) КАК СвободныеОстатки
СГРУППИРОВАТЬ ПО
СвободныеОстатки.Номенклатура
---
В исходном запросе во втором пакете выполнялось условие:
ГДЕ
СпецификацииНоменклатурыИсходныеКомплектующие.Номенклатура.Комментарий ПОДОБНО &Коммент
Проанализируем эту ситуацию. Во-первых, обращение к полю Комментарий через справочник Номенклатура порождает неявное левое соединение. Во-вторых, реквизит Комментарий имеет тип «Строка неограниченной длины». Фильтрация по таким полям через оператор ПОДОБНО полностью исключает использование индексов СУБД. База данных вынуждена производить полное сканирование всей таблицы (Table Scan), считывая тяжелые LOB-данные с диска, что критически замедляет работу.
Рекомендация по оптимизации архитектуры: Хранение классифицирующих признаков в комментариях — грубая ошибка проектирования. Для этих целей необходимо использовать механизм Дополнительных реквизитов и сведений либо создать отдельный реквизит в справочнике Номенклатура с ограниченной длиной (например, Строка 50) и обязательно проиндексировать его. Если же вам временно приходится работать с текущей архитектурой, постарайтесь ограничить выборку спецификаций другими реквизитами (например, по группе номенклатуры или признаку активности спецификации), чтобы минимизировать объем обрабатываемых строк.
---
Сведем все рекомендации воедино и построим чистый, быстрый и масштабируемый запрос для СКД с использованием временных таблиц и правильным индексированием:
// ПАКЕТ 1: Получаем только незакрытые заказы на производство
ВЫБРАТЬ РАЗРЕШЕННЫЕ
РегЗаказы.ЗаказНаПроизводство КАК ЗаказНаПроизводство,
РегЗаказы.Номенклатура КАК Продукция,
РегЗаказы.Спецификация КАК Спецификация,
РегЗаказы.ЕдиницаИзмерения КАК ЕдиницаИзмерения,
РегЗаказы.КоличествоОстаток КАК КоличествоОстаток
ПОМЕСТИТЬ ЗаказыНаПроизводство
ИЗ
РегистрНакопления.ЗаказыНаПроизводство.Остатки(&ДатаКон, ) КАК РегЗаказы
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ПричиныЗакрытияЗаказов КАК ПричиныЗакрытияЗаказов
ПО РегЗаказы.ЗаказНаПроизводство = ПричиныЗакрытияЗаказов.Заказ
ГДЕ
ПричиныЗакрытияЗаказов.Заказ ЕСТЬ NULL
ИНДЕКСИРОВАТЬ ПО
Спецификация
;
// ПАКЕТ 2: Получаем комплектующие из спецификаций незакрытых заказов
ВЫБРАТЬ
СпецификацииНоменклатуры.Номенклатура КАК Полуфабрикат,
СпецификацииНоменклатуры.Количество КАК КоличествоВСпец,
СпецификацииНоменклатуры.Ссылка КАК Спецификация
ПОМЕСТИТЬ НоменклатураСпецификаций
ИЗ
Справочник.СпецификацииНоменклатуры.ИсходныеКомплектующие КАК СпецификацииНоменклатуры
ГДЕ
СпецификацииНоменклатуры.Ссылка В (ВЫБРАТЬ РАЗЛИЧНЫЕ Заказы.Спецификация ИЗ ЗаказыНаПроизводство КАК Заказы)
ИНДЕКСИРОВАТЬ ПО
Полуфабрикат
;
// ПАКЕТ 3: Получаем остатки на складах в один проход по индексированному списку комплектующих
ВЫБРАТЬ
СвободныеОстатки.Номенклатура КАК НоменклатураСклад,
СвободныеОстатки.Склад КАК Склад,
СУММА(СвободныеОстатки.КоличествоОстаток) КАК ОстатокНаСкладе
ПОМЕСТИТЬ ОстаткиНаСкладах
ИЗ
РегистрНакопления.ПартииТоваровНаСкладах.Остатки(&ДатаКон,
Склад В (&СписокСкладов) И
Номенклатура В (ВЫБРАТЬ РАЗЛИЧНЫЕ Комплектующие.Полуфабрикат ИЗ НоменклатураСпецификаций КАК Комплектующие)
) КАК СвободныеОстатки
ГДЕ
СвободныеОстатки.КоличествоОстаток > 0
СГРУППИРОВАТЬ ПО
СвободныеОстатки.Номенклатура,
СвободныеОстатки.Склад
ИНДЕКСИРОВАТЬ ПО
НоменклатураСклад
;
// ПАКЕТ 4: Результирующее объединение данных
ВЫБРАТЬ
ЗаказыНаПроизводство.ЗаказНаПроизводство КАК ЗаказНаПроизводство,
ЗаказыНаПроизводство.Продукция КАК Продукция,
ЗаказыНаПроизводство.КоличествоОстаток КАК ОстатокЗаказа,
НоменклатураСпецификаций.Полуфабрикат КАК Комплектующая,
НоменклатураСпецификаций.КоличествоВСпец КАК КоличествоВСпецификации,
ОстаткиНаСкладах.Склад КАК СкладОстатка,
ОстаткиНаСкладах.ОстатокНаСкладе КАК ОстатокНаСкладе
ИЗ
ЗаказыНаПроизводство КАК ЗаказыНаПроизводство
ВНУТРЕННЕЕ СОЕДИНЕНИЕ НоменклатураСпецификаций КАК НоменклатураСпецификаций
ПО ЗаказыНаПроизводство.Спецификация = НоменклатураСпецификаций.Спецификация
ЛЕВОЕ СОЕДИНЕНИЕ ОстаткиНаСкладах КАК ОстаткиНаСкладах
ПО НоменклатураСпецификаций.Полуфабрикат = ОстаткиНаСкладах.НоменклатураСклад
Давайте резюмируем, какие качественные изменения произошли в запросе после нашей оптимизации:
NULL. Теперь потребление памяти стабильно низкое независимо от размера базы данных.Спецификация, Полуфабрикат, НоменклатураСклад), что гарантирует моментальное слияние данных в финальном пакете.Используя данные методы оптимизации запросов, вы обеспечите быструю и стабильную работу ваших отчетов даже на крупных высоконагруженных базах данных.