В практике разработчика 1С часто встречаются задачи построения комплексных отчетов, которые должны собирать данные из разных источников: продаж, графиков обслуживания, событий и заказов. Если такой запрос начинает выполняться дольше 2–3 секунд, это повод проанализировать его структуру. Часто попытки оптимизации «наобум» (простая замена вложенных запросов на пакеты) приводят к обратному результату — замедлению работы. Рассмотрим подробнее, как правильно подойти к оптимизации тяжелого запроса, на примере реальной ситуации с анализом прохождения ТО автомобилей.
Проанализируем типичную ситуацию: запрос содержит более 10 блоков ОБЪЕДИНИТЬ ВСЕ, в каждом из которых выполняются левые соединения с виртуальными таблицами и табличными частями документов. Разберем по шагам основные ошибки, которые «убивают» производительность:
Регистратор.Организация или Работа.ПрохождениеТО.Наименование в запросе заставляет платформу создавать неявные левые соединения со всеми таблицами документов, которые могут быть регистраторами. Если в регистре много типов документов, это порождает десятки лишних JOIN на уровне SQL.ПО ... НАЧАЛОПЕРИОДА(Продажи.Период, ДЕНЬ), мы создаем условия, которые СУБД не может оптимизировать с помощью индексов (non-SARGable условия).Документ.ЗаказНаряд.Работы крайне медленны. Регистры накопления и сведений всегда работают быстрее, так как их таблицы оптимизированы для выборок.Наименование = "ТО 1" — это медленная операция сравнения строк, которая к тому же делает код зависимым от человеческого фактора.Если вам необходима информация об организации из регистратора, выясним причину, почему нельзя использовать реквизиты самого регистра. Самый быстрый способ — добавить реквизит Организация в регистр накопления ПродажиАвтомобилей. Если это невозможно, используйте функцию ВЫРАЗИТЬ.
Посмотрим на пример оптимизации получения организации:
ВЫБРАТЬ
ПродажаАвтомобилейОбороты.Автомобиль,
ВЫРАЗИТЬ(ПродажаАвтомобилейОбороты.Регистратор КАК Документ.РеализацияАвтомобилей).Организация КАК Организация,
ПродажаАвтомобилейОбороты.Период КАК Период
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
РегистрНакопления.ПродажиАвтомобилей.Обороты(&ДатаНачала, &ДатаОкончания, Регистратор, ) КАК ПродажаАвтомобилейОбороты
ИНДЕКСИРОВАТЬ ПО
Автомобиль
Важный момент: использование периодичности Регистратор в виртуальных таблицах оборотов — очень ресурсоемкая операция. Если вам нужны просто записи о продажах, рассмотрите возможность обращения к физической таблице регистра накопления вместо виртуальной таблицы Обороты.
Чтобы избежать многократного сканирования одних и тех же данных в блоках ОБЪЕДИНИТЬ ВСЕ, необходимо подготовить промежуточные данные во временных таблицах (ВТ). Проанализируем ситуацию: если в каждом блоке мы ищем работы по конкретному ТО, лучше один раз собрать все работы во временную таблицу.
Вместо фильтрации по наименованию "ТО 1", "ТО 2", создадим ВТ со списком работ и их категорий:
ВЫБРАТЬ
СправочникРаботы.Ссылка КАК Работа,
СправочникРаботы.ПрохождениеТО КАК ВидТО
ПОМЕСТИТЬ ВТ_ВидыРабот
ИЗ
Справочник.Работы КАК СправочникРаботы
ГДЕ
СправочникРаботы.ПрохождениеТО В (&СписокНужныхТО)
ИНДЕКСИРОВАТЬ ПО
Работа
Теперь при соединении с табличными частями (если без них не обойтись) мы будем использовать индекс по полю Работа, что значительно ускорит процесс.
Одной из главных причин тормозов является вычисление функций внутри условий ПО. СУБД вынуждена вычислять значение функции для каждой строки, прежде чем сравнить их. Рассмотрим подробнее, как это исправить. Вместо НАЧАЛОПЕРИОДА в условии соединения, подготовьте дату начала дня заранее в первой временной таблице.
Пример правильной подготовки данных:
ВЫБРАТЬ
Продажи.Автомобиль,
НАЧАЛОПЕРИОДА(Продажи.Период, ДЕНЬ) КАК ПериодДень,
Продажи.Организация
ПОМЕСТИТЬ ВТ_ПродажиПодготовленные
ИЗ
ВТ_Продажи КАК Продажи
ИНДЕКСИРОВАТЬ ПО
Автомобиль,
ПериодДень
Теперь соединение будет выглядеть так: ПО ВТ_Продажи.ПериодДень <= График.ДатаПродажи. Это позволит СУБД использовать индексы по датам.
Если структура вашего запроса подразумевает 15 однотипных выборок для разных ТО, это создает колоссальную нагрузку. Посмотрим на пример архитектурного улучшения: вместо 15 объединений можно сделать одну выборку, где данные по всем ТО будут собраны в одну таблицу, а затем сгруппированы.
Разберем алгоритм:
ВТ_Продажи.ВТ_Обслуживание.ВТ_Продажи с ВТ_Обслуживание по автомобилю.ВЫБОР КОГДА для распределения данных по нужным колонкам.Проанализируем еще несколько советов, которые помогут довести запрос до идеала:
СГРУППИРОВАТЬ ПО или ВЫБРАТЬ РАЗЛИЧНЫЕ во временной таблице, прежде чем соединять её с основным потоком данных. Это избавит от тяжелых группировок в финальном запросе.ИЛИ в условиях соединения. Оптимизатор запросов часто пасует перед ними. Если нужно соединение по двум разным условиям, иногда действительно быстрее использовать ОБЪЕДИНИТЬ ВСЕ для двух разных запросов, но только если каждый из них работает по индексу.Index Scan или Table Scan на больших таблицах — индекс не используется. Ищите, где поле обернуто в функцию или где происходит неявное приведение типов.Выясним причину, почему советы из интернета иногда «подводят»: перенос вложенного запроса в пакет ВТ не поможет, если данные в этой ВТ не проиндексированы или если само условие соединения некорректно. Оптимизация — это не механическая перестановка блоков кода, а приведение условий к виду, понятному для SQL-оптимизатора.
Следуя этим правилам, можно добиться сокращения времени выполнения запроса с 10 секунд до нескольких сотен миллисекунд, обеспечивая комфортную работу пользователей с отчетом.