Как исправить критические ошибки в запросе 1С при получении остатков? Разбор типичных ошибок оптимизации

← На главную

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

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


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

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

Ошибка 1. Прямое соединение виртуальных таблиц остатков

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

Выясним причину этой проблемы. Виртуальная таблица в 1С не существует на уровне физических таблиц базы данных СУБД (MS SQL Server, PostgreSQL). В реальности платформа генерирует сложный вложенный подзапрос к таблице итогов и таблице движений регистра, рассчитывая остатки «на лету». Когда мы выполняем конструкцию ВНУТРЕННЕЕ СОЕДИНЕНИЕ для двух таких виртуальных таблиц, планировщик запросов СУБД не имеет актуальной статистики по результатам этих вложенных выборок. В итоге оптимизатор СУБД строит план выполнения запроса «наугад», что часто приводит к выбору неэффективных алгоритмов (например, полного сканирования таблиц Table Scan вместо поиска по индексу) и зависанию сервера.

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

Ошибка 2. Отборы в секции ГДЕ вместо параметров виртуальной таблицы

В анализируемом запросе отборы по складу, организации и номенклатуре наложены в самом конце в секции ГДЕ:


ГДЕ
    ТоварыНаСкладахОстатки.Склад = &Склад
    И ТоварыОрганизацийОстатки.Организация = &Организация
    И ТоварыНаСкладахОстатки.Номенклатура = &Номенклатура

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

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

Ошибка 3. Использование конструкции «.Ссылка» (Разыменование полей)

Обратите внимание на условие связи в запросе:


ПО ТоварыНаСкладахОстатки.Номенклатура = ТоварыОрганизацийОстатки.Номенклатура.Ссылка

Поле Номенклатура в обоих регистрах уже имеет ссылочный тип (например, СправочникСсылка.Номенклатура). Конструкция Номенклатура.Ссылка заставляет платформу выполнять неявное левое соединение (Implicit Join) таблицы остатков с основной таблицей справочника номенклатуры только для того, чтобы проверить, существует ли эта ссылка. Это приводит к лишней нагрузке на СУБД и деградации индексов.

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

Решение: Сравнивать ссылочные поля необходимо напрямую, без использования разыменования: ПО ТоварыНаСкладахОстатки.Номенклатура = ТоварыОрганизацийОстатки.Номенклатура.

Ошибка 4. Несовпадение структуры измерений регистров при соединении

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

Ошибка 5. Логическая ошибка: СОЕДИНЕНИЕ количественных показателей вместо ОБЪЕДИНЕНИЯ

Использование любых соединений (ВНУТРЕННЕЕ, ЛЕВОЕ или ПОЛНОЕ) при сопоставлении количественных остатков из разных регистров — это концептуальная архитектурная ошибка. Рассмотрим пример:

Если по номенклатуре «Товар 1» на складе числится 10 штук, а в регистре организаций данный товар распределен по двум разным организациям (например, ООО «Ромашка» — 6 шт. и ООО «Василек» — 4 шт.), то при соединении по номенклатуре складской остаток в 10 штук продублируется в результате запроса дважды (для каждой организации). Мы получим искаженные, задвоенные данные по складу.
Если товара нет на складе, но он числится по организации, то при внутреннем соединении (INNER JOIN) эта строка вообще исчезнет из результирующей таблицы, и пользователь не увидит расхождений учета.

Решение: Единственно верным шаблоном для сопоставления количественных остатков из разных источников является использование конструкции ОБЪЕДИНИТЬ ВСЕ с последующей группировкой данных.

---

Разберем правильное решение по шагам

Давайте перепишем наш запрос в соответствии со всеми стандартами разработки 1С. Мы будем использовать пакетный запрос, временные таблицы, параметры виртуальных таблиц и механизм объединения.

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

Шаг 2. Получаем остатки по организациям во временную таблицу
Аналогично передаем параметры организации и номенклатуры в параметры виртуальной таблицы ТоварыОрганизаций. Складские поля заполняем нулями.

Шаг 3. Объединяем результаты и группируем по ключевым полям
Объединяем полученные данные с помощью ОБЪЕДИНИТЬ ВСЕ и производим финальное суммирование ресурсов в разрезе номенклатуры.

Посмотрим на готовый оптимизированный вариант запроса:


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

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

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    МАКСИМУМ(ВТ_Остатки.Склад) КАК Склад,
    ВТ_Остатки.Номенклатура КАК Номенклатура,
    СУММА(ВТ_Остатки.СкладОстаток) КАК СкладОстаток,
    СУММА(ВТ_Остатки.ОрганизацияОстаток) КАК ОрганизацияОстаток
ИЗ
    ВТ_Остатки КАК ВТ_Остатки
ГРУППИРОВАТЬ ПО
    ВТ_Остатки.Номенклатура

Давайте проанализируем преимущества разработанного решения:

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

Использование правильных паттернов разработки запросов — залог быстродействия и стабильности любой информационной системы на платформе «1С:Предприятие 8».