Как оптимизировать медленно формирующийся отчет в 1С СКД?

← На главную

Мы сталкиваемся с распространенной проблемой: отчет на Системе Компоновки Данных (СКД) в 1С, который предназначен для анализа расходов на транспортные средства, формируется очень долго. В частности, если рассматривать данные за квартал, процесс может затянуться надолго. Отчет состоит из шести разных запросов, связанных по регистратору или транспортному средству. Вместе с вами разберем, как подойти к оптимизации такого отчета, выявим типичные ошибки и предложим эффективные решения.

Диагностика производительности запроса и СКД

Прежде всего, давайте выясним, где именно кроется "бутылочное горлышко" производительности. Медленная работа отчета СКД может быть вызвана как неоптимальным запросом к базе данных, так и сложной обработкой данных самой СКД.

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

Основные причины замедления отчета в СКД

Мы часто обнаруживаем, что медленная работа отчета обусловлена одной из следующих причин:

"Кривой" запрос: Запрос, который либо выполняет избыточную работу, либо неоптимально использует индексы, либо содержит ресурсоемкие операции.
Большой объем выводимых данных: Если отчет пытается вывести слишком много детализированных строк без адекватных группировок, это неизбежно приведет к замедлению, даже если сам запрос выполняется быстро. СКД приходится обрабатывать и форматировать каждый элемент.
Множество отдельных запросов: Наш случай, когда отчет состоит из шести разных запросов, связанных по регистратору (или транспортному средству), является ярким примером проектной ошибки. Хотя СКД позволяет объединять несколько наборов данных, обычно это менее эффективно, чем один хорошо спроектированный комплексный запрос. Каждый отдельный запрос может выполняться относительно быстро, но накладные расходы на их выполнение и последующее связывание в СКД могут быть значительными.

Объединение запросов в единую структуру

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

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

Оптимизация условий в запросах

Рассмотрим пример одного из наших запросов (сообщение 9) и выясним, какие конкретные шаги по оптимизации мы можем предпринять.


ВЫБРАТЬ
    Пробег.Регистратор КАК ПутевойЛист,
    Пробег.ТС КАК ТС,
    Пробег.Количество КАК Пробег,
    0 КАК ВесГруза,
    Пробег.Организация КАК Организация,
    0 КАК КоличествоРасход,
    0 КАК СтоимостьРасход
ИЗ
    РегистрНакопления.уатВыработкаТС КАК Пробег
ГДЕ
    Пробег.ПараметрВыработки = &Пробег
    И Пробег.Период МЕЖДУ &НачалоПериода И &КонецПериода
    И Пробег.ТС.Наименование <> &НаименованиеТС
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
    ВесГруза.Регистратор,
    ВесГруза.ТС,
    0,
    ВесГруза.Количество,
    ВесГруза.Организация,
    0,
    0
ИЗ
    РегистрНакопления.уатВыработкаТС КАК ВесГруза
ГДЕ
    ВесГруза.ПараметрВыработки = &ВесГруза
    И ВесГруза.Период МЕЖДУ &НачалоПериода И &конецПериода
    И ВесГруза.ТС.Наименование <> &НаименованиеТС
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
    уатОстаткиГСМнаТС.Регистратор,
    уатОстаткиГСМнаТС.ТС,
    0,
    0,
    уатОстаткиГСМнаТС.Организация,
    уатОстаткиГСМнаТС.Количество,
    уатОстаткиГСМнаТС.Стоимость
ИЗ
    РегистрНакопления.уатОстаткиГСМнаТС КАК уатОстаткиГСМнаТС
ГДЕ
    уатОстаткиГСМнаТС.ТС.Наименование <> &НаименованиеТС
    И уатОстаткиГСМнаТС.Период МЕЖДУ &НачалоПериода И &конецПериода
    И уатОстаткиГСМнаТС.ВидДвижения = &Расход

Избегаем сравнений по строковым полям (`Наименование`)

Обратите внимание на условие Пробег.ТС.Наименование <> &НаименованиеТС. Это критически важная точка для оптимизации. Сравнение по строковому полю, особенно с оператором <> (не равно), может приводить к значительному замедлению работы запроса. Почему?

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

