Как в СКД передать параметры в подчиненную связь наборов данных и ускорить работу отчета?

Часто при разработке отчетов на базе Системы Компоновки Данных (СКД) возникает необходимость связать два набора данных: основной набор-объект (например, Таблицу Значений с заказами) и подчиненный набор-запрос (например, срез последних регистра сведений со статусами). Проанализируем ситуацию: в основной таблице может быть всего пара строк, а в регистре — сотни тысяч записей. Если связь настроена некорректно, отчет работает крайне медленно, поскольку фильтры на срез не накладываются должным образом или выполняются запросы в цикле.

Разберем по шагам, почему возникает эта проблема и как ее правильно решить, чтобы добиться оптимальной производительности.

Почему связь наборов работает медленно?

Выясним причину падения производительности. Существуют серьезные ограничения платформы при связывании разных типов наборов:

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

Решение 1: Передача списка в параметры связи (с обходом внутреннего соединения)

Рассмотрим первый вариант решения проблемы, если отказаться от использования двух наборов нельзя. Необходимо передавать параметр непосредственно в настройках связи наборов данных, а не в параметрах самого подчиненного набора. Передача списка (массива) позволит избежать выполнения запроса для каждой отдельной строки главного набора.

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

Посмотрим на пример кода для подчиненного набора-запроса:

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

В данном запросе мы явно соединяем таблицу документа со срезом последних регистра сведений, накладывая условие В(&Заказ). Это гарантирует, что даже если в регистре нет записей, запрос вернет строку с пустой ссылкой статуса (благодаря функции ЕСТЬNULL), и СКД корректно обработает эти данные без потери строк главного набора.

Решение 2: Использование Менеджера Временных Таблиц (МВТ)

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

1. Перед выполнением компоновки программно поместите вашу исходную Таблицу Значений во временную таблицу базы данных с помощью объекта Запрос.
2. Получите МенеджерВременныхТаблиц из этого запроса.
3. Передайте менеджер в ПроцессорКомпоновкиДанных при его инициализации.
4. В самой схеме СКД создайте единственный «Набор данных - запрос», который будет читать созданную временную таблицу и соединять её со срезом регистра сведений стандартным ЛЕВЫМ СОЕДИНЕНИЕМ.

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

Дополнительные способы оптимизации

Рассмотрим еще несколько практических советов по оптимизации, если вы все-таки используете связи наборов данных:

• Используйте «Условие связи»: На вкладке СКД «Связи наборов данных» есть колонка «Условие связи». Если вы можете заранее определить, что для строки (например, при пустой ссылке) искать данные в регистре бессмысленно, пропишите это условие. СКД просто пропустит обращение к подчиненному набору для таких строк.
• Минимизируйте поля: В подчиненном наборе выбирайте только те поля, которые действительно нужны для вывода и ключи для связи.
• Проверьте индексы: Убедитесь, что измерение регистра сведений, по которому происходит связь (в нашем примере — Заказ), проиндексировано. Иначе каждый запрос СКД будет приводить к полному сканированию таблицы регистра (Table Scan), что "убьет" производительность.