Как оптимизировать сложные запросы 1С с условиями соединения, ВЫРАЗИТЬ и виртуальными таблицами для повышения производительности?

Программист 1С v8.3 (Управляемые формы) Управленческий учет Торговля и дистрибуция
← К списку

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

Перед нами стоит задача оптимизации сложного пакета запросов, который в текущей реализации занимает до 85% времени при проведении товарных документов. Основная сложность заключается в многочисленных соединениях, использовании функции ВЫРАЗИТЬ, а также обращении к виртуальным таблицам регистров накопления с неоптимальными условиями. Наша цель — сделать запрос максимально производительным и читаемым.

1. Анализ текущей ситуации и выявление "узких мест"

Прежде чем приступать к изменениям, нам необходимо глубоко проанализировать текущий запрос. Как справедливо отмечают опытные разработчики, необходимо смотреть на тот SQL-запрос, который сформирует платформа 1С, и анализировать его план выполнения. Это ключевой шаг для понимания, где именно возникают задержки.

В нашем случае, запрос выполняется в подписке при проведении всех товарных документов. Это означает, что любое замедление будет напрямую влиять на интерактивную работу пользователей. С учетом предоставленного полного текста запроса, мы видим следующие потенциальные "узкие места":

  1. Сложные условия соединения с использованием ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ ЛОЖЬ КОНЕЦ.
  2. Использование функции ВЫРАЗИТЬ в условиях соединения и получения реквизитов.
  3. Передача подзапросов и сложных условий в параметры виртуальных таблиц регистров накопления (РегистрНакопления.Продажи.Обороты, РегистрНакопления.ВнутренниеЗаказы.Обороты, РегистрНакопления.ЗаказыПокупателей.Обороты, РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты).
  4. Чрезмерное использование виртуальных таблиц Обороты и ОстаткиИОбороты, когда возможно более эффективно прямое обращение к таблицам движений.
  5. Множественные вложенные запросы и группировки, которые могут быть избыточными или неоптимально обрабатываться СУБД.
  6. Использование ОБЪЕДИНИТЬ ВСЕ с последующей группировкой, а также потенциальные проблемы с оператором ИЛИ.

2. Оптимизация условий соединения: ВЫРАЗИТЬ, ССЫЛКА и ВЫБОР

Давайте рассмотрим подробнее блок запроса, который вызвал основной вопрос:


ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Реализации КАК Реализации
ПО (ВЫБОР
        КОГДА ПродажиОбороты.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг
            ТОГДА Реализации.Реализация = (ВЫРАЗИТЬ(ПродажиОбороты.Регистратор КАК Документ.РеализацияТоваровУслуг))
        ИНАЧЕ ЛОЖЬ
    КОНЕЦ)
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Реализации КАК Возврат
ПО (ВЫБОР
        КОГДА ПродажиОбороты.Регистратор ССЫЛКА Документ.ВозвратТоваровОтПокупателя
            ТОГДА Возврат.Реализация = (ВЫРАЗИТЬ(ВЫРАЗИТЬ(ПродажиОбороты.Регистратор КАК Документ.ВозвратТоваровОтПокупателя).ДокументОснование КАК Документ.РеализацияТоваровУслуг))
        ИНАЧЕ ЛОЖЬ
    КОНЕЦ)

Здесь мы видим сложную конструкцию ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ ЛОЖЬ КОНЕЦ в условии соединения, а также использование функции ВЫРАЗИТЬ.

2.1. ВЫРАЗИТЬ vs ССЫЛКА в условиях соединения

Оператор ССЫЛКА используется для проверки типа значения, например, ПродажиОбороты.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг. Функция ВЫРАЗИТЬ используется для явного приведения значения составного типа к конкретному типу. Это помогает оптимизатору запросов избежать неявных соединений со всеми возможными таблицами объектов, входящих в составной тип поля Регистратор.

В данном случае, автор запроса, возможно, интуитивно или намеренно, использовал ВЫРАЗИТЬ для отбора документов конкретного типа. Если убрать ВЫРАЗИТЬ без замены на ССЫЛКА, запрос может вернуть некорректные результаты, так как условие соединения будет применяться ко всем типам документов, на которые может ссылаться поле Регистратор.

2.2. Упрощение условий соединения

Как было предложено в обсуждении, сложную конструкцию ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ ЛОЖЬ КОНЕЦ можно значительно упростить, заменив ее на обычное логическое условие. Вместо:


ПО (ВЫБОР
        КОГДА ПродажиОбороты.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг
            ТОГДА Реализации.Реализация = (ВЫРАЗИТЬ(ПродажиОбороты.Регистратор КАК Документ.РеализацияТоваровУслуг))
        ИНАЧЕ ЛОЖЬ
    КОНЕЦ)

