При разработке сложных отчетов в системе 1С:ERP или 1С:Комплексная автоматизация программисты часто сталкиваются с ситуацией, когда запрос, идеально работающий на тестовой базе, начинает «зависать» или выдавать результат крайне медленно на реальных данных. Одной из наиболее распространенных причин такой деградации производительности является некорректная работа с полями составного типа в условиях соединения или при обращении к их реквизитам. Разберем на конкретном примере, как правильно оптимизировать такие запросы.
Рассмотрим типичную ситуацию: нам необходимо связать данные регистра накопления СебестоимостьТоваров.ОстаткиИОбороты и данные из временной таблицы, сформированной на основе табличной части ПрочиеРасходы документа АвансовыйОтчет. Проблема возникает в момент соединения по полям Регистратор (у регистра) и АналитикаРасходов (в нашей временной таблице), так как оба этих поля имеют составной тип данных.
Проанализируем типичную ошибку. Часто разработчики пытаются использовать функцию ВЫРАЗИТЬ везде, где встречается составной тип, полагая, что это поможет оптимизатору СУБД. Например:
ВЫБРАТЬ
ДД.Регистратор,
ВТ.АвансовыйОтчетАналитикаРасходов
ИЗ
РегистрНакопления.СебестоимостьТоваров.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, , , ) КАК ДД
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Данные КАК ВТ
ПО (ВЫРАЗИТЬ(ДД.Регистратор КАК Документ.ЭтапПроизводства2_2)).Распоряжение = (ВЫРАЗИТЬ(ВТ.АвансовыйОтчетАналитикаРасходов КАК Документ.ЗаказНаПроизводство2_2))
Такой подход может не только не ускорить запрос, но и сделать его неработоспособным на больших объемах данных. Выясним, почему это происходит и как работает механизм составных типов.
Поле составного типа в базе данных SQL представляется не одной колонкой, а несколькими. Обычно это поле для хранения типа (идентификатор метаданных) и поле для хранения уникального идентификатора (GUID) самой ссылки. Когда мы обращаемся к реквизиту через точку от такого поля (например, Регистратор.Дата), платформа 1С генерирует SQL-код, который выполняет ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN) со всеми физическими таблицами документов, которые входят в этот составной тип.
Если в составной тип Регистратор входит 50 различных видов документов, система сделает 50 неявных левых соединений. Это колоссальная нагрузка на планировщик запросов СУБД, которая приводит к экспоненциальному росту сложности плана выполнения запроса.
Функция ВЫРАЗИТЬ предназначена для того, чтобы указать системе: «Я точно знаю, что в данном случае тип значения — вот такой». Рассмотрим ключевые правила ее использования для оптимизации:
Распоряжение), всегда используйте ВЫРАЗИТЬ. Это ограничит количество соединений в SQL только одной нужной таблицей.
Вместо ДД.Регистратор.Распоряжение пишите ВЫРАЗИТЬ(ДД.Регистратор КАК Документ.ЭтапПроизводства2_2).Распоряжение.
ВЫРАЗИТЬ может быть лишним. Платформа 1С достаточно эффективно сравнивает составные ссылки по их внутренним идентификаторам типов и значений.ВЫРАЗИТЬ, функция вернет NULL. Это можно использовать как фильтр, но нужно учитывать это в логике соединения (LEFT JOIN vs INNER JOIN).Проанализируем ситуацию из сообщения форума, где отчет стал «выстреливать» за секунды после правки запроса. Основная ошибка была в избыточном приведении типа справа от знака равенства, где это не требовалось для доступа к вложенным полям.
Шаг 1. Оптимизация обращения к полям.
Если мы связываем ЭтапПроизводства через его реквизит Распоряжение, нам действительно нужно «выразить» регистратор, чтобы добраться до поля Распоряжение без создания десятков лишних соединений.
Шаг 2. Оптимизация правой части соединения.
Если во временной таблице ВТ.АвансовыйОтчетАналитикаРасходов уже лежит ссылка нужного типа (например, ЗаказНаПроизводство), то дополнительное применение ВЫРАЗИТЬ к ней в секции ПО может сбивать оптимизатор SQL. Достаточно того, чтобы типы данных были сопоставимы.
Шаг 3. Использование временных таблиц.
Как отмечено в обсуждении, «делать соединения с виртуальными таблицами — моветон». Рассмотрим правильную структуру запроса:
Пример оптимизированного кода:
// Сначала подготавливаем данные регистра во временную таблицу
ВЫБРАТЬ
ВЫРАЗИТЬ(ДД.Регистратор КАК Документ.ЭтапПроизводства2_2).Распоряжение КАК СсылкаДляСвязи,
ДД.СтоимостьОборот КАК Стоимость
ПОМЕСТИТЬ ВТ_Себестоимость
ИЗ
РегистрНакопления.СебестоимостьТоваров.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, , , ) КАК ДД
ГДЕ
ДД.Регистратор ССЫЛКА Документ.ЭтапПроизводства2_2;
ИНДЕКСИРОВАТЬ ПО
СсылкаДляСвязи;
;
// Затем выполняем финальное соединение
ВЫБРАТЬ
ВТ_Затраты.АвансовыйОтчетАналитикаРасходов,
ВТ_Себестоимость.Стоимость
ИЗ
ВТ_ДанныеАвансовогоОтчета КАК ВТ_Затраты
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Себестоимость КАК ВТ_Себестоимость
ПО ВТ_Затраты.АвансовыйОтчетАналитикаРасходов = ВТ_Себестоимость.СсылкаДляСвязи
Проанализируем еще несколько важных моментов, которые помогут избежать медленной работы отчетов:
Избегайте условий ИЛИ в соединениях. Если вам нужно связать данные по разным типам документов, лучше использовать ОБЪЕДИНИТЬ ВСЕ (UNION ALL) для нескольких запросов, в каждом из которых будет свое жесткое приведение типа через ВЫРАЗИТЬ. Это позволит СУБД построить простой и эффективный план для каждой части объединения.
Параметры виртуальных таблиц. Всегда старайтесь максимально фильтровать данные внутри параметров виртуальной таблицы (в первых скобках), а не в секции ГДЕ. Это критично для регистров накопления, так как расчет итогов «на лету» по всей таблице — крайне ресурсоемкая операция.
Длина строк. Если вы используете ВЫРАЗИТЬ для строковых полей, помните, что приведение неограниченной строки к фиксированной длине (например, ВЫРАЗИТЬ(Поле КАК СТРОКА(200))) позволяет использовать такие поля в группировках и условиях соединения, что невозможно для строк неограниченной длины.
Таким образом, использование ВЫРАЗИТЬ — это мощный инструмент управления тем, как 1С формирует SQL-запрос. Применяйте его осознанно: там, где нужно «пробиться» к реквизитам через точку, и избегайте избыточности там, где выполняется простое сравнение ссылок одного типа.