Как оптимизировать медленный запрос в 1С при работе с виртуальными таблицами и подстроками

← На главную

Разработчики в среде 1С:Предприятие часто сталкиваются со снижением производительности базы данных при выполнении сложных запросов. Особенно остро эта проблема проявляется при замере производительности на строке РезультатЗапроса = Запрос.Выполнить();. В этой статье мы подробно разберем реальный практический кейс, проанализируем критические ошибки в коде запроса и пошагово проведем его рефакторинг для достижения максимальной скорости выполнения.

Рассматриваемый запрос содержит в себе целый «букет» классических антипаттернов проектирования, которые заставляют СУБД (будь то MS SQL Server или PostgreSQL) полностью отказываться от использования индексов и переходить к ресурсоемким операциям сканирования таблиц (Table Scan / Index Scan). Давайте вместе разберемся в причинах медленной работы и найдем оптимальные решения.

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

Использование функции ПОДСТРОКА в условиях соединения (JOIN). Это одна из самых тяжелых операций для планировщика СУБД.
Соединение физических и виртуальных таблиц с вложенными подзапросами. Нарушение базовых стандартов разработки 1С.
Обращение к виртуальной таблице оборотов за весь период без ограничений. Чтение всей истории движений регистра ради получения одной даты.
Неявные левые соединения (разыменование полей через точку) во вложенных условиях. Приводит к скрытому росту количества соединяемых таблиц на уровне СУБД.
Универсальные условия с логическим оператором ИЛИ. Блокируют возможность использования индексов планировщиком запросов.

Рассмотрим каждую из этих проблем подробнее и найдем пути их устранения.

Проблема №1: Функция `ПОДСТРОКА` в условиях связи таблиц

В исходном запросе мы видим конструкцию соединения:


ВНУТРЕННЕЕ СОЕДИНЕНИЕ Доки КАК Доки
ПО (ПОДСТРОКА(ОстСостПотолка.Код, 8, 5) = Доки.НомерСтр)

Почему это работает медленно: Любая строковая функция (такая как ПОДСТРОКА, ПОДСТРОКА, Лев, Прав) в условии связи делает это условие "не-SARGable" (Search Argument Able). СУБД не может сопоставить данные по готовому индексу поля Код, поскольку значение этого поля должно быть предварительно вычислено (обрезано) для каждой строки таблицы. В результате вместо быстрого поиска по индексу (Index Seek) СУБД выполняет полное сканирование всей таблицы (Table Scan) и строит соединение типа Nested Loops (вложенные циклы), производительность которого падает экспоненциально росту объема данных.

Решение: Нам необходимо предварительно подготовить данные. Если архитектура конфигурации не позволяет хранить префиксы и номера документов в отдельных измерениях регистра (что было бы идеальным решением), мы должны вынести вычисление подстроки в отдельную временную таблицу, проиндексировать полученное поле и только потом выполнять соединение.

Проблема №2: Использование вложенных запросов в соединениях

В финальной части запроса присутствует соединение вложенного подзапроса (состоящего из трех секций ОБЪЕДИНИТЬ ВСЕ) с таблицами остатков и оборотов:


ИЗ
    (ВЫБРАТЬ ... ОБЪЕДИНИТЬ ВСЕ ... ) КАК ВложенныйЗапрос
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Товары.Остатки КАК ТоварыОстатки ...

Почему это работает медленно: Стандарты разработки 1С категорически запрещают использовать соединения с вложенными запросами. Оптимизатор СУБД не может корректно оценить количество строк (Cardinality Estimation), которое вернет вложенный запрос. Не имея статистики распределения данных в этой динамической выборке, оптимизатор строит неверный план выполнения. Ситуация усугубляется, когда внутри вложенного запроса также используются соединения.

Решение: Все вложенные запросы необходимо заменить временными таблицами. Временная таблица в 1С материализуется на сервере СУБД (в базе данных tempdb для MS SQL). СУБД имеет возможность собрать точную статистику по этой таблице, а принудительное создание индекса гарантирует моментальное соединение с другими таблицами.

