Оптимизация запроса 1С через АВТОНОМЕРЗАПИСИ сводится к одной идее: если таблицу приходится соединять саму с собой по многим полям, сначала выделите уникальные сочетания этих полей, пронумеруйте их функцией АВТОНОМЕРЗАПИСИ() и дальше соединяйтесь по одному числовому ключу. Условие соединения из десяти сравнений строк превращается в одно сравнение чисел, и СУБД перестаёт перебирать огромное количество комбинаций.

Ниже — задача, на которой приём даёт наибольший эффект, пошаговое построение пакета, готовый код и ограничения функции. Пример синтетический, но структура один в один переносится на реальные регистры.

Задача: интервалы действия записей периодического регистра

Классический случай — получить для каждой записи периодического регистра сведений период действия: дату начала (период записи) и дату окончания (период следующей записи с тем же набором измерений). Например, регистр цен поставщиков с измерениями Поставщик, Номенклатура, Характеристика, ВидЦены, Валюта. Такая выборка нужна для отчётов «цена на дату по каждой строке документа», для выгрузок в BI и для сверок.

Прямолинейное решение — левое соединение таблицы с самой собой:

Запрос 1С
ВЫБРАТЬ
	Цены.Период КАК Период,
	Цены.Поставщик КАК Поставщик,
	Цены.Номенклатура КАК Номенклатура,
	Цены.Характеристика КАК Характеристика,
	Цены.ВидЦены КАК ВидЦены,
	Цены.Валюта КАК Валюта
ПОМЕСТИТЬ ВТЗаписи
ИЗ
	РегистрСведений.ЦеныПоставщиков КАК Цены
ГДЕ
	Цены.Период < &ДатаОкончания

ИНДЕКСИРОВАТЬ ПО
	Поставщик,
	Номенклатура,
	Характеристика,
	ВидЦены,
	Валюта
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	Записи.Период КАК ДатаНачала,
	МИНИМУМ(ЕСТЬNULL(Следующие.Период, &ДатаОкончания)) КАК ДатаОкончания,
	Записи.Поставщик КАК Поставщик,
	Записи.Номенклатура КАК Номенклатура,
	Записи.Характеристика КАК Характеристика,
	Записи.ВидЦены КАК ВидЦены,
	Записи.Валюта КАК Валюта
ИЗ
	ВТЗаписи КАК Записи
		ЛЕВОЕ СОЕДИНЕНИЕ ВТЗаписи КАК Следующие
		ПО Записи.Период < Следующие.Период
			И Записи.Поставщик = Следующие.Поставщик
			И Записи.Номенклатура = Следующие.Номенклатура
			И Записи.Характеристика = Следующие.Характеристика
			И Записи.ВидЦены = Следующие.ВидЦены
			И Записи.Валюта = Следующие.Валюта

СГРУППИРОВАТЬ ПО
	Записи.Период,
	Записи.Поставщик,
	Записи.Номенклатура,
	Записи.Характеристика,
	Записи.ВидЦены,
	Записи.Валюта

На маленьком регистре это работает. На десятках тысяч записей запрос начинает выполняться десятки секунд, а с ростом числа полей в условии — ещё дольше.

Почему соединение по многим полям тормозит

Причин несколько, и они складываются:

  1. Неравенство в условии. Записи.Период < Следующие.Период даёт для каждой строки все последующие строки того же набора. Объём промежуточного результата растёт квадратично от числа записей в наборе.
  2. Каждое сравнение — работа. Ссылочные поля составного типа, строки, характеристики — сравнение десятка таких полей на каждой паре строк стоит дорого.
  3. Широкий индекс. ИНДЕКСИРОВАТЬ ПО по пяти-десяти полям создаёт тяжёлый составной индекс, а планировщик СУБД не всегда им пользуется так, как вы ожидаете.
  4. Широкая группировка. СГРУППИРОВАТЬ ПО по всем полям снова сравнивает их все.

Количество записей поменять нельзя, а вот количество сравниваемых полей — можно.

Оптимизация запроса через АВТОНОМЕРЗАПИСИ по шагам

Идея: набор измерений (Поставщик, Номенклатура, Характеристика, ВидЦены, Валюта) заменить числом. Строим пакет из шести шагов.

Шаг 1. Исходные записи во временную таблицу — как и раньше.

