Пример оптимизации запроса

Загрузка номенклатуры в 1С:Розницу из другой базы (не 1С) с использованием веб-сервиса. При обычных условиях, все работало. Однако время обработки необъяснимо нелинейно увеличивалось при росте объема передаваемых данных. Разработали это задолго до моего прихода, кто - неизвестно...

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

Запрос = Новый Запрос();
Запрос.МенеджерВременныхТаблиц = мМенеджерВременныхТаблиц;
Запрос.Текст =    
"ВЫБРАТЬ
|	ТаблицаТоваров.Артикул КАК Артикул,
|	ТаблицаТоваров.Наименование,
|	ТаблицаТоваров.Размер,
|	ТаблицаТоваров.СтавкаНДС,
|	ТаблицаТоваров.УчетПоХарактеристикам,
|	ТаблицаТоваров.Штрихкод,
|	ТаблицаТоваров.Удалено,
|	ТаблицаТоваров.КодТовара КАК КодТовара,
|	ТаблицаТоваров.ПодарочныйСертификат,
|	ТаблицаТоваров.ОсобенностиУчетаНоменклатуры
|
|ПОМЕСТИТЬ ТаблицаТоваров
|
|ИЗ
|	&ТаблицаТоваров КАК ТаблицаТоваров
|
|ИНДЕКСИРОВАТЬ ПО
|	КодТовара";
Запрос.УстановитьПараметр("ТаблицаТоваров", ТаблицаДанных);
Запрос.Выполнить();

Запрос = Новый Запрос();
Запрос.МенеджерВременныхТаблиц = мМенеджерВременныхТаблиц;
Запрос.Текст =

"ВЫБРАТЬ
|	ТаблицаТоваров.Артикул КАК Артикул,
|	ТаблицаТоваров.Наименование КАК Наименование,
|	НоменклатураДополнительныеРеквизиты.Ссылка КАК Номенклатура,
|	ВЫБОР ТаблицаТоваров.СтавкаНДС
|		КОГДА ""10%""
|			ТОГДА ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.НДС10)
|		КОГДА ""Без НДС""
|			ТОГДА ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.БезНДС)
|		КОГДА ""0%""
|			ТОГДА ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.НДС0)
|		КОГДА ""18%""
|			ТОГДА ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.НДС18)
|		ИНАЧЕ ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.НДС20)
|	КОНЕЦ КАК СтавкаНДС,
|	ТаблицаТоваров.УчетПоХарактеристикам КАК УчетПоХарактеристикам,
|	ТаблицаТоваров.КодТовара КАК КодТовара,
|	ТаблицаТоваров.ПодарочныйСертификат КАК ПодарочныйСертификат,
|	ТаблицаТоваров.ОсобенностиУчетаНоменклатуры КАК ОсобенностиУчетаНоменклатуры
|ИЗ
|	(ВЫБРАТЬ
|		МАКСИМУМ(ТаблицаТоваров.Наименование) КАК Наименование,
|		МАКСИМУМ(ТаблицаТоваров.Артикул) КАК Артикул,
|		МАКСИМУМ(ТаблицаТоваров.СтавкаНДС) КАК СтавкаНДС,
|		МАКСИМУМ(ТаблицаТоваров.УчетПоХарактеристикам) КАК УчетПоХарактеристикам,
|		ТаблицаТоваров.КодТовара КАК КодТовара,
|		МАКСИМУМ(ТаблицаТоваров.ПодарочныйСертификат) КАК ПодарочныйСертификат,
|		МАКСИМУМ(ТаблицаТоваров.ОсобенностиУчетаНоменклатуры) КАК ОсобенностиУчетаНоменклатуры
|	ИЗ
|		ТаблицаТоваров КАК ТаблицаТоваров
|	ГДЕ
|		НЕ ТаблицаТоваров.Удалено
|	
|	СГРУППИРОВАТЬ ПО
|		ТаблицаТоваров.КодТовара) КАК ТаблицаТоваров
|		ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура.ДополнительныеРеквизиты КАК НоменклатураДополнительныеРеквизиты
|		ПО ТаблицаТоваров.КодТовара = НоменклатураДополнительныеРеквизиты.Значение
|			И (НоменклатураДополнительныеРеквизиты.Свойство = &Свойство)";

