Top.Mail.Ru
Full-time, 5/2
Формат: удаленный
Вакансия «1С-программист»
Подробнее
Menu

Оптимизация запроса с помощью псевдоключа сформированного функцией АВТОНОМЕРЗАПИСИ()

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

Рассмотрим первоначальную таблицу

Пример синтетический, для тестирования я создал периодический регистр сведений с десятью строковыми измерениями и заполнил его ~30 000 записей. Исходная таблица выглядит так:

ВЫБРАТЬ
	Расш1_ТестовыйРегистр.Период КАК Период,
	Расш1_ТестовыйРегистр.Измерение1 КАК Измерение1,
	Расш1_ТестовыйРегистр.Измерение2 КАК Измерение2,
	Расш1_ТестовыйРегистр.Измерение3 КАК Измерение3,
	Расш1_ТестовыйРегистр.Измерение4 КАК Измерение4,
	Расш1_ТестовыйРегистр.Измерение5 КАК Измерение5,
	Расш1_ТестовыйРегистр.Измерение6 КАК Измерение6,
	Расш1_ТестовыйРегистр.Измерение7 КАК Измерение7,
	Расш1_ТестовыйРегистр.Измерение8 КАК Измерение8,
	Расш1_ТестовыйРегистр.Измерение9 КАК Измерение9,
	Расш1_ТестовыйРегистр.Измерение10 КАК Измерение10
ПОМЕСТИТЬ ВТПериодыПоМесяцам
ИЗ
	РегистрСведений.Расш1_ТестовыйРегистр КАК Расш1_ТестовыйРегистр
ГДЕ
	Расш1_ТестовыйРегистр.Период < &ДатаОкончания

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

ВЫБРАТЬ
	ВТПериодыПоМесяцам.Измерение1 КАК Измерение1,
	ВТПериодыПоМесяцам.Измерение2 КАК Измерение2,
	ВТПериодыПоМесяцам.Измерение3 КАК Измерение3,
	ВТПериодыПоМесяцам.Измерение4 КАК Измерение4,
	ВТПериодыПоМесяцам.Измерение5 КАК Измерение5,
	ВТПериодыПоМесяцам.Измерение6 КАК Измерение6,
	ВТПериодыПоМесяцам.Измерение7 КАК Измерение7,
	ВТПериодыПоМесяцам.Измерение8 КАК Измерение8,
	ВТПериодыПоМесяцам.Измерение9 КАК Измерение9,
	ВТПериодыПоМесяцам.Измерение10 КАК Измерение10
ПОМЕСТИТЬ ВТИзмерений
ИЗ
	ВТПериодыПоМесяцам КАК ВТПериодыПоМесяцам

СГРУППИРОВАТЬ ПО
	ВТПериодыПоМесяцам.Измерение1,
	ВТПериодыПоМесяцам.Измерение2,
	ВТПериодыПоМесяцам.Измерение3,
	ВТПериодыПоМесяцам.Измерение4,
	ВТПериодыПоМесяцам.Измерение5,
	ВТПериодыПоМесяцам.Измерение6,
	ВТПериодыПоМесяцам.Измерение7,
	ВТПериодыПоМесяцам.Измерение8,
	ВТПериодыПоМесяцам.Измерение9,
	ВТПериодыПоМесяцам.Измерение10

С помощью функции АВТОНОМЕРЗАПИСИ() добавляем ключ набора:

ВЫБРАТЬ
	АВТОНОМЕРЗАПИСИ() КАК КлючЗаписи,
	ВТИзмерений.Измерение1 КАК Измерение1,
	ВТИзмерений.Измерение2 КАК Измерение2,
	ВТИзмерений.Измерение3 КАК Измерение3,
	ВТИзмерений.Измерение4 КАК Измерение4,
	ВТИзмерений.Измерение5 КАК Измерение5,
	ВТИзмерений.Измерение6 КАК Измерение6,
	ВТИзмерений.Измерение7 КАК Измерение7,
	ВТИзмерений.Измерение8 КАК Измерение8,
	ВТИзмерений.Измерение9 КАК Измерение9,
	ВТИзмерений.Измерение10 КАК Измерение10
ПОМЕСТИТЬ ВТКлючевыеИзмерения
ИЗ
	ВТИзмерений КАК ВТИзмерений

Помещаем ключ в исходную таблицу:

ВЫБРАТЬ
	ВТКлючевыеИзмерения.КлючЗаписи КАК КлючЗаписи,
	ВТПериодыПоМесяцам.Период КАК Период