Шаг 2. Уникальные сочетания измерений, без периода:

Запрос 1С
ВЫБРАТЬ
	Записи.Поставщик КАК Поставщик,
	Записи.Номенклатура КАК Номенклатура,
	Записи.Характеристика КАК Характеристика,
	Записи.ВидЦены КАК ВидЦены,
	Записи.Валюта КАК Валюта
ПОМЕСТИТЬ ВТНаборы
ИЗ
	ВТЗаписи КАК Записи

СГРУППИРОВАТЬ ПО
	Записи.Поставщик,
	Записи.Номенклатура,
	Записи.Характеристика,
	Записи.ВидЦены,
	Записи.Валюта

Шаг 3. Нумеруем наборы. Это отдельный простой запрос: только выборка из готовой временной таблицы и ПОМЕСТИТЬ.

Запрос 1С
ВЫБРАТЬ
	АВТОНОМЕРЗАПИСИ() КАК КлючНабора,
	Наборы.Поставщик КАК Поставщик,
	Наборы.Номенклатура КАК Номенклатура,
	Наборы.Характеристика КАК Характеристика,
	Наборы.ВидЦены КАК ВидЦены,
	Наборы.Валюта КАК Валюта
ПОМЕСТИТЬ ВТНаборыСКлючом
ИЗ
	ВТНаборы КАК Наборы

ИНДЕКСИРОВАТЬ ПО
	КлючНабора

Шаг 4. Каждой исходной записи сопоставляем ключ. Здесь соединение по всем полям ещё есть, но оно по равенству и выполняется один раз, без неравенства по периоду:

Запрос 1С
ВЫБРАТЬ
	НаборыСКлючом.КлючНабора КАК КлючНабора,
	Записи.Период КАК Период
ПОМЕСТИТЬ ВТПериодыНаборов
ИЗ
	ВТНаборыСКлючом КАК НаборыСКлючом
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТЗаписи КАК Записи
		ПО НаборыСКлючом.Поставщик = Записи.Поставщик
			И НаборыСКлючом.Номенклатура = Записи.Номенклатура
			И НаборыСКлючом.Характеристика = Записи.Характеристика
			И НаборыСКлючом.ВидЦены = Записи.ВидЦены
			И НаборыСКлючом.Валюта = Записи.Валюта

ИНДЕКСИРОВАТЬ ПО
	КлючНабора,
	Период

Шаг 5. Тяжёлое соединение с неравенством — теперь по двум узким полям:

Запрос 1С
ВЫБРАТЬ
	Текущие.КлючНабора КАК КлючНабора,
	Текущие.Период КАК ДатаНачала,
	МИНИМУМ(ЕСТЬNULL(Следующие.Период, &ДатаОкончания)) КАК ДатаОкончания
ПОМЕСТИТЬ ВТИнтервалы
ИЗ
	ВТПериодыНаборов КАК Текущие
		ЛЕВОЕ СОЕДИНЕНИЕ ВТПериодыНаборов КАК Следующие
		ПО Текущие.КлючНабора = Следующие.КлючНабора
			И Текущие.Период < Следующие.Период

СГРУППИРОВАТЬ ПО
	Текущие.КлючНабора,
	Текущие.Период

Шаг 6. Возвращаем измерения по ключу:

Запрос 1С
ВЫБРАТЬ
	НаборыСКлючом.Поставщик КАК Поставщик,
	НаборыСКлючом.Номенклатура КАК Номенклатура,
	НаборыСКлючом.Характеристика КАК Характеристика,
	НаборыСКлючом.ВидЦены КАК ВидЦены,
	НаборыСКлючом.Валюта КАК Валюта,
	Интервалы.ДатаНачала КАК ДатаНачала,
	Интервалы.ДатаОкончания КАК ДатаОкончания
ИЗ
	ВТИнтервалы КАК Интервалы
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТНаборыСКлючом КАК НаборыСКлючом
		ПО Интервалы.КлючНабора = НаборыСКлючом.КлючНабора

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

Как выполнить пакет из кода

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