Запрос.УстановитьПараметр("Свойство", ПланыВидовХарактеристик.ДополнительныеРеквизитыИСведения.НайтиПоРеквизиту("Имя","КодИнтеграции_Номенклатура"));

Выборка = Запрос.Выполнить().Выбрать();

Что бросается в глаза:

Странная группировка по коду товара с поиском максимумов нечисловых полей. Но это сделано просто, чтобы схлопнуть разрез по размерам (характеристикам), который сейчас не нужен, в общем это не ошибка.
Ужасный архитектурный просчет. Идентификатор номенклатуры внешней базы хранится в табличной части дополнительных реквизитов.
При этом тип данных реквизита "Значение" является составным, включая как примитивные, так и ссылочные типы. Хотя в нашем случае хранятся идентификаторы, то есть строки, приведение типа в запросе не выполняется.
Если уж автор индексирует ключевое поле временной таблицы загружаемых данных, которая в сущности небольшая, то почему бы не попробовать сделать то же самое с большой таблицей дополнительных реквизитов перед соединением.

Исправив эти вполне очевидные недочеты, даже без изменения архитектуры, получаем увеличение производительности на неких контрольных данных с двух часов до нескольких секунд. Отдельные запросы я не объединил в пакет, чтобы между ними вставлять журналирование времени работы. Группировку и соединение я тоже на всякий случай разделил, больше даже для наглядности. Вот что получилось в итоге...

Запрос = Новый Запрос();
Запрос.МенеджерВременныхТаблиц = мМенеджерВременныхТаблиц;
Запрос.Текст =    
"ВЫБРАТЬ
|	ТаблицаТоваров.Артикул КАК Артикул,
|	ТаблицаТоваров.Наименование,
|	ТаблицаТоваров.Размер,
|	ТаблицаТоваров.СтавкаНДС,
|	ТаблицаТоваров.УчетПоХарактеристикам,
|	ТаблицаТоваров.Штрихкод,
|	ТаблицаТоваров.Удалено,
|	ТаблицаТоваров.КодТовара КАК КодТовара,
|	ТаблицаТоваров.ПодарочныйСертификат,
|	ТаблицаТоваров.ОсобенностиУчетаНоменклатуры
|
|ПОМЕСТИТЬ ТаблицаТоваров
|
|ИЗ
|	&ТаблицаТоваров КАК ТаблицаТоваров
|";
Запрос.УстановитьПараметр("ТаблицаТоваров", ТаблицаДанных);
Запрос.Выполнить();


Запрос = Новый Запрос();
Запрос.МенеджерВременныхТаблиц = мМенеджерВременныхТаблиц;
Запрос.Текст =