ПОМЕСТИТЬ ВТКлючиСПериодами
ИЗ
	ВТКлючевыеИзмерения КАК ВТКлючевыеИзмерения
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТПериодыПоМесяцам КАК ВТПериодыПоМесяцам
		ПО (ВТКлючевыеИзмерения.Измерение1 = ВТПериодыПоМесяцам.Измерение1)
			И (ВТКлючевыеИзмерения.Измерение2 = ВТПериодыПоМесяцам.Измерение2)
			И (ВТКлючевыеИзмерения.Измерение3 = ВТПериодыПоМесяцам.Измерение3)
			И (ВТКлючевыеИзмерения.Измерение4 = ВТПериодыПоМесяцам.Измерение4)
			И (ВТКлючевыеИзмерения.Измерение5 = ВТПериодыПоМесяцам.Измерение5)
			И (ВТКлючевыеИзмерения.Измерение6 = ВТПериодыПоМесяцам.Измерение6)
			И (ВТКлючевыеИзмерения.Измерение7 = ВТПериодыПоМесяцам.Измерение7)
			И (ВТКлючевыеИзмерения.Измерение8 = ВТПериодыПоМесяцам.Измерение8)
			И (ВТКлючевыеИзмерения.Измерение9 = ВТПериодыПоМесяцам.Измерение9)
			И (ВТКлючевыеИзмерения.Измерение10 = ВТПериодыПоМесяцам.Измерение10)

Получившуюся таблицу соединяем уже по ключу, в данном случае получим периоды наших наборов:

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

СГРУППИРОВАТЬ ПО
	ВТКлючиСПериодами.Период,
	ВТКлючиСПериодами.КлючЗаписи

Возвращаем наборы:

ВЫБРАТЬ
	ВТКлючевыеИзмерения.Измерение1 КАК Измерение1,
	ВТКлючевыеИзмерения.Измерение2 КАК Измерение2,
	ВТКлючевыеИзмерения.Измерение3 КАК Измерение3,
	ВТКлючевыеИзмерения.Измерение4 КАК Измерение4,
	ВТКлючевыеИзмерения.Измерение5 КАК Измерение5,
	ВТКлючевыеИзмерения.Измерение6 КАК Измерение6,
	ВТКлючевыеИзмерения.Измерение7 КАК Измерение7,
	ВТКлючевыеИзмерения.Измерение8 КАК Измерение8,
	ВТКлючевыеИзмерения.Измерение9 КАК Измерение9,
	ВТКлючевыеИзмерения.Измерение10 КАК Измерение10,
	ВТРезультатСКлючами.ДатаНачала КАК ДатаНачала,
	ВТРезультатСКлючами.ДатаОкончания КАК ДатаОкончания
ИЗ
	ВТРезультатСКлючами КАК ВТРезультатСКлючами
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТКлючевыеИзмерения КАК ВТКлючевыеИзмерения
		ПО ВТРезультатСКлючами.КлючЗаписи = ВТКлючевыеИзмерения.КлючЗаписи

Пример синтетический и интересен тем, что можно создать несложный запрос и, меняя количество выбранных измерений, посмотреть увеличение скорости работы запроса.

Исходный запрос, в котором объединяются все поля

ВЫБРАТЬ
	Расш1_ТестовыйРегистр.Период КАК Период,
	Расш1_ТестовыйРегистр.Измерение1 КАК Измерение1,
	Расш1_ТестовыйРегистр.Измерение2 КАК Измерение2,
	Расш1_ТестовыйРегистр.Измерение3 КАК Измерение3,
	Расш1_ТестовыйРегистр.Измерение4 КАК Измерение4,
	Расш1_ТестовыйРегистр.Измерение5 КАК Измерение5,
	Расш1_ТестовыйРегистр.Измерение6 КАК Измерение6,
	Расш1_ТестовыйРегистр.Измерение7 КАК Измерение7,
	Расш1_ТестовыйРегистр.Измерение8 КАК Измерение8,
	Расш1_ТестовыйРегистр.Измерение9 КАК Измерение9,
	Расш1_ТестовыйРегистр.Измерение10 КАК Измерение10
ПОМЕСТИТЬ ВТПериодыПоМесяцам
ИЗ
	РегистрСведений.Расш1_ТестовыйРегистр КАК Расш1_ТестовыйРегистр
ГДЕ
	Расш1_ТестовыйРегистр.Период < &ДатаОкончания

ИНДЕКСИРОВАТЬ ПО
	Измерение1,
	Измерение2,
	Измерение3,
	Измерение4,
	Измерение5,
	Измерение6,
	Измерение7,
	Измерение8,
	Измерение9,
	Измерение10
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ВТДанные.Период КАК ДатаНачала,
	МИНИМУМ(ЕСТЬNULL(ВТДанные1.Период, &ДатаОкончания)) КАК ДатаОкончания,
	ВТДанные.Измерение1 КАК Измерение1,
	ВТДанные.Измерение2 КАК Измерение2,
	ВТДанные.Измерение3 КАК Измерение3,
	ВТДанные.Измерение4 КАК Измерение4,
	ВТДанные.Измерение5 КАК Измерение5,
	ВТДанные.Измерение6 КАК Измерение6,
	ВТДанные.Измерение7 КАК Измерение7,
	ВТДанные.Измерение8 КАК Измерение8,
	ВТДанные.Измерение9 КАК Измерение9,
	ВТДанные.Измерение10 КАК Измерение10