мы можем написать:


ПО ПродажиОбороты.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг
    И Реализации.Реализация = ПродажиОбороты.Регистратор

Это не только улучшает читаемость, но и может быть более эффективно обработано СУБД. Аналогично для второго соединения:


ПО ПродажиОбороты.Регистратор ССЫЛКА Документ.ВозвратТоваровОтПокупателя
    И Возврат.Реализация = (ВЫРАЗИТЬ(ПродажиОбороты.Регистратор КАК Документ.ВозвратТоваровОтПокупателя).ДокументОснование)

Здесь ВЫРАЗИТЬ для ДокументОснование остается необходимым, так как мы обращаемся к реквизиту составного типа, и это помогает избежать неявных соединений со всеми возможными таблицами-регистраторами, которые могут быть в поле ДокументОснование.

Важный момент: использование ВЫРАЗИТЬ для поля ПродажиОбороты.Регистратор (когда оно сравнивается с Реализации.Реализация) не требуется, так как Реализации.Реализация уже имеет конкретный тип, и сравнение будет идти по УИД.

3. Эффективная работа с виртуальными таблицами регистров накопления

Мы видим, что в запросе активно используются виртуальные таблицы Обороты и ОстаткиИОбороты с довольно сложными условиями в параметрах.


РегистрНакопления.Продажи.Обороты(
        &ДатаЗаказа,
        ,
        Регистратор,
        ДокументПродажи В
            (ВЫБРАТЬ
                Реализации.Реализация
            ИЗ
                ВТ_Реализации КАК Реализации)) КАК ПродажиОбороты

и аналогичные конструкции для ВнутренниеЗаказы.Обороты, ЗаказыПокупателей.Обороты, ТоварыНаСкладах.ОстаткиИОбороты.

3.1. Условия в параметрах виртуальных таблиц

Рассмотрим подробнее рекомендацию: при обращении к виртуальной таблице следует передавать в условия наиболее простые конструкции, например, "Измерение = Значение". Не рекомендуется использовать подзапросы и соединения в параметрах виртуальной таблицы.

Почему это важно? Виртуальная таблица может формировать временные таблицы СУБД для своей работы. Если в параметр Условие заложить сложный подзапрос, оптимизатор СУБД не сможет распознать, что из временной таблицы впоследствии будет использована лишь часть данных. Это приводит к заполнению временной таблицы ненужными данными, что значительно увеличивает объем обрабатываемых данных и снижает производительность.

3.2. Альтернативы для сложных условий

  1. Вынесение подзапроса во временную таблицу: Если список документов (как в ДокументПродажи В (ВЫБРАТЬ ...)) небольшой, мы можем сначала получить этот список во временную таблицу, а затем передать его в качестве параметра запроса в виртуальную таблицу.
  2. Передача списка как параметра: Если список очень маленький (например, несколько элементов), его можно передать как параметр &МассивДокументовПродажи. Однако, для больших списков это может привести к накладным расходам на передачу данных между сервером 1С и СУБД.
  3. Прямое обращение к таблицам движений: Для регистров накопления, особенно если периодичность "Регистратор" и нет отбора по периоду, или для получения остатков и оборотов за промежутки между рассчитанными итогами, прямое обращение к первичным таблицам движений регистра может быть более эффективным. Например, вместо РегистрНакопления.Продажи.Обороты можно использовать РегистрНакопления.Продажи. Это особенно актуально для ОстаткиИОбороты на больших объемах данных, где ее использование не рекомендуется из-за высокой ресурсоемкости.

В нашем случае, вместо:


РегистрНакопления.Продажи.Обороты(
        &ДатаЗаказа,
        ,
        Регистратор,
        ДокументПродажи В
            (ВЫБРАТЬ
                Реализации.Реализация
            ИЗ
                ВТ_Реализации КАК Реализации)) КАК ПродажиОбороты

мы можем сначала получить список реализаций в отдельную временную таблицу, если ВТ_Реализации уже не содержит нужный список:


ВЫБРАТЬ
    Реализации.Реализация
ПОМЕСТИТЬ ВТ_СписокРеализаций
ИЗ
    ВТ_Реализации КАК Реализации
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ...
ИЗ
    РегистрНакопления.Продажи.Обороты(
            &ДатаЗаказа,
            ,
            Регистратор,
            ДокументПродажи В (ВЫБРАТЬ Реализация ИЗ ВТ_СписокРеализаций)) КАК ПродажиОбороты

Хотя даже такой подзапрос в параметре виртуальной таблицы не всегда оптимален. Идеально было бы передать массив значений, если он не слишком велик, или соединиться с временной таблицей ВТ_СписокРеализаций уже после получения данных из Оборотов.

