Как связать регистр накопления с дополнительными свойствами номенклатуры (ТОП/ABC) в запросе 1С

← На главную

При разработке отчетов и обработок в системе 1С программисты часто сталкиваются с необходимостью отбора или группировки данных по свойствам объектов, которые не хранятся в виде прямых реквизитов справочника. Ярким примером является классификация номенклатуры (классы ТОП, ABC, XYZ). Такие значения обычно рассчитываются динамически или хранятся в специальном регистре сведений дополнительных реквизитов и сведений.

В этой статье мы подробно разберем, как правильно связать данные регистра накопления (например, ПартииТоваровНаСкладах) с дополнительными свойствами из регистра сведений ЗначенияСвойствОбъектов, проанализируем типичные ошибки новичков, приводящие к падению производительности СУБД, и построим красивый, оптимизированный и быстродействующий запрос.

Постановка задачи и анализ структуры данных

Представим сценарий: нам необходимо получить список документов списания и оприходования товаров с детализацией по дате, складу, номенклатуре и стоимости. При этом в выборку должна попасть только та номенклатура, которой присвоен определенный класс важности (в нашем примере — ТОП-классы А и B).

Сложность заключается в том, что в справочнике Номенклатура нет явного реквизита "ТОП". Вместо этого используется механизм дополнительных свойств. Данные хранятся в периодическом или непериодическом регистре сведений ЗначенияСвойствОбъектов, который имеет следующую структуру:

Измерение Объект — ссылка на объект конфигурации (в нашем случае — справочник Номенклатура). Имеет составной тип данных.
Измерение Свойство — ссылка на План видов характеристик (ПВХ), определяющий суть свойства (например, свойство "ТОП-класс номенклатуры").
Ресурс Значение — конкретное значение свойства (например, элемент справочника "Значения свойств объектов" со значением "А", "B", "C" и т.д.). Также имеет составной тип.

Наша цель — соединить таблицу партий товаров с этой таблицей свойств по ключевому полю номенклатуры и наложить жесткий отбор.

Шаг 1. Получение базовых данных во временную таблицу

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

Давайте посмотрим на пример реализации первого пакета запроса:


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

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

Обратите внимание на инструкцию ИНДЕКСИРОВАТЬ ПО Номенклатура в самом конце пакета. Мы принудительно создаем индекс по полю связи во временной таблице. Это критически важно, так как при последующем соединении с регистром сведений СУБД сможет мгновенно сопоставить строки, избегая полного сканирования таблиц (Table Scan).

Шаг 2. Соединение с регистром сведений дополнительных свойств

Теперь рассмотрим, как правильно связать нашу временную таблицу ВТ_ОбщиеДанныеПартий с регистром сведений ЗначенияСвойствОбъектов. Основная ошибка начинающих разработчиков — попытка связать значение свойства напрямую со ссылкой на номенклатуру, путая измерения "Объект" и "Значение".

Правильное условие связи выглядит так: ВТ_ОбщиеДанныеПартий.Номенклатура = ЗначенияСвойствОбъектов.Объект.

Посмотрим на структуру второго запроса пакета:


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

Использование Внутреннего соединения (ВНУТРЕННЕЕ СОЕДИНЕНИЕ) гарантирует, что в финальную выборку попадут только те позиции номенклатуры, для которых в регистре сведений действительно заполнено указанное свойство с нужным значением.

Анализ скрытых проблем и оптимизация производительности

Несмотря на то, что приведенный выше запрос является рабочим, в реальных многопользовательских базах данных "1С:Предприятие" с сотнями тысяч записей он может вызывать существенные задержки и блокировки. Давайте проанализируем "узкие места" и исправим их.

1. Проблема разыменования составных типов (обращение "через точку")

В первом запросе пакета используется конструкция: ПартииТоваровНаСкладах.Регистратор.ИнвентаризацияТоваровНаСкладе. Поле Регистратор в регистрах накопления имеет составной тип данных (ссылается на десятки видов документов). Когда платформа транслирует обращение к реквизиту регистратора через точку на уровень СУБД, SQL-сервер генерирует левые соединения (LEFT JOIN) со всеми таблицами документов, входящими в этот тип. Это приводит к созданию огромного, крайне неэффективного SQL-плана запроса.

Решение: Ограничить тип регистратора с помощью функции ВЫРАЗИТЬ():


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

2. Отказ от поиска по кодам (строкам)

В коде новичков часто можно увидеть отборы вида: СвойстваОбъектов.Свойство.Код = "000000467". Это серьезная архитектурная ошибка:

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

Решение: Всегда передавайте нужные элементы Планов видов характеристик и справочников в качестве внешних параметров (&ПараметрСвойствоТОП и &МассивЗначенийТОП), предварительно получая ссылки на них в коде на встроенном языке (через предопределенные элементы или настройки).

Финальный эталонный вариант запроса

Учитывая все архитектурные требования и стандарты разработки "1С", мы можем составить оптимизированный вариант запроса, готовый к высоким нагрузкам:


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

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ОбщиеДанные.ДатаДокумента КАК ДатаДокумента,
    ОбщиеДанные.Склад КАК Склад,
    ОбщиеДанные.Номенклатура КАК Номенклатура,
    СвойстваОбъектов.Значение КАК ЗначениеСвойстваНоменклатуры,
    СвойстваОбъектов.Свойство КАК СвойствоНоменклатуры,
    ОбщиеДанные.ДокументИнвентаризации КАК ДокументИнвентаризации,
    ОбщиеДанные.СтоимостьСписания КАК СтоимостьСписания,
    ОбщиеДанные.СтоимостьОприходования КАК СтоимостьОприходования
ИЗ
    ВТ_ОптимизированныеПартии КАК ОбщиеДанные
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК СвойстваОбъектов
        ПО ОбщиеДанные.Номенклатура = СвойстваОбъектов.Объект
ГДЕ
    СвойстваОбъектов.Свойство = &СвойствоТОП
    И СвойстваОбъектов.Значение В (&КлассыТОП)
    И НЕ ОбщиеДанные.ДокументИнвентаризации = ЗНАЧЕНИЕ(Документ.ИнвентаризацияТоваровНаСкладе.ПустаяСсылка)

УПОРЯДОЧИТЬ ПО
    ДатаДокумента УБЫВ
ИТОГИ
    СУММА(СтоимостьСписания),
    СУММА(СтоимостьОприходования)
ПО
    Склад,
    ЗначениеСвойстваНоменклатуры,
    Номенклатура,
    ДокументИнвентаризации

Альтернативное решение для современных конфигураций (ERP, КА, УТ 11)

Если вы работаете в современной конфигурации на управляемых формах, классификация номенклатуры по классам A, B, C чаще всего реализована в рамках стандартной подсистемы ABC/XYZ-классификации.

В этом случае вместо тяжелого универсального регистра ЗначенияСвойствОбъектов настоятельно рекомендуется использовать специализированный регистр сведений ABCXYZКлассификацияНоменклатуры. Запрос к нему выполняется в разы быстрее, так как его структура максимально проста и содержит прямые проиндексированные измерения: Номенклатура, РазделКлассификации и ресурс Класс (значение перечисления).