ИЗ
	ВТПериодыПоМесяцам КАК ВТДанные
		ЛЕВОЕ СОЕДИНЕНИЕ ВТПериодыПоМесяцам КАК ВТДанные1
		ПО (ВТДанные.Период < ВТДанные1.Период
				И ВТДанные.Измерение1 = ВТДанные1.Измерение1
				И ВТДанные.Измерение2 = ВТДанные1.Измерение2
				И ВТДанные.Измерение3 = ВТДанные1.Измерение3
				И ВТДанные.Измерение4 = ВТДанные1.Измерение4
				И ВТДанные.Измерение5 = ВТДанные1.Измерение5
				И ВТДанные.Измерение6 = ВТДанные1.Измерение6
				И ВТДанные.Измерение7 = ВТДанные1.Измерение7
				И ВТДанные.Измерение8 = ВТДанные1.Измерение8
				И ВТДанные.Измерение9 = ВТДанные1.Измерение9
				И ВТДанные.Измерение10 = ВТДанные1.Измерение10)

СГРУППИРОВАТЬ ПО
	ВТДанные.Период,
	ВТДанные.Измерение1,
	ВТДанные.Измерение2,
	ВТДанные.Измерение3,
	ВТДанные.Измерение4,
	ВТДанные.Измерение5,
	ВТДанные.Измерение6,
	ВТДанные.Измерение7,
	ВТДанные.Измерение8,
	ВТДанные.Измерение9,
	ВТДанные.Измерение10

Оптимизированный запрос:

ВЫБРАТЬ
	Расш1_ТестовыйРегистр.Период КАК Период,
	Расш1_ТестовыйРегистр.Измерение1 КАК Измерение1,
	Расш1_ТестовыйРегистр.Измерение2 КАК Измерение2,
	Расш1_ТестовыйРегистр.Измерение3 КАК Измерение3,
	Расш1_ТестовыйРегистр.Измерение4 КАК Измерение4,
	Расш1_ТестовыйРегистр.Измерение5 КАК Измерение5,
	Расш1_ТестовыйРегистр.Измерение6 КАК Измерение6,
	Расш1_ТестовыйРегистр.Измерение7 КАК Измерение7,
	Расш1_ТестовыйРегистр.Измерение8 КАК Измерение8,
	Расш1_ТестовыйРегистр.Измерение9 КАК Измерение9,
	Расш1_ТестовыйРегистр.Измерение10 КАК Измерение10
ПОМЕСТИТЬ ВТПериодыПоМесяцам
ИЗ
	РегистрСведений.Расш1_ТестовыйРегистр КАК Расш1_ТестовыйРегистр
ГДЕ
	Расш1_ТестовыйРегистр.Период < &ДатаОкончания

ИНДЕКСИРОВАТЬ ПО
	Измерение1,
	Измерение2,
	Измерение3,
	Измерение4,
	Измерение5,
	Измерение6,
	Измерение7,
	Измерение8,
	Измерение9,
	Измерение10
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ВТПериодыПоМесяцам.Измерение1 КАК Измерение1,
	ВТПериодыПоМесяцам.Измерение2 КАК Измерение2,
	ВТПериодыПоМесяцам.Измерение3 КАК Измерение3,
	ВТПериодыПоМесяцам.Измерение4 КАК Измерение4,
	ВТПериодыПоМесяцам.Измерение5 КАК Измерение5,
	ВТПериодыПоМесяцам.Измерение6 КАК Измерение6,
	ВТПериодыПоМесяцам.Измерение7 КАК Измерение7,
	ВТПериодыПоМесяцам.Измерение8 КАК Измерение8,
	ВТПериодыПоМесяцам.Измерение9 КАК Измерение9,
	ВТПериодыПоМесяцам.Измерение10 КАК Измерение10
ПОМЕСТИТЬ ВТИзмерений
ИЗ
	ВТПериодыПоМесяцам КАК ВТПериодыПоМесяцам