Проблема №3: Виртуальная таблица Оборотов за весь период без параметров

Посмотрим на левое соединение с таблицей оборотов:


ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СостояниеПотолков.Обороты(
                        ,
                        ,
                        Регистратор,
                        ПОДСТРОКА(Код, 1, 1) = &ОбщийПрефиск ...

Почему это работает медленно: Здесь допущена критическая ошибка. Разработчик обращается к виртуальной таблице оборотов с периодичностью Регистратор, не указывая при этом ни дату начала, ни дату окончания периода. При этом единственная цель этого соединения — получить максимальный период (дату последнего движения) через агрегатную функцию МАКСИМУМ(СостояниеПотолковОбороты.Period).

Для построения такой таблицы СУБД вынуждена прочитать абсолютно все записи из физической таблицы движений регистра с момента начала ведения учета в системе! Если в регистре миллионы записей, этот запрос гарантированно зависнет или завершится по таймауту. Более того, фильтр внутри параметров виртуальной таблицы снова использует функцию ПОДСТРОКА, окончательно убивая производительность.

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

Проблема №4: Разыменование полей через точку во вложенных условиях

В тексте запроса присутствуют условия вида: СостояниеПотолковОстатки.Контрагент.Ответственный В ИЕРАРХИИ (&ВыбОтветственный).

Почему это работает медленно: Каждая точка в запросе (обращение к реквизиту ссылочного поля) преобразуется платформой 1С в неявное левое соединение (LEFT OUTER JOIN) с таблицей соответствующего справочника. Когда такие условия используются внутри параметров виртуальных таблиц или в секциях ГДЕ сложных запросов, оптимизатор строит перегруженные планы запросов с избыточным количеством таблиц.

Использование оператора В ИЕРАРХИИ заставляет систему выполнять рекурсивный поиск по дереву групп справочника. В сочетании с обращением через точку к контрагенту это приводит к крайне медленной работе.

Решение: Список ответственных лиц (с учетом иерархии) необходимо получить заранее в коде 1С или в отдельном предварительном запросе и передать в финальный запрос в виде массива или проиндексированной временной таблицы.

Пошаговый рефакторинг запроса

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

Шаг 1. Подготовка и оптимизация таблицы документов

Начнем с подготовки входящей таблицы документов &Табл. Исходный запрос помещал ее во временную таблицу Доки, но типизация поля НомерСтр происходила "на лету" с помощью конструкции ВЫРАЗИТЬ. Мы оптимизируем этот шаг. Убедимся, что типы данных определены жестко, а временная таблица проиндексирована по ключевым полям:


Запрос.Текст = "ВЫБРАТЬ
               |    Доки.Номер КАК Номер,
               |    ВЫРАЗИТЬ(Доки.НомерСтр КАК СТРОКА(5)) КАК НомерСтр,
               |    Доки.Ссылка КАК Ссылка
               |ПОМЕСТИТЬ ВТ_ДокументыВходные
               |ИЗ
               |    &Табл КАК Доки
               |
               |ИНДЕКСИРОВАТЬ ПО
               |    НомерСтр,
               |    Ссылка
               |;";

Шаг 2. Выборка остатков по регистру с предварительной фильтрацией

Теперь выберем остатки из регистра СостояниеПотолков. Обратите внимание, что мы убираем универсальные условия с логическим ИЛИ. Если параметры &ВыбДилер или &ВыбОтветственный не заполнены, мы будем динамически формировать текст запроса в коде (этот прием мы рассмотрим ниже). В самом запросе оставляем только прямые и жесткие условия.

Также мы отказываемся от использования функции ПОДСТРОКА в параметрах виртуальной таблицы для отбора по общему префиксу. Вместо этого мы выберем данные без этого условия (или наложим его позже, если префикс действительно ограничивает выборку), чтобы СУБД могла задействовать индексы регистра по измерениям Контрагент и Материал.


Запрос.Текст = Запрос.Текст + "
               |ВЫБРАТЬ
               |    Остатки.Код КАК Код,
               |    Остатки.Состояние КАК Состояние,
               |    Остатки.Контрагент КАК Контрагент,
               |    Остатки.Материал КАК Материал,
               |    Остатки.Площадь КАК Площадь,
               |    Остатки.КоличествоОстаток КАК КоличествоОстаток,
               |    ПОДСТРОКА(Остатки.Код, 1, 1) КАК СимволПрефикса,
               |    ПОДСТРОКА(Остатки.Код, 2, 2) КАК ДвузначныйПрефикс,
               |    ПОДСТРОКА(Остатки.Код, 8, 5) КАК ИзвлеченныйНомер
               |ПОМЕСТИТЬ ВТ_ОстаткиРегистра
               |ИЗ
               |    РегистрНакопления.СостояниеПотолков.Остатки(
               |        ,
               |        Контрагент = &ВыбДилер
               |            И Контрагент.Ответственный = &ВыбОтветственный
               |    ) КАК Остатки
               |
               |ИНДЕКСИРОВАТЬ ПО
               |    ИзвлеченныйНомер,
               |    ДвузначныйПрефикс
               |;";

Что мы сделали: Мы получили чистые остатки по конкретному дилеру и ответственному. На лету мы вычислили все необходимые подстроки (префиксы и номера) и поместили их в типизированные поля временной таблицы ВТ_ОстаткиРегистра. Самое главное — мы проиндексировали полученную временную таблицу по вычисленным полям ИзвлеченныйНомер и ДвузначныйПрефикс. Теперь СУБД сможет мгновенно соединять эти данные с таблицей документов по индексам!

Шаг 3. Быстрое соединение таблиц по индексам временных таблиц

Теперь мы можем выполнить соединение остатков с документами. Благодаря тому, что обе таблицы находятся в TempDB и проиндексированы по связующим полям, операция соединения выполнится за доли миллисекунды. Мы избавились от тяжелых вычислений подстрок в секции ПО (JOIN).


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

Обратите внимание: Мы объединили три тяжелых запроса с конструкциями ОБЪЕДИНИТЬ ВСЕ в один элегантный и быстрый запрос с использованием логического ветвления в условии соединения временных таблиц. СУБД моментально сопоставит строки благодаря подготовленным индексам.

Шаг 4. Оптимизация получения даты последнего движения

Как мы выяснили ранее, обращение к таблице Обороты без параметров периода ради получения максимальной даты движения губительно для производительности. Вместо этого мы обратимся напрямую к реальной физической таблице движений регистра накопления (таблице значений) РегистрНакопления.СостояниеПотолков. Но делать мы это будем не для всего регистра, а соединяя физическую таблицу с нашей компактной временной таблицей ВТ_ОтфильтрованныеОстатки!

Это позволит СУБД использовать индекс физической таблицы по измерениям регистра (Index Seek) и прочитать информацию только по тем объектам, которые прошли фильтрацию на предыдущих шагах.


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

Шаг 5. Финальная сборка данных

На финальном этапе мы собираем все данные воедино. Мы соединяем нашу отфильтрованную таблицу остатков ВТ_ОтфильтрованныеОстатки со справочной таблицей последних дат ВТ_ПоследниеДатыДвижений и таблицей остатков товаров на складах. Соединение со складами происходит по простому и быстрому условию равенства кодов.


Запрос.Текст = Запрос.Текст + "
               |ВЫБРАТЬ
               |    Остатки.Код КАК Код,
               |    Остатки.Состояние КАК Состояние,
               |    Остатки.Контрагент КАК Контрагент,
               |    Остатки.Материал КАК Материал,
               |    Остатки.Площадь КАК Площадь,
               |    Остатки.КоличествоОстаток КАК КоличествоОстаток,
               |    ТоварыОстатки.Склад КАК Склад,
               |    ТоварыОстатки.Ячейка КАК Ячейка,
               |    ДатыДвижений.Период КАК Период
               |ИЗ
               |    ВТ_ОтфильтрованныеОстатки КАК Остатки
               |        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПоследниеДатыДвижений КАК ДатыДвижений
               |        ПО Остатки.Код = ДатыДвижений.Код
               |            И Остатки.Состояние = ДатыДвижений.Состояние
               |            И Остатки.Контрагент = ДатыДвижений.Контрагент
               |            И Остатки.Материал = ДатыДвижений.Материал
               |
               |        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Товары.Остатки КАК ТоварыОстатки
               |        ПО Остатки.Код = ТоварыОстатки.ШтрихКод.Код
               |            И Остатки.Состояние.Код = 7
               |
               |УПОРЯДОЧИТЬ ПО
               |    Состояние УБЫВ
               |ИТОГИ
               |    СУММА(КоличествоОстаток)
               |ПО
               |    ОБЩИЕ,
               |    Контрагент
               |";

Динамическое построение запроса на встроенном языке 1С

Как мы отметили ранее, использование универсальных условий с проверкой параметров на пустое значение (например, &ВыбДилер = ЗНАЧЕНИЕ(Справочник.Контрагент.ПустаяСсылка)) негативно сказывается на качестве построения плана выполнения запроса в СУБД. Оптимизатор СУБД вынужден закладывать в план сценарий, при котором параметр может оказаться пустым, что часто приводит к выбору полного сканирования (Scan) вместо поиска по индексу (Seek), даже если передан конкретный контрагент.

Правильным и профессиональным подходом является динамическое формирование условий запроса в коде 1С. Давайте посмотрим, как это реализовать:


// Определяем базовые параметры
Запрос = Новый Запрос;

СтрокаОтбора = "";
Если ЗначениеЗаполнено(ВыбДилер) Тогда
    СтрокаОтбора = СтрокаОтбора + " И Контрагент = &ВыбДилер";
    Запрос.УстановитьПараметр("ВыбДилер", ВыбДилер);
КонецЕсли;

Если ЗначениеЗаполнено(ВыбОтветственный) Тогда
    // Получаем список ответственных с учетом иерархии заранее
    МассивОтветственных = Новый Массив;
    МассивОтветственных.Добавить(ВыбОтветственный);
    
    // Для примера выберем подчиненных, если это группа
    // В реальной задаче здесь должен быть запрос по иерархии справочника
    
    СтрокаОтбора = СтрокаОтбора + " И Контрагент.Ответственный В (&МассивОтветственных)";
    Запрос.УстановитьПараметр("МассивОтветственных", МассивОтветственных);
КонецЕсли;

// Внедряем динамический отбор в текст запроса
ТекстЗапросаОстатков = "
|ВЫБРАТЬ
|    Остатки.Код КАК Код,
|    Остатки.Состояние КАК Состояние,
|    Остатки.Контрагент КАК Контрагент,
|    Остатки.Материал КАК Материал,
|    Остатки.Площадь КАК Площадь,
|    Остатки.КоличествоОстаток КАК КоличествоОстаток,
|    ПОДСТРОКА(Остатки.Код, 1, 1) КАК СимволПрефикса,
|    ПОДСТРОКА(Остатки.Код, 2, 2) КАК ДвузначныйПрефикс,
|    ПОДСТРОКА(Остатки.Код, 8, 5) КАК ИзвлеченныйНомер
|ПОМЕСТИТЬ ВТ_ОстаткиРегистра
|ИЗ
|    РегистрНакопления.СостояниеПотолков.Остатки(, ИСТИНА " + СтрокаОтбора + ") КАК Остатки
|ИНДЕКСИРОВАТЬ ПО
|    ИзвлеченныйНомер,
|    ДвузначныйПрефикс
|;";

Преимущества этого подхода: Если пользователь не выбрал конкретного дилера или ответственного, в запрос просто не попадут лишние условия. СУБД получит чистый, лаконичный SQL-текст, под который построит идеальный план выполнения, используя только необходимые индексы.