1С
// Возвращает таблицу интервалов действия цен поставщиков до указанной даты.
Функция ИнтервалыДействияЦен(ДатаОкончания) Экспорт

	Запрос = Новый Запрос;
	Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
	Запрос.УстановитьПараметр("ДатаОкончания", ДатаОкончания);

	ЧастиЗапроса = Новый Массив;
	ЧастиЗапроса.Добавить(ТекстЗапросаИсходныеЗаписи());
	ЧастиЗапроса.Добавить(ТекстЗапросаНаборы());
	ЧастиЗапроса.Добавить(ТекстЗапросаНумерацияНаборов());
	ЧастиЗапроса.Добавить(ТекстЗапросаПериодыНаборов());
	ЧастиЗапроса.Добавить(ТекстЗапросаИнтервалы());
	ЧастиЗапроса.Добавить(ТекстЗапросаРезультат());

	Запрос.Текст = СтрСоединить(ЧастиЗапроса, ";" + Символы.ПС);

	Возврат Запрос.Выполнить().Выгрузить();

КонецФункции

Функции ТекстЗапроса... возвращают строки с текстами шагов из предыдущего раздела. Для отладки удобен Запрос.ВыполнитьПакет(): он вернёт массив результатов, и можно посмотреть, сколько строк получилось на каждом шаге.

Ограничения АВТОНОМЕРЗАПИСИ и как их обходить

  • Только с ПОМЕСТИТЬ. Функция работает в запросе, который создаёт временную таблицу. В обычной выборке, без ПОМЕСТИТЬ, она недоступна.
  • Простой запрос для нумерации. Группировку, РАЗЛИЧНЫЕ, объединения и соединения выносите на предыдущий шаг, а нумеруйте уже готовый набор отдельным запросом, как в шаге 3. Так меньше шансов упереться в ограничения платформы и проще читать пакет.
  • Номер — не порядок. Не используйте КлючНабора как порядковый номер по дате или другому полю. Это идентификатор строки, не более.
  • Ключ живёт в пределах пакета. Номера пересчитываются при каждом выполнении; сохранять их в базу или передавать между запусками бессмысленно.
  • Индекс по ключу. ИНДЕКСИРОВАТЬ ПО КлючНабора на шаге 3 и КлючНабора, Период на шаге 4 — дешёвые индексы, которые реально используются в соединениях.

Как убедиться, что стало быстрее

Не принимайте решение «на глаз». Порядок, которым я пользуюсь:

  1. Замер на копии рабочей базы с реальным объёмом данных. На демо-базе любой вариант выполняется мгновенно.
  2. Время выполнения — через замер производительности конфигуратора или технологический журнал (события SDBL и DBMSSQL/DBPOSTGRS в зависимости от СУБД).
  3. План запроса на стороне СУБД: видно, превратилось ли тяжёлое соединение в соединение по индексу.
  4. Сравнение результатов старого и нового варианта: количество строк и выборочная сверка. Оптимизация, которая меняет результат, — это ошибка.

Интересное свойство приёма: время исходного запроса заметно растёт с каждым добавленным в условие полем, а у варианта с ключом тяжёлое соединение всегда идёт по двум полям, поэтому от ширины набора он зависит слабо.

Типичные ошибки

  • Нумерация до группировки. Если пронумеровать ВТЗаписи, а не ВТНаборы, у каждой записи будет свой ключ, и соединение по ключу ничего не найдёт.
  • NULL в измерениях. Сравнение NULL = NULL в соединении ложно. Если измерения могут быть NULL (например, после левого соединения на предыдущих шагах), замените их через ЕСТЬNULL до группировки.
  • Составные типы. Поле составного типа в условии соединения разворачивается СУБД в несколько колонок. Это ещё один аргумент за ключ, но на шаге 4 такие поля лучше привести к одному типу через ВЫРАЗИТЬ, если это допустимо по смыслу.
  • Приём там, где он не нужен. Соединение по одному индексированному полю ключ не ускорит. Начинайте с замера.
Медленное соединение по многим полям чаще всего лечится не индексами, а уменьшением числа полей в условии. АВТОНОМЕРЗАПИСИ — самый простой способ это сделать внутри одного пакета.

Если медленных запросов много и непонятно, с чего начать, правильнее идти от технологического журнала: найти самые тяжёлые запросы и чинить их по очереди. Такую работу я делаю в рамках аудита производительности 1С; отдельные отчёты переписываю как доработку или при разработке отчётов на СКД.

Итог

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