Как правильно и быстро объединить данные из двух регистров накопления в 1С без потери производительности?

← На главную

При разработке отчетов и обработок в среде 1С:Предприятие одной из самых частых задач является получение и сопоставление данных из нескольких источников. Типичный пример — получение актуальных остатков номенклатуры (в нашем случае — натяжных потолков) и связь этих данных с их текущим состоянием или статусом из другого регистра накопления. Однако неверный подход к проектированию такого запроса может превратить простую операцию получения данных в тяжелый процесс, занимающий от 5 до 10 секунд даже на небольших базах данных. На производительных или высоконагруженных системах это недопустимо.

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

Анализ исходной проблемы и неоптимального запроса

Рассмотрим ситуацию, с которой столкнулся автор темы. Изначально задача состояла в том, чтобы выбрать остатки из регистра Потолки по определенному отбору (статус «Заказ») и соединить их с актуальным состоянием этих же потолков из регистра СостояниеПотолков по текстовому коду изделия. Первоначальный вариант запроса выглядел следующим образом:


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

Этот запрос выполнялся около 5–10 секунд. Выясним причину столь медленной работы. Главная архитектурная ошибка здесь — прямое левое соединение двух виртуальных таблиц остатков.

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

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

Попытки обойти эту проблему «напрямую» часто приводят разработчиков к другим критическим ошибкам.

1. Запросы в цикле. В поисках решения автор темы попробовал получить данные из одного регистра объектным методом и организовать перебор строк с получением остатков второго регистра в цикле:


Для каждого СтрРЗ Из РегПотолков Цикл
    ТРег = РегистрыНакопления.СостояниеПотолков.Остатки(, Новый Структура("ШтрихКод", СтрРЗ.ШтрихКод));
    Для Каждого Рег из ТРег Цикл
        СтрРЗ.Состояние = Рег.Состояние;
    КонецЦикла;
КонецЦикла;

Хотя на маленьком объеме данных это может работать быстро, данный подход является одним из злейших антипаттернов в разработке 1С. Каждый вызов метода .Остатки() внутри цикла — это полноценное отдельное обращение к СУБД. Если в первом списке окажется 1000 потолков, система выполнит 1000 отдельных SQL-запросов. Это порождает огромные накладные расходы на сетевой трафик, авторизацию запросов сервером СУБД и компиляцию планов выполнения.

2. Соединение подзапросов (вложенные запросы). Другая попытка (представленная в Сообщении 4) — завернуть виртуальные таблицы во вложенные подзапросы в секции ИЗ:


ИЗ
    (ВЫБРАТЬ ... ИЗ РегистрНакопления.Потолки.Остатки(...) ) КАК РегистрПотолков
    ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ ... ИЗ РегистрНакопления.СостояниеПотолков.Остатки(...) ) КАК РегистрСостоянияПотолков

Это решение сократило время выполнения до 1 секунды, однако, как справедливо замечено в Сообщении 10, это «те же яйца, вид сбоку». Для оптимизатора СУБД вложенный запрос ничем не отличается от прямой виртуальной таблицы — он по-прежнему не имеет по нему физической статистики в базе данных, и при увеличении объема записей этот запрос неизбежно начнет тормозить.

Правильное решение: Использование временных таблиц и индексации

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

При использовании временных таблиц платформа 1С создает реальные физические таблицы в СУБД (например, в базе данных tempdb для MS SQL Server). СУБД легко считывает объем записей в них и строит идеальный план для финального соединения.

Разберем оптимальный вариант запроса по шагам:

Шаг 1. Получаем остатки потолков и помещаем их во временную таблицу. При этом мы сразу накладываем нужные отборы в параметрах виртуальной таблицы и обязательно индексируем поле соединения (в данном случае Код).

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

Шаг 3. Соединяем две временные таблицы в финальном пакете запроса.

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


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

Запрос.УстановитьПараметр("СтатусЗаказ", "Заказ");
Запрос.УстановитьПараметр("Состояние", Статус);

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

Важные технологические аспекты и тонкости оптимизации

Чтобы окончательно разобраться в механике работы этого запроса и уметь применять эти подходы в любых других задачах, проанализируем ключевые технологические правила:

Когда индексирование действительно необходимо?
Индексирование временных таблиц с помощью ключевого слова ИНДЕКСИРОВАТЬ ПО — это мощный инструмент, но он требует дополнительных ресурсов на запись (СУБД тратит время на построение индекса в момент заполнения таблицы). Создавать индексы имеет смысл только тогда, когда во временную таблицу попадает более 100–1000 записей. Если в вашей выборке гарантированно будет 10–20 строк, индексирование может даже слегка замедлить выполнение операции. В нашем случае, поскольку речь идет обо всех активных заказах потолков, объем данных явно превышает эту планку, и индексирование строго обязательно.
Избыточность группировки (СГРУППИРОВАТЬ ПО)
Обратите внимание, что в исходном запросе автора присутствовала тяжелая секция СГРУППИРОВАТЬ ПО со списком из полутора десятков полей. Виртуальная таблица остатков регистра накопления .Остатки() уже возвращает сгруппированные и просуммированные данные в разрезе своих измерений. Если мы выбираем данные без дублирования ключевых полей и связываем их по уникальному коду один-к-одному, повторная группировка во внешнем запросе абсолютно бессмысленна и лишь тратит процессорное время сервера СУБД. В оптимизированном запросе мы полностью избавились от этой секции.
Тип связующего поля (Строка vs Ссылка)
В коде автора связь таблиц происходит по текстовому полю Код или Штрихкод. На уровне СУБД сравнение строк происходит значительно медленнее, чем сравнение уникальных числовых идентификаторов (GUID). Если конфигурация позволяет, всегда старайтесь связывать таблицы по ссылочным типам данных (например, по ссылке на справочник номенклатуры или изделий СправочникСсылка.Потолки). Это позволит СУБД выполнять соединение по стандартным системным индексам таблиц с максимальной скоростью.
Параметры виртуальных таблиц вместо секции ГДЕ
Любые отборы (например, по статусу или состоянию) должны передаваться строго в параметры виртуальной таблицы (первые пакеты нашего запроса). Если наложить отбор через конструкцию ГДЕ во внешнем запросе, СУБД сначала прочитает из базы данных абсолютно все остатки по всем существующим потолкам, и только потом отбросит ненужные. Передача параметров напрямую в виртуальную таблицу заставляет СУБД выбирать только те данные, которые соответствуют условию, минимизируя дисковое чтение.
Использование функции ЕСТЬNULL при левом соединении
Поскольку мы используем ЛЕВОЕ СОЕДИНЕНИЕ, для некоторых записей из таблицы ВТ_Потолки может не найтись соответствия в таблице ВТ_Состояния. В этом случае поле Состояние вернет значение NULL. Чтобы избежать непредвиденных ошибок при последующей обработке этой таблицы в коде 1С, мы обернули выбор поля в функцию ЕСТЬNULL, которая заменяет неопределенное значение на пустую ссылку соответствующего перечисления.
Получение актуальных остатков (без указания периода)
Обратите внимание на вызов РегистрНакопления.Потолки.Остатки(, ...) с пустой первой запятой. Отсутствие параметра даты (периода) — это самый эффективный способ получения текущих остатков в 1С. Платформа обращается напрямую к таблице текущих итогов, минуя расчеты по таблицам движений, что гарантирует моментальный ответ СУБД.

Применение этой методики (разделение на временные таблицы с индексацией связующих полей) позволило сократить время выполнения запроса с 5–10 секунд до сотых долей секунды. Этот подход является золотым стандартом оптимизации любых запросов к виртуальным таблицам в 1С.