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

← На главную

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

Анализ исходного некорректного запроса

Рассмотрим пример кода, в котором возникает эта проблема:


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

В данном запросе допущено сразу несколько критических ошибок с точки зрения производительности и стандартов разработки 1С:

Использование функции ТИПЗНАЧЕНИЯ в условии соединения. При трансляции запроса на язык SQL платформа 1С преобразует проверку типа составного поля в сложное выражение с кодами типов. Это блокирует использование индексов по ссылочным полям, и СУБД вынуждена выполнять полное сканирование (Table Scan или Index Scan).
Отсутствие параметров периода у виртуальной таблицы. Когда у виртуальной таблицы Обороты не заданы границы периода, платформа рассчитывает итоговые обороты за всю историю существования базы данных.
Соединение с виртуальной таблицей напрямую в секции ПО. При прямом соединении реальной таблицы или другой виртуальной таблицы с виртуальной таблицей оборотов СУБД генерирует подзапрос. Оптимизатор SQL-сервера не может точно оценить кардинальность (количество строк) получаемого результата и чаще всего выбирает неэффективный план выполнения (например, Nested Loops вместо Hash Join).
Многократное (двойное) обращение к одной и той же виртуальной таблице. Повторный расчет агрегатов для одной и той же таблицы движений мультиплицирует нагрузку на дисковую подсистему и процессор сервера БД.

Способ 1. Перенос условий в параметры виртуальной таблицы и использование оператора ССЫЛКА

Первый шаг к исправлению ситуативного запроса — перенос фильтрации из условия соединения ПО непосредственно в параметры виртуальной таблицы, а также замена функции ТИПЗНАЧЕНИЯ на логический оператор ССЫЛКА.

Проанализируем исправленный вариант обращения к виртуальной таблице:


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

Почему этот вариант работает быстрее:

Фильтрация по типу документа ДокументПродажи ССЫЛКА Документ.РеализацияТоваровУслуг происходит на этапе формирования самой виртуальной таблицы, а не во время выполнения соединения.
Указание явного периода &НачалоПериода и &КонецПериода снижает объем обрабатываемых физических таблиц итогов и движений.

Способ 2. Оптимизация с использованием временных таблиц (Рекомендуемый стандарт)

Согласно стандартам разработки 1С, наиболее эффективным и безопасным способом является выборка данных из виртуальных таблиц во временные таблицы с последующим индексированием по полям связи.

Рассмотрим пошагово, как правильно переписать запрос:

Шаг 1. Выбираем данные из виртуальной таблицы оборотов во временную таблицу с наложением всех необходимых условий по типу документа и периоду.


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

Шаг 2. В основном запросе связываем остатки с нашей индексированной временной таблицей ВТ_Продажи.


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

Способ 3. Обращение к физической таблице движений

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

Физическая таблица имеет готовые кластеризованные и составные индексы. При фильтрации по полям Период и Регистратор/ДокументПродажи СУБД применит эффективный поиск по индексу (Index Seek) вместо сканирования.

Подведение итогов

Для предотвращения зависания запросов и переполнения журнала транзакций SQL-сервера следует соблюдать ключевые правила оптимизации:

Никогда не использовать ТИПЗНАЧЕНИЯ в секции ПО при соединениях. Заменяйте его на оператор ССЫЛКА внутри параметров виртуальной таблицы.
Всегда задавать параметры периода (начало и конец) для виртуальных таблиц оборотов и срезов.
Избегать прямых соединений виртуальных таблиц между собой. Выгружайте данные во временные таблицы и обязательно индексируйте их конструкцией ИНДЕКСИРОВАТЬ ПО по полям, участвующим в условиях ПО.