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

← На главную

Нередко при добавлении новых данных в рабочий запрос время его выполнения многократно возрастает — например, с 0,7–1 секунды до 5 секунд и более. Проанализируем типичную ситуацию: к выборке документов добавили получение количества связанных документов из регистра сведений, после чего запрос стал сильно тормозить на реальной базе. Разберем по шагам, какие ошибки приводят к снижению скорости и как правильно переписать запрос для максимального быстродействия.

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

Внимательно проанализируем исходный доработанный код и выделим критические узкие места:

Разыменование составных типов данных через точку. Конструкции вида ДопДокументы.Документ2.Состояние.Закрыт и ДопДокументы.Документ2.Тип.Код приводят к тому, что платформа на уровне СУБД генерирует неявные левые соединения (LEFT JOIN) со всеми возможными таблицами типов, входящих в составное измерение. Если в типе поля указаны десятки видов документов, СУБД выполняет десятки скрытых соединений для каждой строки.
Повторное соединение с одной и той же таблицей. В коде данные регистра сначала выбираются во временную таблицу ВТДопДокументы для подсчета незавершенных документов, а затем в основном запросе та же таблица регистра РегистрСведений.ДопДокументы соединяется повторно для вычисления общего количества. Это создает лишнюю нагрузку на дисковую подсистему и процессор.
Тяжелая группировка по широким полям и строкам неограниченной длины. В основном запросе одновременно используются ключевые слова РАЗЛИЧНЫЕ и СГРУППИРОВАТЬ ПО, причем группировка выполняется по полям ВЫРАЗИТЬ(Выписка.Описание КАК СТРОКА(1024)) и ВЫРАЗИТЬ(Выписка.Замечания КАК СТРОКА(1024)). Присоединение неагрегированных записей регистра к таблице документов размножает строки, а последующая группировка по строкам в 1024 символа вызывает сброс промежуточных данных в tempdb (Spill to tempdb) и тяжелую сортировку.
Отсутствие индексации во временной таблице. Временная таблица ВТДопДокументы создается без явного указания индексов. При левом соединении с ней СУБД вынуждена выполнять полное сканирование (Table Scan), а не быстрый поиск по индексу (Index Seek).
Сравнение по строковому коду вместо ссылки. Условие СвойстваДокументов.Свойство.Код = "00199" или Документ2.Тип.Код <> "000000017" заставляет СУБД выполнять соединение со справочником и производить текстовое сравнение строк, вместо эффективного сравнения двоичных идентификаторов ссылок (GUID).

Шаг 1. Перенос всех расчетов в единую временную таблицу

Рассмотрим, как избавиться от двойного соединения с регистром сведений. Вместо того чтобы агрегировать данные дважды (сначала во временную таблицу, а потом в основном запросе), посчитаем общее количество документов и количество незавершенных документов в рамках одной временной таблицы с помощью условной агрегации ВЫБОР КОГДА ... ТОГДА.

Обязательно добавим предложение ИНДЕКСИРОВАТЬ ПО, чтобы СУБД построила индекс по полю связи:


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

Шаг 2. Устранение неявных разыменований и приведение типов

Проанализируем работу с составными типами данных. Если измерение Документ1 или Документ2 имеет составной тип, обращение через точку без указания конкретного типа порождает множественные соединения. Чтобы помочь оптимизатору СУБД, используем явное приведение типов с помощью функции ВЫРАЗИТЬ(... КАК ...).

Также заменим поиск по строковому представлению кода на передачу готовой ссылки в параметрах запроса:

В коде на встроенном языке заранее получаем ссылку на исключаемый тип и передаем в параметр: Запрос.УстановитьПараметр("ИсключаемыйТип", СсылкаНаТип);.
Для отбора по свойству передаем ссылку на свойство: Запрос.УстановитьПараметр("СвойствоСрочно", СсылкаНаСвойство);.
Для отбора по состояниям документа «Выписка» формируем массив подходящих ссылок и передаем через &МассивСостояний, чтобы избавиться от условий с оператором ИЛИ в секции ГДЕ.

Шаг 3. Оптимизация основного запроса и устранение избыточных группировок

Посмотрим на основной запрос. Поскольку мы перенесли расчет всех количественных показателей во временную таблицу, в основном запросе отпадает необходимость объединять строки документов со множеством записей регистра. Это позволяет полностью убрать конструкцию СГРУППИРОВАТЬ ПО из основного запроса, а также убрать дублирующее ключевое слово РАЗЛИЧНЫЕ.

Соберем оптимизированный итоговый пакет запросов:


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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ РАЗРЕШЕННЫЕ
	Выписка.Номер КАК Номер,
	ЕСТЬNULL(ВТДопДокументы.ДопВсего, 0) КАК ДопВсего,
	ЕСТЬNULL(ВТДопДокументы.ДопНезавершенные, 0) КАК ДопНезавершенные,
	Выписка.ДатаПоследнегоИзменения КАК ДатаПоследнегоИзменения,
	Выписка.КонтактноеЛицо КАК КонтактноеЛицо,
	Выписка.Услуга КАК Услуга,
	ВЫРАЗИТЬ(Выписка.Описание КАК СТРОКА(1024)) КАК Описание,
	ВЫРАЗИТЬ(Выписка.Замечания КАК СТРОКА(1024)) КАК Замечания,
	Выписка.Ответственный КАК Ответственный,
	Выписка.Состояние КАК Состояние,
	Выписка.Дата КАК Дата,
	ПараметрыВыписки.ДатаИсполнения КАК ДатаИсполнения,
	ИсторияОткрытия.Период КАК ДатаПоследнегоОткрытия,
	ИсторияОткрытия.Пользователь КАК Пользователь,
	ВремяОбработкиДокумента.ПроцентОтНормы КАК ПроцентОтНормы,
	СвойстваДокументов.Значение КАК Срочно
ИЗ
	Документ.Выписка КАК Выписка
	ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ПараметрыВыписки КАК ПараметрыВыписки
		ПО Выписка.Ссылка = ПараметрыВыписки.Документ
	ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ИсторияОткрытия КАК ИсторияОткрытия
		ПО Выписка.Ссылка = ИсторияОткрытия.Документ
	ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СвойстваДокументов КАК СвойстваДокументов
		ПО Выписка.Ссылка = СвойстваДокументов.Документ
		И (СвойстваДокументов.Свойство = &СвойствоСрочно)
	ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ВремяОбработкиДокумента КАК ВремяОбработкиДокумента
		ПО Выписка.Ссылка = ВремяОбработкиДокумента.Документ
	ЛЕВОЕ СОЕДИНЕНИЕ ВТДопДокументы КАК ВТДопДокументы
		ПО Выписка.Ссылка = ВТДопДокументы.Документ1
ГДЕ
	Выписка.Состояние В(&МассивСостояний)

Проверка структуры и индексов регистра сведений

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

Для устранения этой проблемы рекомендуется:

В конфигураторе открыть свойства регистра сведений ДопДокументы.
Для измерения Документ1 установить свойство Индексировать (или «Индексировать с доп. упорядочиванием»). Это создаст отдельный некластерный индекс по данному полю.
Если есть возможность, ограничить выборку документов по периоду дат (например, за последний месяц или год), так как выборка за всю историю базы данных со временем неизбежно будет деградировать по скорости.