Как правильно и эффективно получить остатки товаров, а также списки складов и номенклатуры в 1С?

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

Изначально перед нами стоит три основные цели:

1. Получить остатки по товарам (например, номенклатуру, склад, количество остаток).
2. Получить уникальные склады, по которым были получены остатки.
3. Получить уникальную номенклатуру, по которой были получены остатки.

Важно отметить, что списки складов и номенклатуры должны быть сформированы именно на основе полученных остатков, а не из всего справочника или регистра.

Решение 1: Использование временных таблиц в пакете запросов (Рекомендуемый подход)

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

Преимущества этого подхода:

1. Читаемость: Запрос становится более структурированным и понятным.
2. Оптимизация СУБД: Временные таблицы создаются и обрабатываются непосредственно на сервере базы данных, что минимизирует передачу избыточных данных между сервером 1С и СУБД.
3. Многократное использование данных: Промежуточные данные (в данном случае, остатки) можно многократно использовать в рамках одного пакета запросов без повторного обращения к исходному регистру.
4. Единая транзакция: Весь пакет запросов выполняется как единая транзакция, что повышает общую производительность по сравнению с выполнением нескольких отдельных запросов.

Рассмотрим пример кода для данного подхода:

Запрос = Новый Запрос;
Запрос.Текст = 
"ВЫБРАТЬ
    |    ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
    |    ТоварыНаСкладахОстатки.Склад КАК Склад,
    |    ТоварыНаСкладахОстатки.КоличествоОстаток КАК КоличествоОстаток
    |ПОМЕСТИТЬ ВТ_Остатки
    |ИЗ
    |    РегистрНакопления.ТоварыНаСкладах.Остатки(&НаМомент) КАК ТоварыНаСкладахОстатки
    |ГДЕ
    |    ТоварыНаСкладахОстатки.КоличествоОстаток > 0
    |;
    |
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ
    |    |    ВТ_Остатки.Номенклатура,
    |    |    ВТ_Остатки.Склад,
    |    |    ВТ_Остатки.КоличествоОстаток
    |    |ИЗ
    |    |    ВТ_Остатки КАК ВТ_Остатки
    |    |;
    |
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ РАЗЛИЧНЫЕ
    |    ВТ_Остатки.Склад КАК Склад
    |ИЗ
    |    ВТ_Остатки КАК ВТ_Остатки
    |;
    |
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ РАЗЛИЧНЫЕ
    |    ВТ_Остатки.Номенклатура КАК Номенклатура
    |ИЗ
    |    ВТ_Остатки КАК ВТ_Остатки";

Запрос.УстановитьПараметр("НаМомент", Новый Граница(ТекущаяДата(), ВидГраницы.Исключая));

// Если необходимо получить результаты каждой выборки пакета
// Для этого используем метод ВыполнитьПакетСПромежуточнымиДанными
РезультатЗапроса = Запрос.ВыполнитьПакетСПромежуточнымиДанными();

// Получаем табличные части по индексу (начиная с 0)
// Первая таблица - это сами остатки
ВыборкаОстатки = РезультатЗапроса[0].Выбрать();

// Вторая таблица - уникальные склады
ВыборкаСклады = РезультатЗапроса[1].Выбрать();

// Третья таблица - уникальная номенклатура
ВыборкаНоменклатура = РезультатЗапроса[2].Выбрать();

В этом примере мы сначала помещаем все остатки в ВТ_Остатки. Затем, в этом же пакете запросов, мы делаем три выборки:

1. Первая выборка возвращает все записи из ВТ_Остатки – это наши основные остатки.
2. Вторая выборка с использованием ключевого слова РАЗЛИЧНЫЕ (аналог SQL-оператора DISTINCT) возвращает уникальные склады из ВТ_Остатки.
3. Третья выборка, также с РАЗЛИЧНЫЕ, возвращает уникальную номенклатуру из ВТ_Остатки.

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

Решение 2: Несколько запросов в пакете без временных таблиц

Этот подход предполагает выполнение трех отдельных запросов в одном пакете, где каждый запрос напрямую обращается к виртуальной таблице регистра накопления. Склады и номенклатура получаются с использованием ключевого слова РАЗЛИЧНЫЕ.

