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

← На главную

При разработке отчетов и обработок в конфигурациях на платформе 1С:Предприятие 8 (таких как ERP 2, УТ 11, КА 2) программисты часто сталкиваются с необходимостью получения остатков товаров по конкретной номенклатуре сразу из нескольких регистров накопителя. Подобные задачи требуют не только понимания синтаксиса языка запросов, но и знания механизмов СУБД для обеспечения высокой производительности системы.

В этой статье мы подробно разберем, как правильно наложить отбор по номенклатуре в запросе, проанализируем типичные ошибки начинающих разработчиков на примере реальной ситуации с форума и рассмотрим продвинутые методы оптимизации таких запросов с использованием временных таблиц и ключей аналитики.

Разбор проблемы: почему не работал «интуитивный» вариант?

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

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


ИЗ
    РегистрНакопления.ТоварыОрганизаций.Остатки(, АналитикаУчетаНоменклатуры.Склад = &Склад) КАК ТоварыОрганизацийОстатки,
    РегистрНакопления.ТоварыОрганизаций.Остатки(, АналитикаУчетаНоменклатуры.Номенклатура = &Номенклатура) КАК ТоварыОрганизацийОстатки2

Такой подход в корне неверен по нескольким причинам:

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

Правильное решение: использование оператора «И» в параметрах виртуальной таблицы

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

Разберем, как должен выглядеть правильный отбор для каждого из используемых регистров в запросе:

1. Регистр «Товары на складах»

Здесь измерение Номенклатура является прямым, поэтому мы просто объединяем условия через оператор И во втором параметре (параметре условий):


РегистрНакопления.ТоварыНаСкладах.Остатки(, Склад = &Склад И Номенклатура = &Номенклатура) КАК ТоварыНаСкладахОстатки

2. Регистр «Товары в ячейках»

Аналогично первому регистру, накладываем отбор по номенклатуре и владельцу ячейки (складу):


РегистрНакопления.ТоварыВЯчейках.Остатки(, Ячейка.Владелец = &Склад И Номенклатура = &Номенклатура) КАК ТоварыВЯчейкахОстатки

3. Регистр «Товары организаций» (учет через ключи аналитики)

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


РегистрНакопления.ТоварыОрганизаций.Остатки(, АналитикаУчетаНоменклатуры.Склад = &Склад И АналитикаУчетаНоменклатуры.Номенклатура = &Номенклатура) КАК ТоварыОрганизацийОстатки

Почему важно накладывать отбор именно в параметрах виртуальной таблицы?

Среди начинающих разработчиков распространена ошибка, когда отбор помещают в секцию ГДЕ в самом конце запроса. Выясним причину, почему это крайне неэффективно.

Когда мы пишем условия в параметрах виртуальной таблицы:

РегистрНакопления.ТоварыНаСкладах.Остатки(, Номенклатура = &Номенклатура)

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

Если же мы напишем запрос следующим образом:


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

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

Оптимизация производительности: избавляемся от обращений «через точку»

Проанализируем исходный запрос автора темы. В финальной выборке мы видим следующие строки:


ЗапросПоДанным.Номенклатура.Код КАК НоменклатураКод,
ЗапросПоДанным.Номенклатура.Артикул КАК НоменклатураАртикул

В языке запросов 1С обращение к реквизитам через точку от поля ссылочного типа (Номенклатура.Код) приводит к автоматическому созданию неявного левого соединения (LEFT JOIN) с таблицей справочника в результирующем SQL-запросе. Когда это происходит внутри больших циклов или сложных группировок, производительность падает.

Рекомендуемый подход для оптимизации:

Сначала получить все остатки и числовые данные во временную таблицу, оперируя исключительно ссылками (только поле Номенклатура).
В самом финальном пакете запроса соединить полученную временную таблицу со справочником Номенклатура по ссылке один-единственный раз и получить код, артикул и наименование.

Продвинутый пример: фильтрация по списку номенклатуры

Очень часто бизнес-логика требует отбора не по одной позиции, а по списку (например, по массиву или таблице значений, переданной из формы). Рассмотрим, как правильно реализовать отбор по списку товаров с помощью оператора В:


РегистрНакопления.ТоварыНаСкладах.Остатки(, Номенклатура В (&СписокНоменклатуры) И Склад = &Склад)

Если переданный массив &СписокНоменклатуры содержит до нескольких десятков элементов, данный запрос отработает быстро. Однако если элементов тысячи, СУБД может построить неоптимальный план запроса. В таких случаях список номенклатуры рекомендуется предварительно поместить во временную таблицу, проиндексировать ее и выполнить внутреннее соединение с виртуальной таблицей остатков.

Готовый оптимизированный вариант запроса

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


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

СГРУППИРОВАТЬ ПО
    ТоварыОрганизацийОстатки.АналитикаУчетаНоменклатуры.Номенклатура

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

ВЫБРАТЬ
    ТоварыНаСкладахОстатки.Номенклатура,
    0,
    СУММА(ТоварыНаСкладахОстатки.ВНаличииОстаток),
    0
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(, Склад = &Склад И Номенклатура = &Номенклатура) КАК ТоварыНаСкладахОстатки

СГРУППИРОВАТЬ ПО
    ТоварыНаСкладахОстатки.Номенклатура

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

ВЫБРАТЬ
    ТоварыВЯчейкахОстатки.Номенклатура,
    0,
    0,
    СУММА(ТоварыВЯчейкахОстатки.ВНаличииОстаток)
ИЗ
    РегистрНакопления.ТоварыВЯчейках.Остатки(, Ячейка.Владелец = &Склад И Номенклатура = &Номенклатура) КАК ТоварыВЯчейкахОстатки

СГРУППИРОВАТЬ ПО
    ТоварыВЯчейкахОстатки.Номенклатура
;

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

СГРУППИРОВАТЬ ПО
    ПромежуточныйЗапрос.Номенклатура,
    СправочникНоменклатура.Код,
    СправочникНоменклатура.Артикул

ИМЕЮЩИЕ
    (СУММА(ПромежуточныйЗапрос.ТоварыОрганизаций) <> СУММА(ПромежуточныйЗапрос.ТоварыНаСкладах)
    ИЛИ СУММА(ПромежуточныйЗапрос.ТоварыВЯчейках) <> СУММА(ПромежуточныйЗапрос.ТоварыНаСкладах)
    ИЛИ СУММА(ПромежуточныйЗапрос.ТоварыОрганизаций) <> СУММА(ПромежуточныйЗапрос.ТоварыВЯчейках))

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