Как исправить ошибку с периодом в запросе 1С и оптимизировать получение среза последних на каждую дату

← На главную

При разработке отчетов и обработок в среде 1С программисты часто сталкиваются с необходимостью получения периодических данных (например, цен номенклатуры, курсов валют или скидок) актуальных на момент проведения конкретных документов. Эта задача классически называется «срез последних на каждую дату документа». В процессе ее реализации легко совершить как простую логическую ошибку в параметрах, так и тяжелую архитектурную ошибку, которая парализует работу СУБД на реальной многопользовательской базе.

В этой статье мы подробно разберем, почему у автора темы не работал отбор по периоду документов, как исправить эту досадную оплошность, а также проведем глубокий аудит исходного запроса и оптимизируем его по всем канонам стандартов разработки 1С.

Разбор исходной ошибки: почему не работал отбор по дате

Автор исходного кода пытался наложить условие на период выборки документов, используя стандартную конструкцию:

Ссылка.Дата МЕЖДУ &ПериодНачало И &ПериодКонец

Но при выполнении запроса отчет упорно возвращал пустой результат или некорректные данные. Как выяснилось в ходе обсуждения, причина крылась не в синтаксисе языка запросов 1С, а в банальной человеческой невнимательности при передаче параметров в код на встроенном языке. При заполнении параметров запроса в коде значения были перепутаны местами:


// Ошибка, приведшая к неработающему запросу:
Запрос.УстановитьПараметр("ПериодНачало", КонецПериода);
Запрос.УстановитьПараметр("ПериодКонец", НачалоПериода);

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

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

Давайте внимательно проанализируем структуру исходного запроса автора темы. На небольших тестовых базах он будет работать мгновенно, но при росте объема данных (более нескольких тысяч документов) система начнет зависать. Выделим три ключевые проблемы этого кода.

Проблема №1: Разыменование полей через точку (неявные соединения)

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

ПриобретениеТоваровУслугТовары.Ссылка.Партнер
ПриобретениеТоваровУслугТовары.Ссылка.Дата
ПриобретениеТоваровУслугТовары.Ссылка.Проведен

Каждое подобное обращение «через точку» транслируется платформой 1С в СУБД как неявное левое соединение (LEFT JOIN) таблицы табличной части с таблицей шапки документа. Поскольку эти поля используются не только в секции выбора, но и в условиях связи (ПО ... >= ЦеныНоменклатурыПоставщиков.Период) и в блоке фильтрации (ГДЕ Ссылка.Проведен), СУБД строит крайне сложный и неэффективный план запроса.

Проблема №2: Неоптимальное условие связи по неравенству (декартово произведение)

Самая критическая архитектурная ошибка кроется в условии левого соединения табличной части с физической таблицей регистра сведений:

ПО ПриобретениеТоваровУслугТовары.Ссылка.Дата >= ЦеныНоменклатурыПоставщиков.Период

Поскольку для СУБД это условие соединения по неравенству, оптимизатор не может использовать классический поиск по индексам (Index Seek) в полной мере. Для каждой строки документа СУБД вынуждена сопоставлять все записи из регистра цен по этой номенклатуре, которые были внесены до даты документа. Если история цен ведется давно и по каждой позиции накоплены десятки изменений, СУБД сформирует гигантскую промежуточную выборку (декартово произведение строк), которая затем будет мучительно сгруппирована с использованием агрегатной функции МАКСИМУМ.

Это приводит к избыточной нагрузке на процессор сервера СУБД, переполнению дисковой памяти (база tempdb в MS SQL Server) и длительным блокировкам таблиц.

Решение проблемы: Правильный паттерн «Срез последних на каждую дату»

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

Шаг 1: Извлечение уникальных аналитик и дат документов

Сначала мы выберем только те комбинации номенклатуры, видов цен и дат документов, для которых нам вообще требуется получить актуальную цену. Это позволит отсечь дубли и значительно уменьшить объем обрабатываемых данных. На этом же этапе мы накладываем жесткий фильтр по периоду документов.

Шаг 2: Поиск дат актуальных цен

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

Шаг 3: Получение самих ценовых показателей

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

Шаг 4: Финальное объединение с документами

На последнем этапе мы соединяем исходные документы с полученной плоской таблицей цен.

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


ВЫБРАТЬ
    ПриобретениеТоваровУслугТовары.Номенклатура КАК Номенклатура,
    ПриобретениеТоваровУслугТовары.ВидЦеныПоставщика КАК ВидЦеныПоставщика,
    ПриобретениеТоваровУслуг.Дата КАК ДатаДокумента
ПОМЕСТИТЬ ВТ_УникальныеТребования
ИЗ
    Документ.ПриобретениеТоваровУслуг.Товары КАК ПриобретениеТоваровУслугТовары
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ПриобретениеТоваровУслуг КАК ПриобретениеТоваровУслуг
        ПО ПриобретениеТоваровУслугТовары.Ссылка = ПриобретениеТоваровУслуг.Ссылка
ГДЕ
    ПриобретениеТоваровУслуг.Проведен
    И ПриобретениеТоваровУслуг.Дата МЕЖДУ &ПериодНачало И &ПериодКонец

СГРУППИРОВАТЬ ПО
    ПриобретениеТоваровУслугТовары.Номенклатура,
    ПриобретениеТоваровУслугТовары.ВидЦеныПоставщика,
    ПриобретениеТоваровУслуг.Дата

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_УникальныеТребования.Номенклатура КАК Номенклатура,
    ВТ_УникальныеТребования.ВидЦеныПоставщика КАК ВидЦеныПоставщика,
    ВТ_УникальныеТребования.ДатаДокумента КАК ДатаДокумента,
    МАКСИМУМ(ЦеныНоменклатурыПоставщиков.Период) КАК ПериодЦены
