Разработка интерфейсов с высокой отзывчивостью — одна из главных задач программиста «1С:Предприятия». Однако на практике часто встречаются ситуации, когда открытие формы или банальная прокрутка динамического списка вызывают зависание системы на 30 и более секунд. Особенно остро эта проблема проявляется при работе с денормализованными регистрами сведений больших объемов (от единиц до десятков миллионов записей), где хранятся лог-состояния различных объектов.
В этой статье мы подробно разберем, почему сложные конструкции ВЫБОР КОГДА совместно с временными таблицами и группировками приводят к критическому падению производительности динамических списков, и рассмотрим пошаговые сценарии оптимизации таких запросов на уровне СУБД и архитектуры платформы «1С:Предприятие 8.3».
Рассмотрим исходный сценарий. У нас имеется регистр сведений РегистрСведений.СостоянияОбъектов, содержащий информацию о состояниях различных объектов в разрезе задач и заданий. Динамический список должен выводить финальное (наиболее приоритетное) состояние объекта по правилу расчета «весов» для 35 различных колонок-состояний. Исходный запрос разработчика состоял из трех пакетов:
СписокОбъектов с фильтрацией по входящему массиву заданий.СостоянияОбъектов, где происходит преобразование перечислений состояний в числовые «веса» (0, 1, 2, 11, 12) с помощью громоздких условий ВЫБОР КОГДА. В этой же таблице через ОБЪЕДИНИТЬ ВСЕ собираются данные из аналогичного регистра без заданий.Объект с получением максимального веса состояния (функция МАКСИМУМ) и обратным преобразованием чисел в человекочитаемые значения перечислений.Давайте проанализируем, почему этот запрос в контексте динамического списка работает крайне медленно:
СписокОбъектов создается без индекса. При последующем внутреннем соединении с многомиллионным регистром СУБД вынуждена выполнять операцию Hash Match или полный скан таблиц вместо быстрого индексного поиска (Index Seek).Поле В (Значение1, Значение2) внутри ВЫБОР усложняет построение плана запроса SQL. Оптимизатор СУБД тратит драгоценное время на компиляцию плана и часто ошибается в оценке количества строк.TOP 45). Однако наличие в основном запросе пакета временных таблиц, группировок (СГРУППИРОВАТЬ ПО) и соединений заставляет СУБД сначала полностью рассчитать весь массив данных (все 360 000 строк) и только потом отсечь первые 45. На каждый скролл или обновление экрана система заново производит этот тяжелейший расчет.Первое, на что следует обратить внимание — это избыточность условий проверки значений перечисления в конструкциях ВЫБОР. Вместо проверки на вхождение в список через оператор В, гораздо эффективнее использовать прямые сравнения и синтаксис выбора по конкретному полю. Проанализируем исходный код:
// Было:
ВЫБОР
КОГДА Состояния.Состояние25 В (ЗНАЧЕНИЕ(Перечисление.ТройноеБулево.Null), ЗНАЧЕНИЕ(Перечисление.ТройноеБулево.ПустаяСсылка))
ТОГДА 0
КОГДА Состояния.Состояние25 В (ЗНАЧЕНИЕ(Перечисление.ТройноеБулево.Нет))
ТОГДА 11
КОГДА Состояния.Состояние25 В (ЗНАЧЕНИЕ(Перечисление.ТройноеБулево.Да))
ТОГДА 12
ИНАЧЕ 0
КОНЕЦ КАК Состояние25
Поскольку значением по умолчанию для отсутствующих данных в СУБД при левом соединении будет NULL, а для пустых ссылок — соответствующее системное значение, мы можем существенно упростить конструкцию. Перепишем ее в классический оператор выбора по значению:
// Стало:
ВЫБОР Состояния.Состояние25
КОГДА ЗНАЧЕНИЕ(Перечисление.ТройноеБулево.Нет)
ТОГДА 11
КОГДА ЗНАЧЕНИЕ(Перечисление.ТройноеБулево.Да)
ТОГДА 12
ИНАЧЕ 0
КОНЕЦ КАК Состояние25
Такой синтаксис транслируется SQL-сервером в гораздо более простую древовидную структуру условий, снижая нагрузку на парсер запросов СУБД.
Если в вашем запросе неизбежно используются временные таблицы, которые затем участвуют в соединениях (JOIN) с другими таблицами, их поля связи обязательно должны быть проиндексированы. В нашем случае временная таблица СписокОбъектов соединяется по полю Объект.
Добавим инструкцию индексирования непосредственно при создании первой временной таблицы:
ВЫБРАТЬ
ВЫРАЗИТЬ(Т.Объект КАК Справочник.Объекты) КАК Объект
ПОМЕСТИТЬ СписокОбъектов
ИЗ
РегистрСведений.СостоянияОбъектов КАК Т
ГДЕ
Т.Задание В(&ЗаданияОтбор)
СГРУППИРОВАТЬ ПО
ВЫРАЗИТЬ(Т.Объект КАК Справочник.Объекты)
ИНДЕКСИРОВАТЬ ПО
Объект
;
Благодаря добавлению ИНДЕКСИРОВАТЬ ПО Объект, СУБД создаст временный индекс в базе данных TempDB. При последующем соединении с таблицей СостоянияОбъектов оптимизатор сможет применить эффективный алгоритм Merge Join или быстрый индексный поиск по цепочке связей.
В исходном запросе во втором пакете присутствовало следующее соединение:
ВНУТРЕННЕЕ СОЕДИНЕНИЕ СписокОбъектов КАК Т
ПО Состояния.Объект = Т.Объект
И (Состояния.Задание В (&ЗаданияОтбор, &ЗаданияЗадачОтбор))
Обратите внимание: таблица СписокОбъектов на первом шаге уже была отфильтрована строго по условию Т.Задание В (&ЗаданияОтбор). Повторное наложение этого же условия во внутреннем соединении на втором шаге является абсолютно избыточным. СУБД тратит ресурсы на повторную фильтрацию. Более того, если поле Задание имеет составной тип данных, это условие заставит SQL-сервер выполнять неявные приведения типов, полностью исключая использование индексов по этому измерению.
Рекомендуется убрать избыточный фильтр &ЗаданияОтбор из условий соединения, оставив только соединение по индексированному полю Объект.
В процессе обсуждения на форуме (сообщение 74) была предпринята попытка заменить условия ВЫБОР КОГДА на левые соединения со статическими временными таблицами приоритетов (ВТ_ПорядокСостояний). Идея заключалась в том, чтобы сопоставить каждому значению перечисления числовой приоритет через обычный LEFT JOIN.
Однако этот подход привел к еще более катастрофическому падению скорости. Выясним причину:
Table Scan) и лавинообразному росту использования TempDB.Вывод: Никогда не используйте десятки левых соединений для тривиальной подстановки значений. В таких случаях ВЫБОР КОГДА или программная обработка на клиенте/сервере будут работать значительно быстрее.
Если данные выводятся в динамическом списке управляемой формы, самым правильным и архитектурно верным решением будет отказ от расчета сложных состояний непосредственно в основном запросе списка. Вместо этого мы применим порционный расчет данных только для тех строк, которые реально видны пользователю на экране.
Разберем этот метод по шагам:
1. Упрощаем основной запрос динамического списка. Мы полностью удаляем из него пакеты временных таблиц, тяжелые группировки по 35 колонкам и соединения. Запрос должен выбирать только базовые реквизиты объекта:
ВЫБРАТЬ
Т.Ссылка КАК Объект,
Т.Код КАК Код,
Т.Наименование КАК Наименование
ИЗ
Справочник.Объекты КАК Т
Этот запрос отработает мгновенно, так как платформа сможет применить к нему стандартное ограничение TOP 45 при открытии формы.
2. Настраиваем форму. Добавляем в таблицу формы колонки для отображения наших 35 состояний. Эти колонки будут не связанными с основным реквизитом динамического списка — мы будем заполнять их программно.
3. Создаем обработчик события таблицы. Для нашей таблицы на форме создаем обработчик события ПриПолученииДанныхНаСервере. Этот обработчик принимает параметр НовыеСтроки, который содержит коллекцию только тех строк, которые СУБД подготовила для отображения на экране (обычно от 30 до 50 строк).
Напишем эффективный алгоритм получения состояний в модуле управляемой формы:
&НаСервере
Процедура СписокПриПолученииДанныхНаСервере(ИмяЭлемента, Настройки, Строки)
// Шаг 1. Собираем массив объектов, которые отображаются на экране
МассивОбъектов = Новый Массив;
Для Каждого КлючИЗначение Из Строки Цикл
МассивОбъектов.Добавить(КлючИЗначение.Ключ);
КонецЦикла;
Если МассивОбъектов.Количество() = 0 Тогда
Возврат;
КонецЕсли;
// Шаг 2. Выполняем быстрый точечный запрос только по выбранным объектам
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Состояния.Объект КАК Объект,
| Состояния.Состояние13 КАК Состояние13,
| Состояния.Состояние14 КАК Состояние14,
| Состояния.Состояние25 КАК Состояние25
|ИЗ
| РегистрСведений.СостоянияОбъектов КАК Состояния
|ГДЕ
| Состояния.Объект В (&МассивОбъектов)";
Запрос.УстановитьПараметр("МассивОбъектов", МассивОбъектов);
Выборка = Запрос.Выполнить().Выбрать();
// Шаг 3. Заполняем программные колонки в полученных строках формы
Пока Выборка.Следующий() Цикл
СтрокаСписка = Строки[Выборка.Объект];
Если СтрокаСписка <> Неопределено Тогда
// Здесь мы можем применить любую сложную логику расчета приоритетов
// прямо в коде 1С, так как обрабатываем всего 45 строк!
СтрокаСписка.Данные.Вставить("Состояние13", Выборка.Состояние13);
СтрокаСписка.Данные.Вставить("Состояние14", Выборка.Состояние14);
СтрокаСписка.Данные.Вставить("Состояние25", Выборка.Состояние25);
EndIf;
КонецЦикла;
КонецПроцедуры
Почему это работает невероятно быстро? Вместо того чтобы при каждом действии пользователя лопатить миллионы записей в регистре, группировать их и сортировать в TempDB, СУБД выполняет простейший поиск по первичному индексу (Index Seek) для 45 конкретных ссылок. Время выполнения такого запроса составляет сотые доли миллисекунды, а интерфейс программы становится максимально плавным и отзывчивым.
Если бизнес-требования все же обязывают вас осуществлять поиск и фильтрацию в динамическом списке по вычисляемым колонкам-состояниям (что делает невозможным использование метода ПриПолученииДанныхНаСервере, так как по программно заполняемым полям нельзя делать отборы платформы), необходимо прибегнуть к архитектурному паттерну кэширования состояний.
Для этого выполните следующие действия:
КэшСостоянийОбъектов с измерением Объект и ресурсами Состояние1, Состояние2 ... Состояние35.Объект.Этот подход гарантирует моментальный отклик системы, высокую скорость поиска и масштабируемость системы даже при росте базы данных до сотен миллионов записей.