Как найти серии номенклатуры без движений и оптимизировать запрос в 1С?

← На главную

При обслуживании и очистке информационных баз нередко возникает задача выявления неиспользуемых аналитических разрезов. В частности, требуется быстро и без лишней нагрузки на СУБД найти все элементы справочника СерииНоменклатуры, по которым за всю историю базы не было зафиксировано ни одного движения. Рассмотрим классические подходы к составлению такого запроса в платформе 1С:Предприятие, разберем поведение оптимизатора СУБД, оценим подводные камни каждого варианта и выясним, какие альтернативные инструменты платформы помогают решить задачу максимально эффективно.

Вариант 1. Классическое левое соединение с проверкой на NULL

Разберем самый распространенный подход, принятый в большинстве реляционных баз данных. В платформе 1С отсутствуют прямые операторы EXCEPT или NOT EXISTS, поэтому поиск отсутствующих записей стандартно реализуется через ЛЕВОЕ СОЕДИНЕНИЕ основной таблицы с зависимой и последующую фильтрацию по признаку ЕСТЬ NULL.

Посмотрим на пример базового запроса к реальной таблице движений регистра накопления:


ВЫБРАТЬ
    Серии.Ссылка КАК Серия
ИЗ
    Справочник.СерииНоменклатуры КАК Серии
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ПартииТоваровНаСкладах КАК Партии
    ПО Серии.Ссылка = Партии.СерияНоменклатуры
ГДЕ
    Партии.СерияНоменклатуры ЕСТЬ NULL

Проанализируем работу этого запроса на уровне СУБД. При наличии индексов по полям соединения оптимизатор SQL преобразует такую конструкцию в физический оператор Left Anti Semi Join (или применяет Merge Join / Hash Match). Однако прямое соединение со всей физической таблицей регистра накопления несет в себе серьезные издержки: в реальной таблице движений могут храниться миллионы строк с одной и той же серией. Это приводит к избыточному сканированию огромного массива записей перед выполнением фильтрации.

Вариант 2. Конструкция «НЕ В» и ловушка трехзначной логики

Рассмотрим альтернативную конструкцию, к которой разработчики часто прибегают на небольших базах: использование предиката НЕ В (ВЫБРАТЬ ...) с подзапросом.


ВЫБРАТЬ
    Серии.Ссылка КАК Серия
ИЗ
    Справочник.СерииНоменклатуры КАК Серии
ГДЕ
    Серии.Ссылка НЕ В
        (ВЫБРАТЬ РАЗЛИЧНЫЕ
            Партии.СерияНоменклатуры
        ИЗ
            РегистрНакопления.ПартииТоваровНаСкладах КАК Партии
        ГДЕ
            Партии.СерияНоменклатуры ЕСТЬ НЕ NULL)

Выясним причину, почему разработчики с осторожностью относятся к конструкции НЕ В:

Проблема трехзначной логики (ANSI NULL): Если во вложенном запросе окажется хотя бы одно значение NULL (например, при наличии движений без указания серии), оператор НЕ В вернет логическое значение UNKNOWN. В результате весь запрос вернет пустой результат, даже если в справочнике есть тысячи неиспользованных серий. Поэтому фильтрация ГДЕ СерияНоменклатуры ЕСТЬ НЕ NULL во вложенном запросе строго обязательна.
Трансляция в СУБД: Если MS SQL Server часто справляется с переписыванием NOT IN в Anti Semi Join, то в PostgreSQL вложенный подзапрос нередко превращается в ресурсоемкий SubPlan, вызывающий вычисление для каждой строки внешнего справочника. На базах большого объема это вызывает критические задержки выполнения.

Вариант 3. Оптимизация через виртуальную таблицу оборотов

Чтобы избежать сканирования всей физической таблицы регистра движений, обратимся к предрассчитанным таблицам итогов. Для этого воспользуемся виртуальной таблицей Обороты регистра накопления без указания периода.

Виртуальная таблица оборотов считывает готовые итоги, где движения уже свернуты по уникальным комбинациям измерений. Это сокращает объем обрабатываемых строк в десятки и сотни раз:


ВЫБРАТЬ
    Серии.Ссылка КАК Серия
ИЗ
    Справочник.СерииНоменклатуры КАК Серии
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ПартииТоваровНаСкладах.Обороты(, , Авто, ) КАК ПартииОбороты
    ПО Серии.Ссылка = ПартииОбороты.СерияНоменклатуры
ГДЕ
    ПартииОбороты.СерияНоменклатуры ЕСТЬ NULL

Вариант 4. Использование индексированной временной таблицы

Если справочник серий и регистр партий содержат колоссальное количество данных, наиболее управляемый и быстрый план выполнения дает предварительная выгрузка используемых серий во временную таблицу с явным индексированием.


ВЫБРАТЬ РАЗЛИЧНЫЕ
    ПартииОбороты.СерияНоменклатуры КАК СерияНоменклатуры