"ВЫБРАТЬ
|	ТаблицаТоваров.Артикул КАК Артикул,
|	ТаблицаТоваров.Наименование КАК Наименование,
|	ВЫБОР ТаблицаТоваров.СтавкаНДС
|		КОГДА ""10%""
|			ТОГДА ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.НДС10)
|		КОГДА ""Без НДС""
|			ТОГДА ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.БезНДС)
|		КОГДА ""0%""
|			ТОГДА ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.НДС0)
|		КОГДА ""18%""
|			ТОГДА ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.НДС18)
|		ИНАЧЕ ЗНАЧЕНИЕ(Перечисление.СтавкиНДС.НДС20)
|	КОНЕЦ КАК СтавкаНДС,
|	ТаблицаТоваров.УчетПоХарактеристикам КАК УчетПоХарактеристикам,
|	ТаблицаТоваров.КодТовара КАК КодТовара,
|	ТаблицаТоваров.ПодарочныйСертификат КАК ПодарочныйСертификат,
|	ТаблицаТоваров.ОсобенностиУчетаНоменклатуры КАК ОсобенностиУчетаНоменклатуры
|
|ПОМЕСТИТЬ ТаблицаТоваровСвернутая
|
|ИЗ
|	(ВЫБРАТЬ
|		МАКСИМУМ(ТаблицаТоваров.Наименование) КАК Наименование,
|		МАКСИМУМ(ТаблицаТоваров.Артикул) КАК Артикул,
|		МАКСИМУМ(ТаблицаТоваров.СтавкаНДС) КАК СтавкаНДС,
|		МАКСИМУМ(ТаблицаТоваров.УчетПоХарактеристикам) КАК УчетПоХарактеристикам,
|		ТаблицаТоваров.КодТовара КАК КодТовара,
|		МАКСИМУМ(ТаблицаТоваров.ПодарочныйСертификат) КАК ПодарочныйСертификат,
|		МАКСИМУМ(ТаблицаТоваров.ОсобенностиУчетаНоменклатуры) КАК ОсобенностиУчетаНоменклатуры
|	ИЗ
|		ТаблицаТоваров КАК ТаблицаТоваров
|	ГДЕ
|		НЕ ТаблицаТоваров.Удалено
|	
|	СГРУППИРОВАТЬ ПО
|		ТаблицаТоваров.КодТовара) КАК ТаблицаТоваров
|ИНДЕКСИРОВАТЬ ПО КодТовара";
|";
Запрос.Выполнить();	


Запрос = Новый Запрос();
Запрос.МенеджерВременныхТаблиц = мМенеджерВременныхТаблиц;
Запрос.Текст =

"ВЫБРАТЬ
|	ТаблицаДопРеквизитов.Ссылка КАК Ссылка,
|	Выразить(ТаблицаДопРеквизитов.Значение КАК Строка(25)) КАК КодИнтеграции
|
|ПОМЕСТИТЬ ТаблицаДопРеквизитов
|
|ИЗ Справочник.Номенклатура.ДополнительныеРеквизиты КАК ТаблицаДопРеквизитов
|ИНДЕКСИРОВАТЬ ПО КодИнтеграции
|";
Запрос.Выполнить();	


Запрос = Новый Запрос();
Запрос.МенеджерВременныхТаблиц = мМенеджерВременныхТаблиц;
Запрос.Текст =

"ВЫБРАТЬ
|	ТаблицаТоваровСвернутая.Артикул КАК Артикул,
|	ТаблицаТоваровСвернутая.Наименование КАК Наименование,
|	ТаблицаДопРеквизитов.Ссылка КАК Номенклатура,
|	ТаблицаТоваровСвернутая.СтавкаНДС КАК СтавкаНДС,
|	ТаблицаТоваровСвернутая.УчетПоХарактеристикам КАК УчетПоХарактеристикам,
|	ТаблицаТоваровСвернутая.КодТовара КАК КодТовара,
|	ТаблицаТоваровСвернутая.ПодарочныйСертификат КАК ПодарочныйСертификат,
|	ТаблицаТоваровСвернутая.ОсобенностиУчетаНоменклатуры КАК ОсобенностиУчетаНоменклатуры
|ИЗ
|	ТаблицаТоваровСвернутая КАК ТаблицаТоваровСвернутая
|		ЛЕВОЕ СОЕДИНЕНИЕ (Выбрать * из ТаблицаДопРеквизитов) КАК ТаблицаДопРеквизитов
|		ПО ТаблицаТоваровСвернутая.КодТовара = ТаблицаДопРеквизитов.КодИнтеграции
|";

Выборка = Запрос.Выполнить().Выбрать();

Использование специального инструментария для анализа планов запросов часто является избыточным. Достаточно просто соблюдать несложные логичные принципы.

Нужно ли всегда тестировать масштабирование? Я считаю, что нет. Например, если запрос поиска среди 5 тысяч контрагентов работает быстро, а мы понимаем, что большой рост не предвидится, то в тестировании надуманного масштабирования нет необходимости. А вот если ежедневная загрузка номенклатуры 10-20 позиций идет быстро, то нужно обязательно проверить, что будет, если в базе источнике внесут массовые изменения и на загрузку поступит объем в 10-20 тысяч элементов.

←5 | заметка 6 | 7→