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

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

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

1. Выбираем правильный уровень для отбора: Отчет или Группировка?

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

1. Главный отбор отчета (на уровне отчета): Этот отбор применяется ко всему отчету и влияет на получение данных из всех наборов данных. Если это возможно, СКД попытается поместить условия из главного отбора непосредственно в текст запроса, например, в условие виртуальной таблицы, в предложение ГДЕ или ИМЕЮЩИЕ. Это происходит, если для набора данных включено автоматическое заполнение полей или явно указано, что отбор по данному полю должен быть помещен в условие виртуальной таблицы.
2. Отбор группировки (на уровне группировки): Этот отбор фильтрует записи, выводимые только в конкретной группировке и вложенных в нее, не затрагивая другие группировки отчета.

Практический совет:

Мы выяснили, что накладывать отбор на уровне группировки, а не на уровне отчета, часто оказывается более эффективным. В ходе экспериментов было замечено, что на уровне "группировки СКД" отбор работает при любых полях в предложении СГРУППИРОВАТЬ ПО. Это может быть полезно, когда нам нужно отфильтровать только часть выводимых данных, не изменяя при этом общую выборку для других группировок или итогов.

2. Оптимизация запросов с использованием временных таблиц (ВТ)

При использовании пакетов запросов с временными таблицами, мы часто создаем избыточные данные, которые затем влияют на производительность. Давайте разберем, как минимизировать эту проблему:

1. Убираем автозаполнение:

   Начнем с отключения автозаполнения. Это дает нам полный контроль над тем, какие поля выбираются и какие таблицы участвуют в запросе.

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

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

   • Переделываем запрос таким образом, чтобы в СГРУППИРОВАТЬ ПО во временных таблицах присутствовали только действительно необходимые поля.
   • Избегаем создания очень больших временных таблиц и не помещаем в них поля, которые не используются в последующих запросах.
3. Дополнительные рекомендации по работе с ВТ:
   • Разбиение сложных запросов: Если у нас есть очень сложный запрос, мы можем разбить его на несколько более простых с использованием временных таблиц. Это упрощает отладку и иногда улучшает производительность.
   • Индексирование ВТ: Помним, что в 1С нет прямого аналога индексов для временных таблиц, но мы можем имитировать их, правильно формируя запросы и используя поля для соединений.
   • Менеджер временных таблиц: При программном формировании отчетов на СКД мы можем передать менеджер временных таблиц в метод инициализации процессора компоновки. Это полезно, если одинаковые запросы используются в разных наборах данных, так как позволяет использовать уже существующую временную таблицу.

3. Управление полями и группировками для эффективного отбора

Эффективность отбора тесно связана с тем, как мы управляем полями и группировками в СКД. Рассмотрим подробнее:

1. Контроль включения таблиц в запрос:

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

   Скопировать код

   
   ВЫБРАТЬ
       Таблица1.Поле1,
       Таблица1.Поле2
       {ЛЕВОЕ СОЕДИНЕНИЕ Таблица2 КАК Таблица2 ПО Таблица1.ПолеКлюч = Таблица2.ПолеКлюч}
   ИЗ
       Таблица1 КАК Таблица1
   

   В этом примере Таблица2 будет присоединена только в том случае, если пользователь выбрал из нее какие-либо поля.

2. Динамическая группировка:

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

4. Использование псевдонимов полей для гибких отборов

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

Рассмотрим на примере:

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

Мы можем использовать следующий подход:

ВЫБРАТЬ 1 КАК КоличествоТовара
ПОМЕСТИТЬ ВТ;

ВЫБРАТЬ
    Т.КоличествоТовара
ИЗ
    ВТ КАК Т
{ГДЕ Т.КоличествоТовара КАК ОтборПоКоличеству}

Что здесь происходит:

• Мы создаем временную таблицу ВТ с полем КоличествоТовара.
• В финальном запросе мы выбираем это поле.
• Конструкция {ГДЕ Т.КоличествоТовара КАК ОтборПоКоличеству} сообщает СКД, что для интерактивной установки отборов пользователю будет доступно поле "ОтборПоКоличеству".
• При этом, когда пользователь установит отбор по "ОтборПоКоличеству", он будет наложен именно на поле Т.КоличествоТовара в последнем запросе.

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

5. Инструменты для анализа и отладки

Чтобы эффективно оптимизировать отчеты, нам необходимы мощные инструменты для анализа запросов и настроек СКД. Без них вся оптимизация превращается в "гадание на кофейной гуще".

1. Консоль СКД из ИР (Инструменты Разработчика):

   Это незаменимый инструмент. С его помощью мы можем:

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

   Важно не просто посмотреть запрос, но и проанализировать его фактическое выполнение. Для этого используем:

   • Журнал регистрации 1С: в нем можно включить логирование выполнения длительных запросов.
   • Технологический журнал (ТЖ): дает более детальную информацию о работе платформы, включая время выполнения запросов, блокировки и другие события.
   • Профилировщики запросов (если работаем с СУБД напрямую): позволяют глубоко анализировать планы выполнения запросов.

6. Общие методы оптимизации запросов СКД

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

1. Виртуальные таблицы:
   • Виртуальная таблица Остатки строится на начало секунды, указанной в параметре Период, то есть не включает саму дату. Чтобы получить остаток на конец дня, мы должны добавить одну секунду к параметру Период.
   • Виртуальные таблицы Обороты и ОстаткиИОбороты включают дату начала и дату окончания.
   • Стараемся избегать соединения с виртуальной таблицей, если она может оказаться справа (внутреннее соединение), и стремимся собирать срезы вручную, явно индексируя поля.
2. Замена вложенных запросов на временные таблицы:

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

3. Устранение функций от параметров:

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

4. Ограничение получения поля "Ссылка":

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

5. Работа с "набором данных - объект":

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

6. Прототипирование и сравнение:

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

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