При разработке и доработке конфигураций семейства 1С:ERP, Управление торговлей 11 и Комплексная автоматизация разработчики часто сталкиваются с ситуацией, когда простейший запрос с оператором ПЕРВЫЕ 1 выполняются не доли секунды, а несколько минут. Разберем типичную проблему на примере обращения к регистру накопления РегистрНакопления.СебестоимостьТоваров, выясним технические причины падения производительности в СУБД и рассмотрим правильные варианты оптимизации.
Рассмотрим исходный запрос, выполнение которого занимает около 5 минут в рабочей базе:
Запрос = Новый Запрос;
Запрос.УстановитьПараметр("Номенклатура", Номенклатура);
Запрос.УстановитьПараметр("ВидДвижения", ВидДвиженияНакопления.Приход);
Запрос.Текст = "ВЫБРАТЬ ПЕРВЫЕ 1
| СебестоимостьТоваров.Период,
| СебестоимостьТоваров.Количество,
| СебестоимостьТоваров.Стоимость
|ИЗ
| РегистрНакопления.СебестоимостьТоваров КАК СебестоимостьТоваров
|ГДЕ
| СебестоимостьТоваров.АналитикаУчетаНоменклатуры.Номенклатура = &Номенклатура
| И СебестоимостьТоваров.ВидДвижения = &ВидДвижения
|
|УПОРЯДОЧИТЬ ПО
| СебестоимостьТоваров.Период УБЫВ";
РезультатСебестоимость = Запрос.Выполнить().Выгрузить();
На первый взгляд запрос кажется простым: мы выбираем первую запись с сортировкой по убыванию даты. Однако на уровне СУБД данный код порождает серьезные проблемы с производительностью.
Выясним главные причины замедления:
СебестоимостьТоваров.АналитикаУчетаНоменклатуры.Номенклатура заставляет платформу генерировать неявное левое соединение (LEFT OUTER JOIN) с таблицей ключей аналитики учета номенклатуры.АналитикаУчетаНоменклатуры, но не по полю Номенклатура справочника ключей. Из-за неявного соединения оптимизатор СУБД не может применить индекс с порядком Измерение + Период. Вместо быстрых точечных поисков (операция Index Seek) СУБД вынуждена выполнять полный просмотр огромной таблицы регистра (Table Scan или Index Scan), соединять строки, а затем сортировать огромный массив данных в памяти или во временной базе (TempDB).КлючиАналитикиУчетаНоменклатуры. Одной позиции номенклатуры может соответствовать несколько элементов аналитики (с разным сочетанием характеристик, складов, серий или назначений).Если в задаче требуется найти движения для конкретной комбинации параметров или если аналитику можно получить заранее, самое простое решение — передавать в запрос прямую ссылку на ключ аналитики. В этом случае СУБД задействует составной индекс физической таблицы регистра.
Проанализируем пример кода с предварительным поиском ссылки на аналитику:
// Вначале получаем нужный элемент аналитики учета номенклатуры
КлючАналитики = Справочники.КлючиАналитикиУчетаНоменклатуры.СформироватьКлючАналитикиУчетаНоменклатуры(Номенклатура);
Запрос = Новый Запрос;
Запрос.УстановитьПараметр("АналитикаУчетаНоменклатуры", КлючАналитики);
Запрос.УстановитьПараметр("ВидДвижения", ВидДвиженияНакопления.Приход);
Запрос.Текст = "ВЫБРАТЬ ПЕРВЫЕ 1
| СебестоимостьТоваров.Период,
| СебестоимостьТоваров.Количество,
| СебестоимостьТоваров.Стоимость
|ИЗ
| РегистрНакопления.СебестоимостьТоваров КАК СебестоимостьТоваров
|ГДЕ
| СебестоимостьТоваров.АналитикаУчетаНоменклатуры = &АналитикаУчетаНоменклатуры
| И СебестоимостьТоваров.ВидДвижения = &ВидДвижения
|
|УПОРЯДОЧИТЬ ПО
| СебестоимостьТоваров.Период УБЫВ";
При таком подходе запрос отработает мгновенно, так как условие СебестоимостьТоваров.АналитикаУчетаНоменклатуры = &АналитикаУчетаНоменклатуры полностью совпадает с первыми полями индекса регистра.
Если для одной номенклатуры в системе существует несколько ключей аналитики (например, разный складской учет или разные характеристики), передача одной ссылки может дать неполный результат. В таком случае разберем стандартный типовой механизм с использованием временной таблицы.
Схема оптимизации по шагам:
ВНУТРЕННЕЕ СОЕДИНЕНИЕ временной таблицы с регистром накопления.Рассмотрим реализацию данного метода:
Запрос = Новый Запрос;
Запрос.УстановитьПараметр("Номенклатура", Номенклатура);
Запрос.УстановитьПараметр("ВидДвижения", ВидДвиженияНакопления.Приход);
Запрос.Текст = "ВЫБРАТЬ
| Ключи.Ссылка КАК АналитикаУчетаНоменклатуры
|ПОМЕСТИТЬ ВТ_Аналитика
|ИЗ
| Справочник.КлючиАналитикиУчетаНоменклатуры КАК Ключи
|ГДЕ
| Ключи.Номенклатура = &Номенклатура
|
|ИНДЕКСИРОВАТЬ ПО
| АналитикаУчетаНоменклатуры
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ ПЕРВЫЕ 1
| СебестоимостьТоваров.Период,
| СебестоимостьТоваров.Количество,
| СебестоимостьТоваров.Стоимость
|ИЗ
| ВТ_Аналитика КАК ВТ_Аналитика
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.СебестоимостьТоваров КАК СебестоимостьТоваров
| ПО ВТ_Аналитика.АналитикаУчетаНоменклатуры = СебестоимостьТоваров.АналитикаУчетаНоменклатуры
|ГДЕ
| СебестоимостьТоваров.ВидДвижения = &ВидДвижения
|
|УПОРЯДОЧИТЬ ПО
| СебестоимостьТоваров.Период УБЫВ";
Результат = Запрос.Выполнить().Выгрузить();
Почему это работает быстрее: Замена неявного соединения на явное соединение с проиндексированной временной таблицей заставляет оптимизатор СУБД использовать соединение через вложенные циклы (Nested Loops). СУБД берет найденные ключи из маленькой временной таблицы и выполняет точечные обращения по индексу (Index Seek) к физической таблице регистра, полностью избегая полного сканирования таблицы.
Если конечной целью является получение агрегированных данных за определенный период времени, а не конкретная физическая запись движения, рекомендуется обращаться к виртуальным таблицам (например, Обороты или ОстаткиИОбороты).
Свойства виртуальных таблиц:
ГДЕ.Главная рекомендация при работе с конфигурациями, использующими ключи аналитики (УТ 11, ERP, КА): никогда не обращайтесь к свойствам аналитики через точку в условиях отбора физических таблиц регистров. Используйте предварительное формирование списка аналитик во временную таблицу с индексацией либо передавайте готовую ссылку в параметр запроса.