Как оптимизировать запрос для понедельного отчета, который вызывает ошибку "недостаточно памяти"?

← На главную

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

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

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

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

Проблема №1: Неправильное получение остатков на каждую неделю

Основная ошибка в исходном коде — это попытка "притянуть" актуальный остаток к каждой неделе периода с помощью сложного соединения. Для этого создается календарь недель (`ТаблицаДат`), а затем он соединяется с таблицей остатков (`ВТ_Остатки`) по непрямому условию:


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

Условие ПО ТаблицаДат.Период >= ВТ_Остатки.Период заставляет СУБД для каждой недели и каждой номенклатуры перебирать все предыдущие записи об остатках, чтобы найти максимальную (то есть последнюю) дату. Это приводит к так называемому "взрывному" росту соединяемых записей, что и является главной причиной ошибки "недостаточно памяти".

Проблема №2: Сканирование регистров за весь период учета

В запросе для получения остатков не указаны границы периода. Это критическая ошибка:


ИЗ
    РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(, , НЕДЕЛЯ, Движения, ) КАК ТоварыНаСкладахОстаткиИОбороты

Пустые первые два параметра означают, что платформа 1С будет анализировать движения в регистре ТоварыНаСкладах с самого начала ведения учета. Если база данных существует несколько лет, объем анализируемых данных становится колоссальным, даже если в отчете выбран период в один месяц.

Проблема №3: Неэффективный способ получения цен

Для расчета себестоимости в запросе используется крайне сложный и медленный способ получения последней цены номенклатуры. Это многоуровневый вложенный запрос с использованием МАКСИМУМ(Период):


...
ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
    ВложенныйЗапрос.Номенклатура КАК Номенклатура,
    МАКСИМУМ(ВложенныйЗапрос1.Период) КАК Период
ИЗ
    (ВЫБРАТЬ
        ТоварыНаСкладах.Номенклатура КАК Номенклатура
    ИЗ
        РегистрНакопления.ТоварыНаСкладах КАК ТоварыНаСкладах) КАК ВложенныйЗапрос
        ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
            ЦеныНоменклатуры.Период КАК Период,
            ЦеныНоменклатуры.Номенклатура КАК Номенклатура
        ИЗ
            РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
        ГДЕ
            ЦеныНоменклатуры.ТипЦен = &ТипЦен) КАК ВложенныйЗапрос1
        ПО ВложенныйЗапрос.Номенклатура = ВложенныйЗапрос1.Номенклатура
СГРУППИРОВАТЬ ПО
    ВложенныйЗапрос.Номенклатура) КАК ВложенныйЗапрос
...

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

Реструктуризация и оптимизация запроса: пошаговое решение

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

Шаг 1. Используем виртуальную таблицу `ОстаткиИОбороты` правильно

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

Важные моменты:

Обязательно указываем параметры периода: &ДатаНачала и &ДатаОкончания.
Задаем периодичность: НЕДЕЛЯ.
Передаем отборы по номенклатуре и другим измерениям в параметры таблицы, а не в условие ГДЕ.

Это полностью избавляет нас от необходимости создавать таблицу ВТ_БлижайшиеДатыОстатков.

Пример получения данных по остаткам и оборотам:


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

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

Шаг 2. Получаем цены через виртуальную таблицу `СрезПоследних`

Для получения актуальных цен на определенную дату существует специальная виртуальная таблица РегистрСведений.ЦеныНоменклатуры.СрезПоследних. Она работает на порядок быстрее, чем самописные конструкции с МАКСИМУМ(Период).

Пример получения цен:


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

Этот запрос вернет нам по одной записи для каждой номенклатуры с самой последней ценой на конец периода отчета.

Шаг 3. Собираем итоговый отчет

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

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

Пример финального запроса (упрощенная структура):


// ... (здесь пакеты создания ВТ_Календарь, ВТ_ОстаткиИОбороты, ВТ_Продажи, ВТ_Цены)

// Не забываем индексировать временные таблицы!
ИНДЕКСИРОВАТЬ ВТ_ОстаткиИОбороты ПО Номенклатура, Период;
ИНДЕКСИРОВАТЬ ВТ_Продажи ПО Номенклатура, Период;
ИНДЕКСИРОВАТЬ ВТ_Цены ПО Номенклатура;

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

        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОстаткиИОбороты КАК Остатки
        ПО ВТ_Календарь.Период = Остатки.Период
            И Номенклатура.Ссылка = Остатки.Номенклатура

        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Продажи КАК Продажи
        ПО ВТ_Календарь.Период = Продажи.Период
            И Номенклатура.Ссылка = Продажи.Номенклатура
            
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Цены КАК Цены
        ПО Номенклатура.Ссылка = Цены.Номенклатура

УПОРЯДОЧИТЬ ПО
    Номенклатура,
    Период

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