СГРУППИРОВАТЬ ПО
	ВТПериодыПоМесяцам.Измерение1,
	ВТПериодыПоМесяцам.Измерение2,
	ВТПериодыПоМесяцам.Измерение3,
	ВТПериодыПоМесяцам.Измерение4,
	ВТПериодыПоМесяцам.Измерение5,
	ВТПериодыПоМесяцам.Измерение6,
	ВТПериодыПоМесяцам.Измерение7,
	ВТПериодыПоМесяцам.Измерение8,
	ВТПериодыПоМесяцам.Измерение9,
	ВТПериодыПоМесяцам.Измерение10
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	АВТОНОМЕРЗАПИСИ() КАК КлючЗаписи,
	ВТИзмерений.Измерение1 КАК Измерение1,
	ВТИзмерений.Измерение2 КАК Измерение2,
	ВТИзмерений.Измерение3 КАК Измерение3,
	ВТИзмерений.Измерение4 КАК Измерение4,
	ВТИзмерений.Измерение5 КАК Измерение5,
	ВТИзмерений.Измерение6 КАК Измерение6,
	ВТИзмерений.Измерение7 КАК Измерение7,
	ВТИзмерений.Измерение8 КАК Измерение8,
	ВТИзмерений.Измерение9 КАК Измерение9,
	ВТИзмерений.Измерение10 КАК Измерение10
ПОМЕСТИТЬ ВТКлючевыеИзмерения
ИЗ
	ВТИзмерений КАК ВТИзмерений

ИНДЕКСИРОВАТЬ ПО
	КлючЗаписи,
	Измерение1,
	Измерение2,
	Измерение3,
	Измерение4,
	Измерение5,
	Измерение6,
	Измерение7,
	Измерение8,
	Измерение9,
	Измерение10
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ВТКлючевыеИзмерения.КлючЗаписи КАК КлючЗаписи,
	ВТПериодыПоМесяцам.Период КАК Период
ПОМЕСТИТЬ ВТКлючиСПериодами
ИЗ
	ВТКлючевыеИзмерения КАК ВТКлючевыеИзмерения
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТПериодыПоМесяцам КАК ВТПериодыПоМесяцам
		ПО (ВТКлючевыеИзмерения.Измерение1 = ВТПериодыПоМесяцам.Измерение1)
			И (ВТКлючевыеИзмерения.Измерение2 = ВТПериодыПоМесяцам.Измерение2)
			И (ВТКлючевыеИзмерения.Измерение3 = ВТПериодыПоМесяцам.Измерение3)
			И (ВТКлючевыеИзмерения.Измерение4 = ВТПериодыПоМесяцам.Измерение4)
			И (ВТКлючевыеИзмерения.Измерение5 = ВТПериодыПоМесяцам.Измерение5)
			И (ВТКлючевыеИзмерения.Измерение6 = ВТПериодыПоМесяцам.Измерение6)
			И (ВТКлючевыеИзмерения.Измерение7 = ВТПериодыПоМесяцам.Измерение7)
			И (ВТКлючевыеИзмерения.Измерение8 = ВТПериодыПоМесяцам.Измерение8)
			И (ВТКлючевыеИзмерения.Измерение9 = ВТПериодыПоМесяцам.Измерение9)
			И (ВТКлючевыеИзмерения.Измерение10 = ВТПериодыПоМесяцам.Измерение10)
;

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

СГРУППИРОВАТЬ ПО
	ВТКлючиСПериодами.Период,
	ВТКлючиСПериодами.КлючЗаписи
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ВТКлючевыеИзмерения.Измерение1 КАК Измерение1,
	ВТКлючевыеИзмерения.Измерение2 КАК Измерение2,
	ВТКлючевыеИзмерения.Измерение3 КАК Измерение3,
	ВТКлючевыеИзмерения.Измерение4 КАК Измерение4,
	ВТКлючевыеИзмерения.Измерение5 КАК Измерение5,
	ВТКлючевыеИзмерения.Измерение6 КАК Измерение6,
	ВТКлючевыеИзмерения.Измерение7 КАК Измерение7,
	ВТКлючевыеИзмерения.Измерение8 КАК Измерение8,
	ВТКлючевыеИзмерения.Измерение9 КАК Измерение9,
	ВТКлючевыеИзмерения.Измерение10 КАК Измерение10,
	ВТРезультатСКлючами.ДатаНачала КАК ДатаНачала,
	ВТРезультатСКлючами.ДатаОкончания КАК ДатаОкончания
ИЗ
	ВТРезультатСКлючами КАК ВТРезультатСКлючами
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТКлючевыеИзмерения КАК ВТКлючевыеИзмерения
		ПО ВТРезультатСКлючами.КлючЗаписи = ВТКлючевыеИзмерения.КлючЗаписи

Постепенно уменьшая количество выбранных измерений, можно посмотреть динамику ускорения в секундах:

Или в виде диаграммы:

Количество измерений существенно не влияет на нашу оптимизацию, на первоначальный запрос влияет значительно.

Похожие материалы

https://infostart.ru/1c/articles/1328254/
https://infostart.ru/1c/articles/1903939/

Comments

So empty here ... leave a comment!

Добавить комментарий

Last posts

Sidebar