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

← На главную

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

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

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

Первое, на что стоит обратить внимание при использовании нескольких наборов данных, — это то, как именно СКД передает данные из одного набора в другой. По умолчанию, если связь настроена просто по полям, СКД может выполнять отдельный запрос к базе данных для каждой строки первого набора. Если у вас 10 000 документов в первом наборе, вы получите 10 000 запросов ко второму, что недопустимо.

Проанализируем правильный подход к настройке связи:

В подчиненном наборе данных (где мы ищем подчиненные документы) необходимо объявить параметр, например &МассивОснований.
В тексте запроса подчиненного набора данных нужно прописать условие: ГДЕ ДокументОснование В (&МассивОснований).
На закладке "Связи наборов данных" в колонке "Параметр" выберем наш параметр МассивОснований.
Важный момент: необходимо установить галочку "Список параметров".

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

Проблема использования функций в условиях (SARGability)

Часто разработчики допускают ошибку, используя функции преобразования типов или дат в секции ГДЕ. Рассмотрим пример, когда мы хотим ограничить выборку подчиненных документов определенным периодом относительно даты основного документа. Посмотрим на типичную ошибку:

ГДЕ ДОБАВИТЬКДАТЕ(Реализация.Дата, МЕСЯЦ, 12) >= &ТекущаяДата

В данном случае условие становится не-SARGable (Search Argumentable). Это означает, что SQL-сервер не сможет использовать индекс по полю Дата, так как ему нужно сначала вычислить функцию для каждой записи в таблице. Выясним причину замедления: при объеме базы в полтерабайта сканирование всей таблицы (Index Scan или Table Scan) вместо поиска по индексу (Index Seek) занимает минуты.

Решение: всегда преобразовывайте правую часть выражения (параметр), оставляя поле таблицы "чистым":


// Вместо вычислений в запросе, передаем уже готовый параметр
ГДЕ Реализация.Дата >= &НачалоПериодаПоиска

Динамическое формирование запроса через ПриКомпоновкеРезультата

Если пользователю нужна возможность выбирать типы подчиненных документов (галочками на форме), то создание статического запроса с множеством ЛЕВОЕ СОЕДИНЕНИЕ или ОБЪЕДИНИТЬ ВСЕ крайне неэффективно. СУБД будет вынуждена обрабатывать все таблицы, даже если данные из них не требуются.

Рассмотрим вариант программной модификации схемы в модуле объекта отчета:


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

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

Использование внешних наборов данных (Таблиц значений)

Если связи на уровне SQL всё равно работают медленно из-за отсутствия индексов, которые вы не можете добавить (например, в типовой конфигурации без ее изменения), можно применить стратегию "разделяй и властвуй". Мы можем выполнить выборку в два этапа:

Шаг 1: Программным запросом выбираем все необходимые ссылки основных документов и помещаем их в ТаблицуЗначений.

Шаг 2: На основании этой таблицы формируем второй запрос, который выбирает подчиненные документы. Поскольку мы работаем в коде, мы можем построить запрос максимально эффективно.

Шаг 3: Передаем обе таблицы в СКД как внешние наборы данных.


// В модуле объекта
ВнешниеНаборыДанных = Новый Структура;
ВнешниеНаборыДанных.Вставить("ТЗ_Основные", ПолучитьТаблицуОсновныхДокументов());
ВнешниеНаборыДанных.Вставить("ТЗ_Подчиненные", ПолучитьТаблицуПодчиненных(ВнешниеНаборыДанных.ТЗ_Основные));

ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, ВнешниеНаборыДанных);

В этом случае связь наборов данных будет происходить в оперативной памяти сервера 1С, что для объемов в несколько десятков тысяч строк будет работать значительно быстрее, чем неоптимизированный Join на стороне SQL по неиндексированному составному полю.

Работа с временными таблицами в пакете запросов

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

Пример структуры пакета запросов:


// Запрос 1: Выбираем основания во временную таблицу
ВЫБРАТЬ
    Реализация.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_Основания
ИЗ
    Документ.РеализацияТоваровУслуг КАК Реализация
ГДЕ
    Реализация.Дата МЕЖДУ &НачалоПериода И &КонецПериода
;
////////////////////////////////////////////////////////////////////////////////
// Запрос 2: Индексируем для ускорения последующих соединений
ИНДЕКСИРОВАТЬ ПО
    Ссылка
;
////////////////////////////////////////////////////////////////////////////////
// Запрос 3: Выбираем подчиненные документы через соединение с ВТ
ВЫБРАТЬ
    ТТН.Ссылка КАК ПодчиненныйДокумент,
    ВТ_Основания.Ссылка КАК Основание
ИЗ
    ВТ_Основания КАК ВТ_Основания
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ТТН КАК ТТН
        ПО ВТ_Основания.Ссылка = ТТН.ДокументОснование

Преимущество этого метода: Временная таблица ВТ_Основания создается один раз в рамках сеанса выполнения запроса. SQL-серверу гораздо проще выполнить JOIN с физической временной таблицей, которая имеет индекс, чем пытаться оптимизировать сложный запрос с множеством вложенных условий.

Использование Критериев отбора

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

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