При разработке отчетов и выборок в 1С часто возникает типовая бизнес-задача: получить ограниченное количество последних документов для каждого контрагента, участника или номенклатуры (например, 3 последних заказа клиента). Нередко разработчики реализуют это через коррелированный подзапрос в секции соединения, однако при добавлении отборов по признакам проведения, удаления или статуса такой запрос начинает катастрофически замедляться. Разберем детально, почему это происходит и как правильно оптимизировать выборку.
Посмотрим на исходный запрос, вызывающий сложности с производительностью:
ВЫБРАТЬ РАЗЛИЧНЫЕ
Участники.Участник КАК Участник,
ЗаказТураУчастники.Ссылка КАК Ссылка
ИЗ
Участники КАК Участники
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказТура.Участники КАК ЗаказТураУчастники
ПО (ЗаказТураУчастники.Ссылка В
(ВЫБРАТЬ ПЕРВЫЕ 3
ЗаказТураУчастникиУ.Ссылка
ИЗ
Документ.ЗаказТура.Участники КАК ЗаказТураУчастникиУ
ГДЕ
ЗаказТураУчастникиУ.Участник = Участники.Участник
УПОРЯДОЧИТЬ ПО
ЗаказТураУчастникиУ.Ссылка.Дата УБЫВ))
ГДЕ
ЗаказТураУчастники.Ссылка.Проведен = ИСТИНА
И ЗаказТураУчастники.Ссылка.Аннулирован = ЛОЖЬ
И ЗаказТураУчастники.Ссылка.ПометкаУдаления = ЛОЖЬ
Проанализируем механику выполнения этого запроса на уровне платформы и СУБД:
В (ВЫБРАТЬ ПЕРВЫЕ 3... ГДЕ ЗаказТураУчастникиУ.Участник = Участники.Участник) представляет собой коррелированный подзапрос. СУБД не может выполнить его единожды: подзапрос принудительно рассчитывается заново для каждой строки или группы строк внешней таблицы. Если в таблице тысячи участников, СУБД выполнит тысячи тяжелых отдельных выборок.УПОРЯДОЧИТЬ ПО ЗаказТураУчастникиУ.Ссылка.Дата УБЫВ заставляет СУБД производить физическую операцию сортировки на каждой итерации обхода участников, что приводит к колоссальной нагрузке на процессор и временные таблицы СУБД (TempDB).Ссылка.Дата из таблицы табличной части приводит к скрытому левому соединению с таблицей шапки документа. Поскольку реквизиты табличной части и шапки физически разделены, единый составной индекс построить невозможно, и СУБД сканирует таблицы без использования эффективных индексов.ГДЕ основного запроса накладываются на результат уже вычисленного соединения. СУБД сначала пытается связать и отсортировать огромный массив данных, а затем отфильтровать его. Кроме того, проверка Ссылка.Проведен = ИСТИНА и Ссылка.ПометкаУдаления = ЛОЖЬ одновременно является избыточной: проведенный документ по логике конфигураций 1С не может иметь пометку на удаление.Если задача решается в рамках Системы компоновки данных, лучшим решением будет полностью отказаться от вложенных подзапросов с ограничением ПЕРВЫЕ в тексте запроса и переложить ограничение на движок компоновки. Разберем по шагам, как это настраивается:
Участник, Ссылка и Дата, наложив базовые отборы по проведению.Участник.Дата с направлением Убыв.При таком подходе запрос к СУБД выполняется за один быстрый проход, а платформа 1С на уровне вывода компоновки автоматически оставляет только первые 3 записи для каждого участника.
Для процедур и функций модулей, где компоновка данных не используется, наиболее производительный вариант — плоский запрос с последующим отбором в коде. Выясним причину: СУБД способна мгновенно отдать отсортированные данные по индексам, а отсечение лишних записей в оперативной памяти сервера занимает доли секунды.
Посмотрим на пример запроса:
ВЫБРАТЬ
ЗаказТураУчастники.Участник КАК Участник,
ЗаказТура.Ссылка КАК Ссылка
ИЗ
Документ.ЗаказТура КАК ЗаказТура
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ЗаказТура.Участники КАК ЗаказТураУчастники
ПО ЗаказТура.Ссылка = ЗаказТураУчастники.Ссылка
ГДЕ
ЗаказТура.Проведен = ИСТИНА
И ЗаказТура.Аннулирован = ЛОЖЬ
УПОРЯДОЧИТЬ ПО
ЗаказТураУчастники.Участник,
ЗаказТура.Дата УБЫВ
Далее обработаем результат запроса в цикле выборки:
Выборка = Запрос.Выполнить().Выбрать();
ТекущийУчастник = Неопределено;
Счетчик = 0;
Пока Выборка.Следующий() Цикл
Если Выборка.Участник <> ТекущийУчастник Тогда
ТекущийУчастник = Выборка.Участник;
Счетчик = 0;
КонецЕсли;
Если Счетчик >= 3 Тогда
Продолжить;
КонецЕсли;
Счетчик = Счетчик + 1;
// Обработка нужной записи (добавление в массив, итоговую таблицу и т.д.)
КонецЦикла;
Если результат обязательно требуется получить строго в виде одного объекта ТаблицаЗначений через язык запросов, необходимо изолировать тяжелые условия во временную таблицу. Разберем этот алгоритм:
ЗаказТура, сразу фильтруя по реквизитам Проведен и Аннулирован, а также по диапазону дат. Сохраняем их во временную таблицу и обязательно индексируем поле Ссылка.Пример структуры пакетного запроса:
ВЫБРАТЬ
ЗаказТура.Ссылка КАК Ссылка,
ЗаказТура.Дата КАК Дата
ПОМЕСТИТЬ ВТ_ДействующиеЗаказы
ИЗ
Документ.ЗаказТура КАК ЗаказТура
ГДЕ
ЗаказТура.Проведен = ИСТИНА
И ЗаказТура.Аннулирован = ЛОЖЬ
ИНДЕКСИРОВАТЬ ПО
Ссылка;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ РАЗЛИЧНЫЕ
Участники.Участник КАК Участник,
Заказы.Ссылка КАК Ссылка
ИЗ
Участники КАК Участники
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ
ТЧ.Участник КАК Участник,
ТЧ.Ссылка КАК Ссылка,
ЗаказыШапка.Дата КАК Дата
ИЗ
Документ.ЗаказТура.Участники КАК ТЧ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ДействующиеЗаказы КАК ЗаказыШапка
ПО ТЧ.Ссылка = ЗаказыШапка.Ссылка
) КАК Заказы
ПО Участники.Участник = Заказы.Участник
Таким образом, отказ от коррелированных подзапросов с ВЫБРАТЬ ПЕРВЫЕ внутри условий соединения позволяет СУБД построить эффективный план выполнения и полностью устраняет тормоза при обработке больших массивов данных.