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

← На главную

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

Анализ проблем исходного запроса

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

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

Потеря SARGability (неиндексируемый отбор по дате). В секции ГДЕ каждого подзапроса используется условие вида ВЫБОР КОГДА ДокументыИсключения.Исключить ТОГДА ДокументыИсключения.ПоказыватьОтДаты ИНАЧЕ Заказанное.Ссылка.ДатаПоставки КОНЕЦ МЕЖДУ &НачДата И &КонДата. Для СУБД такое вычисляемое выражение является невычисляемым предикатом на этапе построения плана выполнения. Оптимизатор SQL не может выполнить операцию поиска по индексу (Index Seek) ни по датам документов, ни по датам регистра. Это приводит к полному сканированию таблиц (Table Scan / Clustered Index Scan) за весь период существования базы данных.
Обращение к реквизитам шапки через точку из табличной части. В табличных частях на уровне физических таблиц СУБД нет реквизитов Дата, ДатаПоставки или Склад — там присутствуют только Ссылка, НомерСтроки и данные строк. Обращение вида Заказанное.Ссылка.ДатаПоставки генерирует неявное соединение с таблицей шапки документа. Если обращаться к табличным частям без предварительной фильтрации списка документов, СУБД вынуждена обрабатывать миллионы строк за всю историю.
Множественное разыменование ссылочных полей. Конструкции вроде ЗаявкаСклад.Номенклатура.Код, ЗаявкаСклад.Агент.Код, ЗаявкаСклад.Контрагент.Наименование платформой транслируются в отдельные операции LEFT JOIN на уровне SQL. Чем больше таких разыменований на этапе группировок, тем тяжелее итоговый план выполнения.
Лишние соединения в подзапросах. В первой ветке запроса присутствует соединение ЛЕВОЕ СОЕДИНЕНИЕ Документ.Заявка.ПолнаяТаблица КАК ЗаявкаПолнаяТаблица, поля которого вообще не выбираются. Это не только тратит ресурсы СУБД, но и может дублировать строки (декартово произведение), искажая агрегаты СУММА().
Вложенные запросы в соединениях со справочником. Соединение со вложенным запросом по справочнику Справочник.ШтрихКоды мешает СУБД использовать стандартные индексы.

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

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

Создадим временную таблицу шапок документов с обязательным индексированием по полю связи:


ВЫБРАТЬ
    Док.Ссылка КАК Ссылка,
    Док.МестоХранения КАК МестоХранения,
    Док.Статус КАК Статус,
    ЕСТЬNULL(Исключения.АгентДляОтчета, Док.Агент) КАК Агент,
    ЕСТЬNULL(Исключения.ТорговаяТочкаДляОтчета.Контрагент, Док.Контрагент) КАК Контрагент,
    ЕСТЬNULL(Исключения.ТорговаяТочкаДляОтчета, Док.ТорговаяТочка) КАК ТорговаяТочка,
    ВЫБОР
        КОГДА Исключения.Исключить
            ТОГДА Исключения.ПоказыватьОтДаты
        ИНАЧЕ Док.ДатаПоставки
    КОНЕЦ КАК ДатаПоставки,
    (100 + ЕСТЬNULL(Исключения.Дефлятор, 0)) / 100 КАК КоэффДефлятора,
    ВЫБОР
        КОГДА Исключения.Исключить
            ТОГДА Исключения.ПоказыватьОтДаты
        ИНАЧЕ Док.Дата
    КОНЕЦ КАК Дата,
    Док.Номер КАК Номер,
    Док.ВидДокумента КАК ВидДокумента
ПОМЕСТИТЬ ВТ_ОтобранныеЗаявки
ИЗ
    Документ.Заявка КАК Док
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДополнительнаяИнформацияПоЗаявкам КАК Исключения
        ПО Док.Ссылка = Исключения.Документ
        И (Исключения.Исключить)
ГДЕ
    // Обычные документы по индексу даты поставки
    (Исключения.Документ ЕСТЬ NULL И Док.ДатаПоставки МЕЖДУ &НачДата И &КонДата)
    // Либо документы-исключения по дате из регистра
    ИЛИ (Исключения.Исключить И Исключения.ПоказыватьОтДаты МЕЖДУ &НачДата И &КонДата)

ИНДЕКСИРОВАТЬ ПО
    Ссылка;

Аналогичным образом формируем временную таблицу ВТ_ОтобранныеЗаявкиСклад для второго вида документа. Обратим внимание на блок ИНДЕКСИРОВАТЬ ПО Ссылка: без этого дальнейшие соединения с табличными частями будут выполняться неэффективно сканированием всей временной таблицы.

Шаг 2. Соединение табличных частей только с отобранными документами

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


ВЫБРАТЬ
    Заказанное.Номенклатура КАК Номенклатура,
    Заявки.МестоХранения КАК МестоХранения,
    Заявки.Ссылка КАК Ссылка,
    Заявки.Статус КАК Статус,
    Заявки.Агент КАК Агент,
    Заявки.Контрагент КАК Контрагент,
    Заявки.ТорговаяТочка КАК ТорговаяТочка,
    Заказанное.Количество КАК Количество,
    Заказанное.Количество * Заказанное.Цена * Заявки.КоэффДефлятора КАК СумВсего,
    0 КАК Недогруз,
    0 КАК Недовоз,
    0 КАК Возврат,
    0 КАК Бой,
    0 КАК НедогрузСумма,
    0 КАК НедовозСумма,
    0 КАК ВозвратСумма,
    0 КАК БойСумма,
    Заявки.ДатаПоставки КАК ДатаПоставки,
    Заявки.Дата КАК Дата,
    Заявки.Номер КАК Номер,
    Заявки.ВидДокумента КАК ВидДокумента,
    Заказанное.Номенклатура.НоменклатураОснование КАК НоменклатураОснование