Преимущества:

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

Недостатки:

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

Пример кода:

Запрос = Новый Запрос;
Запрос.Текст = 
"ВЫБРАТЬ
    |    ТоварыНаСкладахОстатки.Номенклатура,
    |    ТоварыНаСкладахОстатки.Склад,
    |    ТоварыНаСкладахОстатки.КоличествоОстаток
    |ИЗ
    |    РегистрНакопления.ТоварыНаСкладах.Остатки(&НаМомент) КАК ТоварыНаСкладахОстатки
    |ГДЕ
    |    ТоварыНаСкладахОстатки.КоличествоОстаток > 0
    |;
    |
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ РАЗЛИЧНЫЕ
    |    ТоварыНаСкладахОстатки.Склад
    |ИЗ
    |    РегистрНакопления.ТоварыНаСкладах.Остатки(&НаМомент) КАК ТоварыНаСкладахОстатки
    |ГДЕ
    |    ТоварыНаСкладахОстатки.КоличествоОстаток > 0
    |;
    |
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ РАЗЛИЧНЫЕ
    |    ТоварыНаСкладахОстатки.Номенклатура
    |ИЗ
    |    РегистрНакопления.ТоварыНаСкладах.Остатки(&НаМомент) КАК ТоварыНаСкладахОстатки
    |ГДЕ
    |    ТоварыНаСкладахОстатки.КоличествоОстаток > 0";

Запрос.УстановитьПараметр("НаМомент", Новый Граница(ТекущаяДата(), ВидГраницы.Исключая));

РезультатЗапроса = Запрос.ВыполнитьПакет(); // Или ВыполнитьПакетСПромежуточнымиДанными, т.к. временных таблиц нет

ВыборкаОстатки = РезультатЗапроса[0].Выбрать();
ВыборкаСклады = РезультатЗапроса[1].Выбрать();
ВыборкаНоменклатура = РезультатЗапроса[2].Выбрать();

В этом варианте, хотя мы и используем пакет запросов, каждая выборка складов и номенклатуры заново обращается к виртуальной таблице остатков регистра. Если объем данных большой, и виртуальная таблица достаточно "тяжелая" для вычисления, это может привести к дополнительным затратам ресурсов СУБД. Однако, для большинства стандартных случаев, когда не требуется сложная фильтрация, такой подход может быть вполне приемлемым.

Решение 3: Обход выборки на стороне 1С

Этот подход подразумевает получение всех остатков одним запросом, а затем итерацию по полученным данным на стороне 1С (на сервере или клиенте) для формирования списков уникальных складов и номенклатуры.

Преимущества:

1. Простота кода 1С: Если вы привыкли работать с коллекциями в 1С, такой подход может показаться более интуитивным.

Недостатки:

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

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

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

Запрос.УстановитьПараметр("НаМомент", Новый Граница(ТекущаяДата(), ВидГраницы.Исключая));

ВыборкаОстатки = Запрос.Выполнить().Выбрать();

МассивСкладов = Новый Массив;
МассивНоменклатуры = Новый Массив;

// Для более быстрой проверки уникальности используем Соответствие
СоответствиеСкладов = Новый Соответствие;
СоответствиеНоменклатуры = Новый Соответствие;

Пока ВыборкаОстатки.Следующий() Цикл
    // Добавляем сами остатки в какую-либо коллекцию, если нужно

    Если СоответствиеСкладов.Получить(ВыборкаОстатки.Склад) = Неопределено Тогда
        МассивСкладов.Добавить(ВыборкаОстатки.Склад);
        СоответствиеСкладов.Вставить(ВыборкаОстатки.Склад, Истина);
    КонецЕсли;

    Если СоответствиеНоменклатуры.Получить(ВыборкаОстатки.Номенклатура) = Неопределено Тогда
        МассивНоменклатуры.Добавить(ВыборкаОстатки.Номенклатура);
        СоответствиеНоменклатуры.Вставить(ВыборкаОстатки.Номенклатура, Истина);
    КонецЕсли;
КонецЦикла;

Этот вариант наименее предпочтителен для больших объемов данных из-за указанных выше недостатков. Однако, для отчетов с малым количеством строк (до нескольких тысяч), разница в производительности может быть неощутима.