ПОМЕСТИТЬ ВТ_ИспользованныеСерии
ИЗ
    РегистрНакопления.ПартииТоваровНаСкладах.Обороты(, , Авто, ) КАК ПартииОбороты
ГДЕ
    ПартииОбороты.СерияНоменклатуры ЕСТЬ НЕ NULL

ИНДЕКСИРОВАТЬ ПО
    СерияНоменклатуры
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Серии.Ссылка КАК Серия
ИЗ
    Справочник.СерииНоменклатуры КАК Серии
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИспользованныеСерии КАК ИспользованныеСерии
    ПО Серии.Ссылка = ИспользованныеСерии.СерияНоменклатуры
ГДЕ
    ИспользованныеСерии.СерияНоменклатуры ЕСТЬ NULL

В этом сценарии СУБД строит индекс в рабочей базе tempdb, после чего слияние двух отсортированных наборов (Merge Join) выполняется с минимальным потреблением оперативной памяти и ресурсов процессора.

Вариант 5. Объединение с группировкой по признаку присутствия

Посмотрим на алгоритм исключения через объединение наборов и фильтрацию по агрегатной функции. Этот способ позволяет исключить сложные операции соединения, заменив их единой сортировкой и группировкой:


ВЫБРАТЬ
    Серии.Ссылка КАК Серия,
    0 КАК Флаг
ПОМЕСТИТЬ ВТ_ВсеСерии
ИЗ
    Справочник.СерииНоменклатуры КАК Серии

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ РАЗЛИЧНЫЕ
    ПартииОбороты.СерияНоменклатуры,
    1
ИЗ
    РегистрНакопления.ПартииТоваровНаСкладах.Обороты(, , Авто, ) КАК ПартииОбороты
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ.Серия КАК Серия
ИЗ
    ВТ_ВсеСерии КАК ВТ
ГДЕ
    ВТ.Серия ЕСТЬ НЕ NULL
СГРУППИРОВАТЬ ПО
    ВТ.Серия
ИМЕЮЩИЕ
    МАКСИМУМ(ВТ.Флаг) = 0

Предметные особенности конфигураций (УПП, ERP, КА)

Проанализируем прикладную специфику учета серий, которую обязательно нужно учитывать перед выполнением любых запросов:

Неполнота одного регистра: В типовых конфигурациях (например, 1С:Управление производственным предприятием) серии номенклатуры фиксируются не только в регистре ПартииТоваровНаСкладах. Они могут присутствовать в оперативных складских регистрах (ТоварыНаСкладах, ТоварыВРознице), а также в резервах (ТоварыВРезервеНаСкладах). Проверка исключительно одного регистра приведет к ошибочному признанию серии «неиспользованной», если партия еще не списана или распределена по другим складским ордерам.
Режим РАУЗ (Расширенная аналитика учета затрат): Если в информационной базе активирован режим РАУЗ, регистр ПартииТоваровНаСкладах вообще не формирует движений. Вся себестоимость ведется по регистрам учета затрат. В таком случае запрос к партиям покажет отсутствие движений по абсолютно всем существующим сериям базы.
Серии в документах без движений: Серия может быть выбрана в непроведенном документе, в заказе покупателя или во внутреннем заказе, которые не делают движений по складским остаткам. Удалять такие серии прямым запросом нельзя.

Альтернативный платформенный метод: НайтиПоСсылкам()

Если конечная цель поиска серий без движений — очистка базы и удаление неиспользуемых элементов, прямые запросы к регистрам не гарантируют ссылочную целостность базы данных. В этом случае правильнее воспользоваться методом глобального контекста НайтиПоСсылкам().


// Формируем массив всех серий номенклатуры
Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |    Серии.Ссылка КАК Ссылка
    |ИЗ
    |    Справочник.СерииНоменклатуры КАК Серии";

МассивСерий = Запрос.Выполнить().Выгрузить().ВыгрузитьКолонку("Ссылка");

// Ищем ссылки во всех метаданных конфигурации
ТаблицаСсылок = НайтиПоСсылкам(МассивСерий);

// Собираем ссылки, на которые есть указания в других объектах
ИспользуемыеСерии = Новый Соответствие;
Для Каждого Стр Из ТаблицаСсылок Цикл
    ИспользуемыеСерии.Вставить(Стр.Ссылка, Истина);
КонецЦикла;

// Получаем действительно пустые серии для безопасного удаления
НеиспользуемыеСерии = Новый Массив;
Для Каждого Серия Из МассивСерий Цикл
    Если ИспользуемыеСерии[Серия] = Неопределено Тогда
        НеиспользуемыеСерии.Добавить(Серия);
    КонецЕсли;
КонецЦикла;

Метод НайтиПоСсылкам() проверяет реквизиты документов, табличные части, периодические регистры сведений и планы видов характеристик. Это полностью исключает риск нарушения ссылочной целостности информационной базы.