ПОМЕСТИТЬ ВТ_СтрокиДокументов
ИЗ
    ВТ_ОтобранныеЗаявки КАК Заявки
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.Заявка.ТаблицаЗаказанное КАК Заказанное
        ПО Заявки.Ссылка = Заказанное.Ссылка

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    Отклонения.Номенклатура,
    Заявки.МестоХранения,
    Заявки.Ссылка,
    Заявки.Статус,
    Заявки.Агент,
    Заявки.Контрагент,
    Заявки.ТорговаяТочка,
    0,
    0,
    ВЫБОР КОГДА Отклонения.ВидОтклонений.Недогруз ТОГДА Отклонения.Количество ИНАЧЕ 0 КОНЕЦ,
    ВЫБОР КОГДА Отклонения.ВидОтклонений.Недовоз ТОГДА Отклонения.Количество ИНАЧЕ 0 КОНЕЦ,
    ВЫБОР КОГДА Отклонения.ВидОтклонений.Возврат ТОГДА Отклонения.Количество ИНАЧЕ 0 КОНЕЦ,
    ВЫБОР КОГДА Отклонения.ВидОтклонений.Бой ТОГДА Отклонения.Количество ИНАЧЕ 0 КОНЕЦ,
    ВЫБОР КОГДА Отклонения.ВидОтклонений.Недогруз ТОГДА Отклонения.Количество * Отклонения.Цена ИНАЧЕ 0 КОНЕЦ * Заявки.КоэффДефлятора,
    ВЫБОР КОГДА Отклонения.ВидОтклонений.Недовоз ТОГДА Отклонения.Количество * Отклонения.Цена ИНАЧЕ 0 КОНЕЦ * Заявки.КоэффДефлятора,
    ВЫБОР КОГДА Отклонения.ВидОтклонений.Возврат ТОГДА Отклонения.Количество * Отклонения.Цена ИНАЧЕ 0 КОНЕЦ * Заявки.КоэффДефлятора,
    ВЫБОР КОГДА Отклонения.ВидОтклонений.Бой ТОГДА Отклонения.Количество * Отклонения.Цена ИНАЧЕ 0 КОНЕЦ * Заявки.КоэффДефлятора,
    Заявки.ДатаПоставки,
    Заявки.Дата,
    Заявки.Номер,
    Заявки.ВидДокумента,
    Отклонения.Номенклатура.НоменклатураОснование
ИЗ
    ВТ_ОтобранныеЗаявки КАК Заявки
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.Заявка.Отклонения КАК Отклонения
        ПО Заявки.Ссылка = Отклонения.Ссылка

ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    Ссылка;

Аналогично в эту же таблицу через ОБЪЕДИНИТЬ ВСЕ добавляются ветки по документу ЗаявкаСклад с соединением по ВТ_ОтобранныеЗаявкиСклад.

Шаг 3. Оптимизация получения штрихкодов

В исходном варианте справочник штрихкодов соединялся со всей огромной таблицей строк. Чтобы ускорить эту операцию:

Выбираем уникальный список номенклатуры во временную таблицу:


ВЫБРАТЬ РАЗЛИЧНЫЕ
    Строки.Номенклатура КАК Номенклатура,
    Строки.НоменклатураОснование КАК НоменклатураОснование
ПОМЕСТИТЬ ВТ_УникальнаяНоменклатура
ИЗ
    ВТ_СтрокиДокументов КАК Строки
ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    НоменклатураОснование;

Получаем штрихкоды только для отобранных позиций без вложенных запросов:


ВЫБРАТЬ
    Номенклатура.Номенклатура КАК Номенклатура,
    ЕСТЬNULL(ШК1.Код, ШК2.Код) КАК ШК
ПОМЕСТИТЬ ВТ_Штрихкоды
ИЗ
    ВТ_УникальнаяНоменклатура КАК Номенклатура
    ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ШтрихКоды КАК ШК1
        ПО Номенклатура.Номенклатура = ШК1.Владелец
        И (НЕ ШК1.ПометкаУдаления)
        И ШК1.Единица.ВидЕдиницы = ЗНАЧЕНИЕ(Перечисление.ВидыЕдиниц.Базовая)
    ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ШтрихКоды КАК ШК2
        ПО Номенклатура.НоменклатураОснование = ШК2.Владелец
        И (НЕ ШК2.ПометкаУдаления)
ИНДЕКСИРОВАТЬ ПО
    Номенклатура;

Шаг 4. Финальная агрегация и получение строковых реквизитов

В результирующем запросе соединяем подготовленную таблицу ВТ_СтрокиДокументов с таблицей ВТ_Штрихкоды, выполняем группировку и расчет суммовых полей. Обращение к наименованиям и кодам через точку рекомендуется делать исключительно в итоговой выборке, либо передавать ссылки в Систему компоновки данных (СКД), которая автоматически оптимизирует получение представлений ссылочных типов.

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