При разработке отчетов и обработок в конфигурациях на платформе 1С (в частности, в 1С:УПП 1.3) разработчики часто сталкиваются с медленной работой запросов, обращающихся к табличным частям документов. Ситуация усугубляется, когда в запросе присутствуют соединения с регистрами сведений, разыменования полей через точку, фильтрация по составным типам и группировки по неиндексированным полям. Разберем по шагам, почему исходный запрос выполняется медленно и как кардинально ускорить его работу.
Проанализируем исходный запрос, выполняющий выборку из табличной части Документ.ОтчетПроизводстваЗаСмену.РаспределениеМатериалов с соединением с регистром РегистрСведений.КатегорииОбъектов. В нем присутствует сразу несколько узких мест, негативно влияющих на производительность СУБД:
СерияНоменклатуры.СрокГодности, ЕдиницаИзмерения.Объем, Номенклатура.ВидНоменклатуры, Ссылка.Проведен и Ссылка.Дата транслируются СУБД в неявные LEFT JOIN. Поскольку они повторяются в секциях ВЫБРАТЬ, ГДЕ, СГРУППИРОВАТЬ ПО и УПОРЯДОЧИТЬ ПО, СУБД строит тяжелый план выполнения с избыточными соединениями таблиц еще до применения основных фильтров.Объект регистра сведений КатегорииОбъектов имеет составной тип. Конструкция ПО ОтчетПроизводстваЗаСменуРаспределениеМатериалов.Номенклатура = КатегорииОбъектов.Объект И (КатегорииОбъектов.Объект ССЫЛКА Справочник.Номенклатура) заставляет СУБД выполнять дополнительную проверку по типу объекта, что не позволяет полноценно использовать индексы и приводит к сканированию всей таблицы (Table Scan / Index Scan).КатегорииОбъектов.Категория = &КатегорияВиноматериал находится в секции ГДЕ при использовании ЛЕВОЕ СОЕДИНЕНИЕ. Это превращает левое соединение во внутреннее, однако выполняется неоптимально для оптимизатора СУБД.СУММА(Количество * ЕдиницаИзмерения.Объем / 10) и проверки ВЫБОР КОГДА рассчитываются построчно до группировки, нагружая процессор и память.НАЧАЛОПЕРИОДА и КОНЕЦПЕРИОДА непосредственно в секции ГДЕ ухудшает возможности СУБД по точному попаданию в индекс по дате документа.Выясним причину задержки при фильтрации по датам. Когда мы используем встроенные функции языка запросов над параметрами, СУБД выполняет дополнительные вычисления. Рассчитаем точные границы периода заранее на встроенном языке 1С и передадим готовые значения:
Запрос = Новый Запрос;
Запрос.Текст = "...";
// Подготавливаем точные границы периода заранее
Запрос.УстановитьПараметр("ДатаНачала", НачалоДня(Дата_010115));
Запрос.УстановитьПараметр("ДатаОкончания", КонецДня(ДатаТекущая));
Запрос.УстановитьПараметр("Серия", ВыбраннаяСерия);
Запрос.УстановитьПараметр("КатегорияВиноматериал", СправочникКатегории.Виноматериал);
Запрос.УстановитьПараметр("ВидНоменклатурыМатериал", СправочникВидыНоменклатуры.Материал);
Рассмотрим первый этап оптимизации структуры запроса. Чтобы избежать тяжелых соединений по составному типу измерения Объект регистра КатегорииОбъектов, предварительно выберем только нужную номенклатуру во временную таблицу. Обязательно выполним явное приведение типов с помощью функции ВЫРАЗИТЬ и проиндексируем поле связи:
ВЫБРАТЬ
ВЫРАЗИТЬ(КатегорииОбъектов.Объект КАК Справочник.Номенклатура) КАК Номенклатура
ПОМЕСТИТЬ ВТ_Виноматериалы
ИЗ
РегистрСведений.КатегорииОбъектов КАК КатегорииОбъектов
ГДЕ
КатегорииОбъектов.Категория = &КатегорияВиноматериал
И КатегорииОбъектов.Объект ССЫЛКА Справочник.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
Теперь соберем данные из табличной части документа. Отберем только проведенные документы за указанный период с нужной серией продукции, сразу соединив их с нашей временной таблицей ВТ_Виноматериалы с помощью ВНУТРЕННЕЕ СОЕДИНЕНИЕ. На этом шаге выбираем только исходные ссылочные поля и агрегируем количество, не выполняя разыменований через точку:
ВЫБРАТЬ
ТЧ.Ссылка КАК ОПзС,
ТЧ.Номенклатура КАК Номенклатура,
ТЧ.СерияНоменклатуры КАК СерияНоменклатуры,
ТЧ.ЕдиницаИзмерения КАК ЕдиницаИзмерения,
СУММА(ТЧ.Количество) КАК Количество
ПОМЕСТИТЬ ВТ_СвернутыеМатериалы
ИЗ
Документ.ОтчетПроизводстваЗаСмену.РаспределениеМатериалов КАК ТЧ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Виноматериалы КАК Виноматериалы
ПО ТЧ.Номенклатура = Виноматериалы.Номенклатура
ГДЕ
ТЧ.Ссылка.Проведен = ИСТИНА
И ТЧ.Ссылка.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания
И ТЧ.СерияПродукции = &Серия
СГРУППИРОВАТЬ ПО
ТЧ.Ссылка,
ТЧ.Номенклатура,
ТЧ.СерияНоменклатуры,
ТЧ.ЕдиницаИзмерения
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
СерияНоменклатуры,
ЕдиницаИзмерения
;
Посмотрим на финальный этап. Теперь, когда выборка содержит минимальное количество сгруппированных строк, мы соединяем полученную временную таблицу со справочниками для получения их реквизитов (объем, срок годности, вид номенклатуры, наименование). Это устраняет лишние соединения и исключает необходимость разыменования полей через точку в секциях группировки:
ВЫБРАТЬ
Материалы.Номенклатура КАК Номенклатура,
Материалы.СерияНоменклатуры КАК СерияНоменклатуры,
СпрСерии.СрокГодности КАК СрокГодности,
Материалы.Количество КАК Количество,
Материалы.ЕдиницаИзмерения КАК ЕдиницаИзмерения,
Единицы.Объем / 10 КАК Литраж,
Материалы.Количество * (Единицы.Объем / 10) КАК Объем,
ВЫБОР
КОГДА СпрНоменклатура.ВидНоменклатуры = &ВидНоменклатурыМатериал
ТОГДА 0
ИНАЧЕ 1
КОНЕЦ КАК НеобходимоЕщеРазвернуть,
Материалы.ОПзС КАК ОПзС
ИЗ
ВТ_СвернутыеМатериалы КАК Материалы
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.СерииНоменклатуры КАК СпрСерии
ПО Материалы.СерияНоменклатуры = СпрСерии.Ссылка
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ЕдиницыИзмерения КАК Единицы
ПО Материалы.ЕдиницаИзмерения = Единицы.Ссылка
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК СпрНоменклатура
ПО Материалы.Номенклатура = СпрНоменклатура.Ссылка
УПОРЯДОЧИТЬ ПО
СпрНоменклатура.Наименование,
СпрСерии.СрокГодности
СерияПродукции в табличной части выполняется регулярно и на больших объемах данных, имеет смысл установить свойство Индексировать для этого реквизита в метаданных конфигуратора. Это позволит СУБД осуществлять быстрый поиск по индексу вместо сканирования всей таблицы строк документов.УПОРЯДОЧИТЬ ПО из текста запроса лучше исключить и перенести настройку порядка на закладку «Настройки» схемы компоновки данных. Это избавит СУБД от затратной операции сортировки в памяти или базе TempDB перед передачей результата в платформу.