Решение: Мы должны изменить условие фильтрации. Если мы хотим исключить конкретное транспортное средство, мы должны фильтровать по его ссылке, а не по наименованию. То есть, вместо передачи наименования в параметр &НаименованиеТС, мы должны передавать ссылку на объект ТС (например, из справочника) в новый параметр &ИсключаемоеТС.

Оптимизированное условие будет выглядеть так:


// ...
ГДЕ
    // ...
    И Пробег.ТС <> &ИсключаемоеТС

Если нам нужно исключить несколько ТС, мы можем использовать конструкцию НЕ В (СПИСОК):


// ...
ГДЕ
    // ...
    И Пробег.ТС НЕ В (&СписокИсключаемыхТС)

Использование виртуальных таблиц регистров

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

В нашем запросе мы выбираем данные по Период МЕЖДУ &НачалоПериода И &КонецПериода. Это явный признак того, что мы можем (и должны) использовать виртуальные таблицы.

Обороты: Если нам нужны данные о приходе и расходе (обороты) за период.
ОстаткиИОбороты: Если нужны начальный остаток, обороты и конечный остаток.
СрезПоследних: Если нужен только последний остаток на определенную дату (актуально, если регистр остаточный).

В нашем случае, для получения Пробег.Количество и ВесГруза.Количество, а также уатОстаткиГСМнаТС.Количество и уатОстаткиГСМнаТС.Стоимость за период, мы бы использовали виртуальную таблицу Обороты.

Пример использования виртуальной таблицы Обороты:


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

Разберем этот пример:

Мы используем РегистрНакопления.уатВыработкаТС.Обороты(...) и РегистрНакопления.уатОстаткиГСМнаТС.Обороты(...), передавая в них параметры периода и фильтрации.
Внутри виртуальной таблицы Обороты мы уже можем указать условие ТС <> &ИсключаемоеТС, что позволит СУБД отфильтровать данные на самом раннем этапе.
Мы объединяем обороты двух разных регистров по Регистратор с помощью ЛЕВОЕ СОЕДИНЕНИЕ, так как нам нужны данные из обоих регистров.
С помощью конструкции ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ 0 КОНЕЦ и агрегатной функции СУММА мы формируем нужные поля (Пробег, ВесГруза, КоличествоРасход, СтоимостьРасход) из различных параметров выработки или видов движения.
Обязательно используем СГРУППИРОВАТЬ ПО для всех неагрегированных полей.

Этот подход позволяет нам объединить три секции из исходного запроса в одну, используя более эффективные виртуальные таблицы и корректную фильтрацию по ссылке.

Дополнительные рекомендации по оптимизации запросов

Временные таблицы (ПОМЕСТИТЬ): Если запрос становится слишком сложным, и нам нужно выполнить несколько логических шагов обработки данных, мы можем использовать временные таблицы с конструкцией ПОМЕСТИТЬ. Это позволяет разбить сложный запрос на более простые, промежуточные этапы, где каждый этап может быть оптимизирован отдельно.
Минимизация выбираемых полей: Мы должны выбирать из базы данных только те поля, которые нам действительно нужны для отчета. Избыточные поля увеличивают объем передаваемых данных и замедляют выполнение запроса.

Индексирование и настройки СКД

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

Индексирование

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

Поле Период в регистрах накопления.
Поле Регистратор в регистрах накопления.
Поле ТС (ссылка) в регистрах накопления.
Поле ПараметрВыработки в регистре уатВыработкаТС.
Поле ВидДвижения в регистре уатОстаткиГСМнаТС.

Правильно настроенные индексы значительно ускоряют поиск и фильтрацию данных, предотвращая полные сканирования таблиц.

Настройки Компоновки Данных (СКД)

Даже самый быстрый запрос может быть замедлен неоптимальными настройками СКД:

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

Использование технологического журнала 1С

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

Время выполнения запросов к СУБД.
Время обработки данных самой СКД (вычисление полей, группировки).
Время формирования выходной формы и вывода на экран.

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

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