Посмотрим на пример из обсуждения: если ВТ_Реализации малюсенькая, то лучше передать ее как параметр. Но если она будет формироваться подзапросом, то это снова проблема. Если ВТ_Реализации уже есть и она небольшая, то можно использовать:


|    РегистрНакопления.Продажи.Обороты(
|            ,
|            ,
|            Регистратор,
|            ДокументПродажи В (&МассивДокументовПродажи)

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

4. Использование временных таблиц и пакетов запросов

Весь предоставленный запрос представляет собой большой пакет запросов с множеством вложенных конструкций ОБЪЕДИНИТЬ и СГРУППИРОВАТЬ ПО. Такая структура может быть очень сложной для отладки и понимания, а также для оптимизатора СУБД.

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

  1. Улучшает читаемость и облегчает отладку.
  2. Позволяет замерять время выполнения отдельных этапов, чтобы точно определить, какой именно блок запроса тормозит.
  3. Помогает СУБД выбрать более оптимальный план выполнения, так как она видит промежуточные результаты и может лучше спланировать дальнейшие операции.

Автор запроса уже начал растаскивать часть логики на ВТ, что по его словам, "полегчало чуть". Мы должны продолжить эту работу. Например, блок ПродажиОборот, который содержит два ЛЕВОЕ СОЕДИНЕНИЕ, можно переписать, разбив на отдельные запросы и затем объединив их, если это целесообразно, или использовать внутренние соединения, если это соответствует логике.

5. Замена логического оператора ИЛИ на ОБЪЕДИНИТЬ ВСЕ

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

Если в вашем запросе есть условия типа (Условие1 ИЛИ Условие2), особенно когда Условие1 и Условие2 обращаются к разным полям или имеют разную логику, рассмотрите возможность переписать это как:


ВЫБРАТЬ ... ИЗ Таблица ГДЕ Условие1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ ... ИЗ Таблица ГДЕ Условие2

Это позволяет СУБД обработать каждое условие отдельно, потенциально используя индексы для каждого подзапроса, а затем объединить результаты.

6. Индексирование временных таблиц

Для оптимизации соединений с временными таблицами, особенно если они содержат большое количество записей (более 1000), рекомендуется индексировать поля, участвующие в условии соединения ПО.

Например, если у нас есть ВТ_Реализации и мы соединяемся по полю Реализация:


ПОМЕСТИТЬ ВТ_Реализации
ИЗ
    ...
;
СОЗДАТЬ ИНДЕКС ПО ВТ_Реализации.Реализация;

Это помогает СУБД использовать индексы для более быстрого поиска и слияния данных (merge join), что может значительно ускорить выполнение запроса. Без индексирования СУБД может дважды сортировать временную таблицу, что является избыточным.

7. Общие рекомендации и архитектурные аспекты

7.1. Анализ плана запроса SQL

Проанализируем ситуацию: в 1С мы пишем на языке запросов 1С, который затем транслируется в SQL. Именно план выполнения SQL-запроса на СУБД (MSSQL, PostgreSQL и т.д.) является наиболее точным инструментом для выявления проблем. Используйте инструменты вроде SQL Profiler или встроенные средства СУБД для получения и анализа плана.

7.2. Избегание неявных соединений через точку

Обращение через точку к полям составного типа, особенно в условиях фильтрации или соединения, может приводить к неявным соединениям со всеми возможными таблицами, входящими в составной тип. Это значительно замедляет запрос. Используйте ВЫРАЗИТЬ или явные соединения для таких случаев, как мы уже рассмотрели.

7.3. Контекст подписки на события

Помните, что запрос живет в подписке проведения документов. Это означает, что он выполняется очень часто. Любые оптимизации здесь критичны. Если даже после всех оптимизаций запрос остается медленным, возможно, стоит пересмотреть архитектуру и перенести часть расчетов из подписки в регламентные задания или отложенное выполнение. Например, статусы заказов можно обновлять не в момент проведения каждого документа, а периодически, или асинхронно.

7.4. Долгосрочное решение: пересмотр архитектуры

Как было справедливо отмечено в обсуждении, иногда проще не городить "это безумие", а добавить план-фактный регистр накопления (например, ИсполнениеЗаказов). В нем можно фиксировать плановые и фактические количества по заказам, и все документы будут отмечать завершенные количества. Это значительно упростит запрос на получение статуса, поскольку он будет обращаться к уже агрегированным данным. Хотя это и требует переделки архитектуры, что не всегда возможно в краткосрочной перспективе, но это наиболее правильное решение для подобных задач.

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

Наш путь оптимизации будет состоять из последовательных шагов: упрощение условий соединения, рациональное использование виртуальных таблиц, разбиение запроса на логические блоки с временными таблицами, а также анализ и корректировка плана выполнения SQL. Работая вместе, мы сможем добиться значительного улучшения производительности!

← К списку