Дополнительный вариант: Прямой запрос к виртуальной таблице для списков (без остатков)

Если вам нужны только списки складов и номенклатуры, а сами остатки не нужны, или они могут быть получены отдельным запросом без привязки к тем же условиям, вы можете использовать простой пакет запросов с РАЗЛИЧНЫЕ:

Запрос = Новый Запрос;
Запрос.Текст = 
"ВЫБРАТЬ РАЗЛИЧНЫЕ
    |    ТоварыНаСкладахОстатки.Номенклатура
    |ИЗ
    |    РегистрНакопления.ТоварыНаСкладах.Остатки(&НаМомент) КАК ТоварыНаСкладахОстатки
    |;
    |
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ РАЗЛИЧНЫЕ
    |    ТоварыНаСкладахОстатки.Склад
    |ИЗ
    |    РегистрНакопления.ТоварыНаСкладах.Остатки(&НаМомент) КАК ТоварыНаСкладахОстатки";

Запрос.УстановитьПараметр("НаМомент", Новый Граница(ТекущаяДата(), ВидГраницы.Исключая));

РезультатЗапроса = Запрос.ВыполнитьПакет();

ВыборкаНоменклатура = РезультатЗапроса[0].Выбрать();
ВыборкаСклады = РезультатЗапроса[1].Выбрать();

Этот вариант эффективен, если списки складов и номенклатуры требуются отдельно и не обязательно должны быть точно привязаны к тому же набору остатков, что и в основном отчете. Однако, если требуется строгое соответствие (т.е., "склады, по которым получены эти конкретные остатки"), то все равно следует использовать временные таблицы, как в первом варианте.

Общие рекомендации по оптимизации запросов в 1С

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

1. Приоритет отдавайте запросам: Всегда стремитесь максимально использовать возможности языка запросов 1С и СУБД для обработки и фильтрации данных. Запросы выполняются на стороне СУБД, которая, как правило, гораздо эффективнее обрабатывает большие объемы данных, чем код 1С. Избегайте "вытягивания" всех данных на сторону 1С для последующей фильтрации или агрегации программным способом.
2. Минимизируйте передачу данных: Старайтесь, чтобы запросы возвращали только те данные, которые действительно необходимы. Чем меньше данных передается между СУБД и сервером 1С, тем выше общая производительность запроса. Избегайте конструкции ВЫБРАТЬ *, если вам нужны лишь несколько полей.
3. Используйте индексы: Убедитесь, что поля, по которым выполняются соединения, фильтрация и сортировка, проиндексированы в базе данных. 1С автоматически создает некоторые индексы для регистров и справочников, но в некоторых сложных случаях может потребоваться ручная оптимизация или анализ плана выполнения запроса в СУБД для выявления отсутствующих индексов.
4. Стремитесь к читаемости кода: Помимо производительности, не забывайте о читаемости и поддерживаемости кода. Сложный, но очень быстрый запрос может быть менее предпочтительным, чем немного более медленный, но понятный и легко изменяемый. Временные таблицы и пакеты запросов часто улучшают читаемость, разбивая сложную логику на логические блоки.
5. Профилирование и тестирование: На больших базах данных единственным надежным способом определить самый быстрый вариант является профилирование. Используйте встроенные инструменты замера производительности 1С, а также инструменты СУБД (например, SQL Server Management Studio) для анализа планов выполнения запросов и оценки времени выполнения различных запросов на реальных данных. Небольшие объемы тестовых данных могут не показать истинной картины производительности на промышленных системах.

В контексте нашей задачи, если вам нужны все три набора данных (остатки, уникальные склады, уникальная номенклатура), и эти списки должны быть получены из именно этих остатков (а не из всего регистра), то вариант с временной таблицей, в которую сначала помещаются остатки, а затем из нее отдельными запросами (в том же пакете) выбираются РАЗЛИЧНЫЕ Склады и РАЗЛИЧНАЯ Номенклатура, является наиболее универсальным, читаемым и, как правило, производительным решением для средних и больших объемов данных. Мы рекомендуем использовать именно этот подход как наиболее надежный и масштабируемый.