Как ускорить выполнение большого и сложного запроса в 1С? Разбор узких мест и методов оптимизации

← На главную

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

Шаг 1. Локализация проблемы с помощью трассировки пакета

Прежде чем приступать к оптимизации кода запроса, необходимо точно определить, какой именно из временных пакетов (запросов в пакете) вызывает наибольшую задержку. Оптимизировать весь гигантский запрос «вслепую» — неэффективно. Проанализируем ситуацию с помощью программной трассировки.

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

Рассмотрим код функции, которую можно разместить во внешней обработке или общем модуле для поиска «тормозящего» участка:


Функция ВыполнитьЗапросСТрассировкой(ЗапросИсх) Экспорт
    Результат = Неопределено;
    
    МВТ = Новый МенеджерВременныхТаблиц;
    
    Запрос = Новый Запрос;
    Запрос.МенеджерВременныхТаблиц = МВТ;
    Для каждого КлючИЗначение Из ЗапросИсх.Параметры Цикл
        Запрос.УстановитьПараметр(КлючИЗначение.Ключ, КлючИЗначение.Значение);
    КонецЦикла; 
    
    ТЗ = Новый ТаблицаЗначений;
    ТЗ.Колонки.Добавить("ТекстЗапроса");
    ТЗ.Колонки.Добавить("Время", Новый ОписаниеТипов("Число"));
    ТЗ.Колонки.Добавить("КоличествоЗаписей", Новый ОписаниеТипов("Число"));
    
    МассивТекстов = ОбщегоНазначения.РазложитьСтрокуВМассивПодстрок(ЗапросИсх.Текст, ";");
    Для каждого ТекстЗапроса Из МассивТекстов Цикл
        Если ПустаяСтрока(СокрЛП(ТекстЗапроса)) Тогда
            Продолжить;
        КонецЕсли;
        
        Запрос.Текст = ТекстЗапроса;
        ВремяНачала = ТекущаяУниверсальнаяДатаВМиллисекундах();
        Результат = Запрос.Выполнить();
        ВремяВыполнения = (ТекущаяУниверсальнаяДатаВМиллисекундах() - ВремяНачала) / 1000;
        
        НоваяСтрока = ТЗ.Добавить();
        НоваяСтрока.Время = ВремяВыполнения;
        НоваяСтрока.ТекстЗапроса = ТекстЗапроса;
        
        Попытка
            Выборка = Результат.Выбрать();
            НоваяСтрока.КоличествоЗаписей = Выборка.Количество();
        Исключение
            // Если это пакет создания ВТ без возврата результата наружу
            НоваяСтрока.КоличествоЗаписей = 0;
        КонецПопытки;
    КонецЦикла; 
    
    ТЗ.Сортировать("Время УБЫВ");
    Для каждого СтрокаТЗ Из ТЗ Цикл
        Сообщить("Время выполнения: " + СтрокаТЗ.Время + " сек. Записей: " + СтрокаТЗ.КоличествоЗаписей);
        Сообщить("Текст пакета:" + Символы.ПС + СтрокаТЗ.ТекстЗапроса);
        Сообщить("--------------------------------------------------");
    КонецЦикла; 
    
    МВТ.Закрыть();
    Возврат Результат;
КонецФункции

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

Шаг 2. Оптимизация работы с большими регистрами

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

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

Разберем, как исправить эту ошибку:

Создадим одну временную таблицу-буфер, в которую выберем все сырые данные из регистра РабочееВремяРаботниковОрганизаций за требуемый период времени и с отбором по нужной организации.
Проиндексируем эту временную таблицу по ключевым полям: Сотрудник, Период.
В последующих пакетах запроса будем обращаться уже к нашей локальной, быстрой временной таблице, а не к гигантской таблице СУБД. Это разгрузит дисковую подсистему и существенно ускорит работу планировщика СУБД.

Шаг 3. Избавление от неявных левых соединений (обращений через точку)

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

ВТСотрудникиОтработанноеВремя.Сотрудник.ДатаУвольнения

РабочееВремяРаботниковНеявки.Сотрудник.Физлицо

ВТПериодыИзмененияДанныхКонецПериода.Сотрудник.Код

Каждое подобное обращение на уровне СУБД транслируется во вложенный оператор LEFT JOIN с таблицей справочника (в данном случае Справочник.Сотрудники и далее Справочник.ФизическиеЛица). Когда таких обращений десятки в разных временных таблицах, СУБД строит неоптимальные планы выполнения, многократно перечитывая одни и те же данные.

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


ВЫБРАТЬ
    Работники.Сотрудник КАК Сотрудник,
    Работники.Сотрудник.Код КАК ТабНомер,
    Работники.Сотрудник.Физлицо КАК Физлицо,
    Работники.Сотрудник.ДатаУвольнения КАК ДатаУвольнения
ПОМЕСТИТЬ ВТСотрудникиПлоская
ИЗ
    ...

Во всех последующих таблицах пакета будем использовать исключительно поля ТабНомер, Физлицо и ДатаУвольнения напрямую из нашей плоской таблицы ВТСотрудникиПлоская. Это полностью исключит дублирование неявных левых соединений.

Шаг 4. Оптимизация среза последних на каждую дату (неравенства в соединениях)

Проанализируем пакет ВТРезультатПериоды. Здесь выполняется соединение по условию:

ПО ВТРезультат.Сотрудник = РаботникиОрганизаций.Сотрудник И ВТРезультат.Период >= РаботникиОрганизаций.Period

