Как в запросе 1С выбрать только последнюю запись по каждому объекту и избежать замедления?

← На главную

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

Анализ исходной проблемы

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

Попытка использовать конструкцию ПЕРВЫЕ 1 с упорядочиванием не решает проблему, так как данная инструкция ограничит весь результирующий набор ровно одной записью на весь запрос, а не по одной строке для каждого отдельного объекта.

Решение 1. Использование временной таблицы с группировкой и фильтрацией

Разберем классический и наиболее универсальный подход в языке запросов 1С. Он заключается в разделении задачи на два этапа:

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

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


ВЫБРАТЬ
	ЭДО_Состояние.Объект КАК Объект,
	МАКСИМУМ(ЭДО_Состояние.ДатаВыгрузки) КАК ДатаВыгрузки
ПОМЕСТИТЬ ВТ_ПоследниеДаты
ИЗ
	РегистрСведений.ЭДО_Состояние КАК ЭДО_Состояние
СГРУППИРОВАТЬ ПО
	ЭДО_Состояние.Объект
;

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

Посмотрим на оптимизированный вариант с правильной фильтрацией и индексацией временной таблицы:


ВЫБРАТЬ
	ЭДО_Состояние.Объект КАК Объект,
	МАКСИМУМ(ЭДО_Состояние.ДатаВыгрузки) КАК ДатаВыгрузки
ПОМЕСТИТЬ ВТ_ПоследниеДаты
ИЗ
	РегистрСведений.ЭДО_Состояние КАК ЭДО_Состояние
ГДЕ
	ЭДО_Состояние.ТипДокумента В(&ТипыДокументов)
	И НЕ ЭДО_Состояние.СтатусДокумента В(&СписокСтатусов_Завершение)
	И ЭДО_Состояние.ДатаВыгрузки >= &ГраницаДаты
СГРУППИРОВАТЬ ПО
	ЭДО_Состояние.Объект
ИНДЕКСИРОВАТЬ ПО
	Объект,
	ДатаВыгрузки
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ЭДО_Состояние.Объект КАК Объект,
	ЭДО_Состояние.ДатаСтатуса КАК ДатаСтатуса,
	ЭДО_Состояние.СтатусДокумента КАК СтатусДокумента,
	ЭДО_Состояние.ДатаВыгрузки КАК ДатаВыгрузки,
	РАЗНОСТЬДАТ(ЭДО_Состояние.ДатаВыгрузки, &ТекДата, ДЕНЬ) КАК ПрошлоДней
ИЗ
	ВТ_ПоследниеДаты КАК ВТ_ПоследниеДаты
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЭДО_Состояние КАК ЭДО_Состояние
		ПО ВТ_ПоследниеДаты.Объект = ЭДО_Состояние.Объект
			И ВТ_ПоследниеДаты.ДатаВыгрузки = ЭДО_Состояние.ДатаВыгрузки

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

Оптимизация условий отбора по дате (SARGability)

Проанализируем еще одну критическую ошибку производительности, часто встречающуюся в запросах 1С:


ГДЕ РАЗНОСТЬДАТ(ЭДО_Состояние.ДатаВыгрузки, &ТекДата, ДЕНЬ) <= &ДнейСМоментаВыгрузкиНеБолее

Когда в секции ГДЕ к колонке таблицы применяется встроенная функция (в данном случае РАЗНОСТЬДАТ), СУБД вынуждена вычислять эту функцию построчно для всей таблицы, что полностью блокирует использование существующих индексов. Полнотекстовое сканирование таблицы (Table Scan) приводит к колоссальным задержкам на больших объемах данных.

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


// Рассчитываем граничную дату заранее
СекундВСутках = 86400;
ДатаНачала = НачалоДня(ТекущаяДатаСеанса() - ДнейСМоментаВыгрузкиНеБолее * СекундВСутках);

Запрос.УстановитьПараметр("ГраницаДаты", ДатаНачала);
Запрос.УстановитьПараметр("ТекДата", ТекущаяДатаСеанса());

Теперь в условии запроса используется прямое сравнение индексированного поля: ЭДО_Состояние.ДатаВыгрузки >= &ГраницаДаты. Оптимизатор СУБД сможет применить операцию Index Seek, моментально отсекая неактуальные строки.

Решение проблемы потенциальных дублей

Рассмотрим важный краевой случай: если у одного и того же объекта совпадут значения поля ДатаВыгрузки в двух разных записях, внутреннее соединение вернет обе записи, что вновь приведет к задвоению.

Для предотвращения подобной коллизии воспользуемся одним из следующих приемов:

Если регистр подчинен регистратору, добавим во временную таблицу дополнительную агрегацию: МАКСИМУМ(Период) или МАКСИМУМ(НомерСтроки).
Если регистр не подчинен регистратору, во втором пакете запроса выполним группировку по объекту с агрегацией остальных полей (например, МАКСИМУМ(СтатусДокумента)).

Решение 2. Выборка через левое соединение без временных таблиц

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


ВЫБРАТЬ
	Т1.Объект КАК Объект,
	Т1.ДатаВыгрузки КАК ДатаВыгрузки,
	Т1.СтатусДокумента КАК СтатусДокумента,
	РАЗНОСТЬДАТ(Т1.ДатаВыгрузки, &ТекДата, ДЕНЬ) КАК ПрошлоДней
ИЗ
	РегистрСведений.ЭДО_Состояние КАК Т1
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЭДО_Состояние КАК Т2
		ПО Т1.Объект = Т2.Объект
			И Т1.ДатаВыгрузки < Т2.ДатаВыгрузки
			И Т2.ТипДокумента В(&ТипыДокументов)
			И НЕ Т2.СтатусДокумента В(&СписокСтатусов_Завершение)
			И Т2.ДатаВыгрузки >= &ГраницаДаты
ГДЕ
	Т2.Объект ЕСТЬ NULL
	И Т1.ТипДокумента В(&ТипыДокументов)
	И НЕ Т1.СтатусДокумента В(&СписокСтатусов_Завершение)
	И Т1.ДатаВыгрузки >= &ГраницаДаты

Такой вариант удобен для небольших выборок, так как не задействует системную базу данных tempdb для создания физических временных таблиц. Однако на больших таблицах соединение по неравенству (<) может уступать по скорости группировке с индексацией.

Архитектурные рекомендации

В завершение проанализируем структуру самих метаданных конфигурации 1С:

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