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

← На главную

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

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

Выясним причину: как работает механизм связей наборов данных

Когда мы настраиваем связь двух наборов данных на соответствующей вкладке схемы компоновки данных и указываем передачу значений в параметры запроса-приемника, платформа по умолчанию использует логику построчного выполнения:

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

Если во вкладке Связи наборов данных включить флаг Список параметров, платформа попытается оптимизировать выполнение и передать значения пачками (в виде массивов до 1000 элементов). Однако при связи по двум зависимым полям (например, Номенклатура и Характеристика) СКД сформирует два независимых списка. В результате условие вида ГДЕ Номенклатура В (&МассивНоменклатур) И Характеристика В (&МассивХарактеристик) выполнит декартово перемножение условий и выберет избыточные комбинации данных.

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

Способ 1. Использование менеджера временных таблиц в СКД

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

Проанализируем алгоритм реализации:

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

Посмотрим на пример текста запроса в схеме СКД:


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

Теперь разберем программный код модуля отчета в процедуре ПриКомпоновкеРезультата:


Процедура ПриКомпоновкеРезультата(ДокументРезультат, ДанныеРасшифровки, СтандартнаяОбработка)
    
    СтандартнаяОбработка = Ложь;
    
    // 1. Создаем менеджер временных таблиц и помещаем туда исходную таблицу значений
    МенеджерВТ = Новый МенеджерВременныхТаблиц;
    
    ЗапросПомещения = Новый Запрос;
    ЗапросПомещения.МенеджерВременныхТаблиц = МенеджерВТ;
    ЗапросПомещения.Текст = 
        "ВЫБРАТЬ
        |   Шаблон.Товар КАК Товар,
        |   Шаблон.Характеристика КАК Характеристика
        |ПОМЕСТИТЬ ВТ_Шаблон
        |ИЗ
        |   &ТаблицаШаблона КАК Шаблон";
    ЗапросПомещения.УстановитьПараметр("ТаблицаШаблона", ШаблонОтчета);
    ЗапросПомещения.Выполнить();
    
    // 2. Инициализируем компоновку макета
    Настройки = КомпоновщикНастроек.ПолучитьНастройки();
    
    КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
    МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновкиДанных, Настройки, ДанныеРасшифровки);
    
    // 3. Передаем МенеджерВТ шестым параметром в ПроцессорКомпоновкиДанных
    ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
    ПроцессорКомпоновки.Инициализировать(
        МакетКомпоновки,
        , // ВнешниеНаборыДанных
        ДанныеРасшифровки,
        Истина, // Прямой вывод
        , // ПроверятьДоступностьПолей
        МенеджерВТ // Менеджер временных таблиц
    );
    
    // 4. Выводим результат в табличный документ
    ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВТабличныйДокумент;
    ПроцессорВывода.УстановитьДокумент(ДокументРезультат);
    ПроцессорВывода.Вывести(ПроцессорКомпоновки);
    
КонецПроцедуры

Преимущество метода: запрос к СУБД выполняется строго один раз за один пакет запросов, без декартовых произведений и без вызовов в цикле.

Способ 2. Подготовка итоговых данных через запрос в коде с выводом через набор «Объект»

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

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

Рассмотрим пример кода:


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

Способ 3. Передача массива ссылок в параметры СКД (для отборов по одному полю)

Если задача отбора не требует строгого контроля пар (Номенклатура, Характеристика), а фильтровать данные нужно только по списку товаров, можно избежать связывания двух наборов данных:

Из исходной таблицы значений выгружаем массив уникальных ссылок: МассивТоваров = ШаблонОтчета.ВыгрузитьКолонку("Товар").

Устанавливаем этот массив в параметр компоновки данных:


ПараметрОтбора = КомпоновщикНастроек.Настройки.ПараметрыДанных.Элементы.Найти("СписокНоменклатуры");
Если ПараметрОтбора <> Неопределено Тогда
    ПараметрОтбора.Значение = МассивТоваров;
    ПараметрОтбора.Использование = Истина;
КонецЕсли;

В единственном наборе данных Запрос используем стандартное условие в параметрах виртуальной таблицы: Номенклатура В (&СписокНоменклатуры).

Итоги и рекомендации

Использование связей наборов данных в СКД с передачей параметров полезно только в редких случаях (например, при соединении данных из совершенно разнородных источников, веб-сервисов или различных баз). При работе с таблицами значений и базами данных 1С следует избегать соединения наборов на клиенте/сервере через параметры связи и отдавать предпочтение работе с МенеджеромВременныхТаблиц или единому запросу к СУБД.