с последующим использованием функции группировки МАКСИМУМ.

Соединение таблиц по неравенству (операторы >=, <=, >, <) на больших объемах данных заставляет СУБД использовать алгоритм Nested Loops (вложенные циклы). СУБД начинает построчно сопоставлять огромные массивы информации, что мгновенно просаживает производительность.

Как решить эту задачу оптимально?

Если задача решается в отчете, настоятельно рекомендуем вынести расчет кадровой истории в **Систему компоновки данных (СКД)**. Используйте механизм связей наборов данных, где в качестве параметров связи передаются сотрудник и дата. СКД выполняет этот расчет на уровне платформы оптимизированными порционными запросами, что работает в десятки раз быстрее ручных соединений по неравенству.
Если запрос выполняется строго в коде, рекомендуется предварительно собрать таблицу всех уникальных кадровых событий сотрудников с рассчитанными интервалами действия (поля ДатаНачала и ДатаОкончания). После этого соединение рабочих часов с историческими интервалами выполняется по быстрому условию равенства или строгому вхождению в готовый закрытый интервал МЕЖДУ, что позволяет СУБД использовать стандартный Hash Match вместо тяжелого Nested Loops.

Шаг 5. Замена полного соединения (FULL OUTER JOIN) на ОБЪЕДИНИТЬ ВСЕ

В пакете ВТОтработанноеВремя мы видим использование ПОЛНОГО СОЕДИНЕНИЯ двух временных таблиц:


ИЗ
    ВТОтработаноЧасов КАК ВТОтработаноЧасов
    ПОЛНОЕ СОЕДИНЕНИЕ ВТЧасовНеявкиПоПК КАК ВТЧасовНеявкиПоПК
    ПО ВТОтработаноЧасов.Сотрудник = ВТЧасовНеявкиПоПК.Сотрудник
        И ВТОтработаноЧасов.Период = ВТЧасовНеявкиПоПК.Период

Оператор FULL JOIN крайне неэффективно обрабатывается оптимизаторами СУБД (особенно под управлением PostgreSQL). Он требует значительного объема оперативной памяти для построения хэш-таблиц по обеим сторонам соединения, а при нехватке памяти сбрасывает временные данные на диск в базу tempdb.

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


ВЫБРАТЬ
    Т.Период КАК Период,
    Т.Сотрудник КАК Сотрудник,
    СУММА(Т.ОтработаноЧасов) КАК ОтработаноЧасов,
    СУММА(Т.ЧасовНеявки) КАК ЧасовНеявки
ПОМЕСТИТЬ ВТОтработанноеВремяОптимизированная
ИЗ
    (ВЫБРАТЬ
        Явка.Период КАК Период,
        Явка.Сотрудник КАК Сотрудник,
        Явка.ЧасовЯвки КАК ОтработаноЧасов,
        0 КАК ЧасовНеявки
    ИЗ
        ВТОтработаноЧасов КАК Явка
    
    ОБЪЕДИНИТЬ ВСЕ
    
    ВЫБРАТЬ
        Неявка.Период,
        Неявка.Сотрудник,
        0,
        Неявка.ЧасовНевки
    ИЗ
        ВТЧасовНеявкиПоПК КАК Неявка) КАК Т
СГРУППИРОВАТЬ ПО
    Т.Период,
    Т.Сотрудник

Этот прием позволяет СУБД выполнить быстрое последовательное сканирование (Scan) двух временных таблиц и объединить их результаты в памяти без выполнения тяжелой и непредсказуемой операции слияния по двум ключам.

Шаг 6. Оптимизация параметров виртуальных таблиц

Обратим внимание на пакет ВТСведенияОбИнвлидности, где в параметрах виртуальной таблицы используется подзапрос:


РегистрСведений.СведенияОбИнвалидностиФизлиц.СрезПоследних(
    &КонецПериода,
    Физлицо В (ВЫБРАТЬ Т.ФизЛицо ИЗ ВТСотрудникиПодразделения КАК Т)
)

Использование вложенных запросов в параметрах отбора виртуальных таблиц — это серьезная архитектурная ошибка. Оптимизатор СУБД в этом случае часто не может построить корректный план применения индексов и переходит к полному сканированию физической таблицы регистра сведений (Index Scan или Table Scan).

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


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

Такой подход гарантирует, что СУБД сначала выберет актуальные записи среза последних, а затем мгновенно сопоставит их по индексу с плоским списком сотрудников.

Шаг 7. Разумное использование индексации временных таблиц

В исходном запросе практически каждый промежуточный пакет завершается инструкцией ИНДЕКСИРОВАТЬ ПО.... Важно помнить, что создание индексов в СУБД — это платная операция, требующая процессорного времени и дисковых операций записи.

Рекомендация по индексации:

Если временная таблица гарантированно содержит мало записей (менее 100-200 строк), индексировать ее не имеет никакого смысла. СУБД быстрее прочитает такую таблицу целиком (сканирование в памяти), чем будет создавать и использовать индекс.
Индексировать временные таблицы нужно только в том случае, если они содержат тысячи записей и далее будут участвовать в операциях соединения (JOIN) с другими таблицами по индексируемым полям.
Всегда индексируйте только те поля, по которым будет происходить связь в секции ПО последующих пакетов. Индексировать поля, используемые только в группировках или отборах ГДЕ, чаще всего избыточно.

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