ПОМЕСТИТЬ ВТ_МаксимальныеДатыЦен
ИЗ
    ВТ_УникальныеТребования КАК ВТ_УникальныеТребования
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатурыПоставщиков КАК ЦеныНоменклатурыПоставщиков
        ПО ВТ_УникальныеТребования.Номенклатура = ЦеныНоменклатурыПоставщиков.Номенклатура
        И ВТ_УникальныеТребования.ВидЦеныПоставщика = ЦеныНоменклатурыПоставщиков.ВидЦеныПоставщика
        И ВТ_УникальныеТребования.ДатаДокумента >= ЦеныНоменклатурыПоставщиков.Период

СГРУППИРОВАТЬ ПО
    ВТ_УникальныеТребования.Номенклатура,
    ВТ_УникальныеТребования.ВидЦеныПоставщика,
    ВТ_УникальныеТребования.ДатаДокумента

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_МаксимальныеДатыЦен.Номенклатура КАК Номенклатура,
    ВТ_МаксимальныеДатыЦен.ВидЦеныПоставщика КАК ВидЦеныПоставщика,
    ВТ_МаксимальныеДатыЦен.ДатаДокумента КАК ДатаДокумента,
    ЕСТЬNULL(ЦеныНоменклатурыПоставщиков.Цена, 0) КАК Цена,
    ЕСТЬNULL(ЦеныНоменклатурыПоставщиков.Валюта, ЗНАЧЕНИЕ(Справочник.Валюты.ПустаяСсылка)) КАК Валюта
ПОМЕСТИТЬ ВТ_ИтоговыеЦены
ИЗ
    ВТ_МаксимальныеДатыЦен КАК ВТ_МаксимальныеДатыЦен
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатурыПоставщиков КАК ЦеныНоменклатурыПоставщиков
        ПО ВТ_МаксимальныеДатыЦен.Номенклатура = ЦеныНоменклатурыПоставщиков.Номенклатура
        И ВТ_МаксимальныеДатыЦен.ВидЦеныПоставщика = ЦеныНоменклатурыПоставщиков.ВидЦеныПоставщика
        И ВТ_МаксимальныеДатыЦен.ПериодЦены = ЦеныНоменклатурыПоставщиков.Период

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

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

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

Явное соединение: Мы убрали все обращения к шапке через точку и связали табличную часть с документом через ВНУТРЕННЕЕ СОЕДИНЕНИЕ по индексному полю Ссылка.
Индексация временных таблиц: Мы проиндексировали все временные таблицы по полям связи. Это позволяет СУБД мгновенно сопоставлять данные, используя алгоритм Merge Join или Index Seek.
Минимизация данных для нелинейных связей: Поиск максимальной даты по условию >= производится только для уникального набора строк в таблице ВТ_УникальныеТребования, а не для всей массы дублирующихся строк из документов.

Альтернативное решение: Использование связей наборов данных в СКД

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

Давайте пошагово рассмотрим, как настроить такую схему:

Создаем Набор данных - запрос №1 (Документы): Выбираем из документов только те данные, которые нас интересуют (без обращения к регистру цен).


ВЫБРАТЬ
    ПриобретениеТоваровУслугТовары.Ссылка КАК Ссылка,
    ПриобретениеТоваровУслугТовары.Ссылка.Дата КАК Дата,
    ПриобретениеТоваровУслугТовары.Ссылка.Партнер КАК Партнер,
    ПриобретениеТоваровУслугТовары.Номенклатура КАК Номенклатура,
    ПриобретениеТоваровУслугТовары.ВидЦеныПоставщика КАК ВидЦеныПоставщика
ИЗ
    Документ.ПриобретениеТоваровУслуг.Товары КАК ПриобретениеТоваровУслугТовары
ГДЕ
    ПриобретениеТоваровУслугТовары.Ссылка.Проведен
    И ПриобретениеТоваровУслугТовары.Ссылка.Дата МЕЖДУ &ПериодНачало И &ПериодКонец

Создаем Набор данных - запрос №2 (Цены): Пишем простой запрос к виртуальной таблице среза последних с использованием параметров.


ВЫБРАТЬ
    ЦеныНоменклатурыПоставщиковСрезПоследних.Номенклатура КАК Номенклатура,
    ЦеныНоменклатурыПоставщиковСрезПоследних.ВидЦеныПоставщика КАК ВидЦеныПоставщика,
    ЦеныНоменклатурыПоставщиковСрезПоследних.Цена КАК Цена,
    ЦеныНоменклатурыПоставщиковСрезПоследних.Валюта КАК Валюта
ИЗ
    РегистрСведений.ЦеныНоменклатурыПоставщиков.СрезПоследних(&ПериодЦен, Номенклатура = &ПараметрНоменклатура И ВидЦеныПоставщика = &ПараметрВидЦены) КАК ЦеныНоменклатурыПоставщиковСрезПоследних

Настраиваем связи наборов данных: Переходим на вкладку «Связи наборов данных» и добавляем связь:
- Источник связи: НаборДанных1.
- Приемник связи: НаборДанных2.
- Условие связи: Номенклатура = Номенклатура И ВидЦеныПоставщика = ВидЦеныПоставщика.
- Параметры: Имя параметра — ПериодЦен, выражение источника — Дата. Имя параметра — ПараметрНоменклатура, выражение источника — Номенклатура. Имя параметра — ПараметрВидЦены, выражение источника — ВидЦеныПоставщика.

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