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

← На главную

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

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

---

Причина 1. Фильтрация данных в секции «ГДЕ» вместо параметров виртуальной таблицы

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


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

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

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

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

Правильное решение проблемы: перенос условий фильтрации непосредственно в параметры виртуальной таблицы. Посмотрим на оптимизированный вариант:


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

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

---

Причина 2. Избыточное использование «СГРУППИРОВАТЬ ПО» и функций агрегации

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

Проанализируем архитектуру виртуальных таблиц в 1С. Одно из их ключевых преимуществ — это автоматическая свертка (группировка) данных до уровня тех измерений, которые выбраны в секции ВЫБРАТЬ. Если в регистре присутствуют измерения Номенклатура, Склад, ХарактеристикаНоменклатуры, СерияНоменклатуры, но в полях выбора запроса вы указываете только Номенклатура, платформа 1С на уровне СУБД самостоятельно сгруппирует результат до номенклатуры, просуммировав все показатели.

Если мы оставляем явное ключевое слово СГРУППИРОВАТЬ ПО и функцию СУММА(), мы заставляем СУБД выполнять двойную работу:

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

Таким образом, использование группировок при выборке данных напрямую из виртуальных таблиц (без соединений с другими таблицами) является абсолютно избыточным. Запрос должен выглядеть следующим образом:


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

---

Причина 3. Грубая логическая ошибка: Суммирование остатков по периодам

Обратим внимание на последнее сообщение в теме форума. В нем справедливо указывается на полную бессмысленность операции суммирования остатков в сочетании с детализацией по периоду (в данном случае — по дням).

В чем логическая суть ошибки? Остаток — это моментный показатель. Его значение имеет физический смысл только на конкретную точку времени (например, на начало дня или на конец дня). Оборот же — это интервальный показатель (сколько пришло или ушло за весь день). Обороты складывать можно и нужно, а вот складывать остатки за разные периоды времени — математически некорректно.

Рассмотрим пример. Допустим, на складе лежит 10 штук товара. Движений по складу не происходит в течение 3 дней:

1 ноября: Начальный остаток = 10 шт.
2 ноября: Начальный остаток = 10 шт.
3 ноября: Начальный остаток = 10 шт.

Если мы применим функцию СУММА(КоличествоНачальныйОстаток) к этой выборке, то СУБД честно сложит эти три числа и выдаст результат: 30 шт. Но ведь реальный начальный остаток товара за этот трехдневный период равен 10 штукам, а не тридцати!

Как решать эту проблему правильно? Все зависит от того, каким образом вы выводите данные:

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

---

Особенности использования параметра «ДвиженияИГраницыПериода»

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

Важно помнить: если за весь месяц по товару не было ни одной операции движения, этот запрос вернет вам всего две строки — на дату начала и на дату окончания. Если вашей задачей является построение непрерывного ежедневного календаря остатков (чтобы в таблице присутствовал каждый день месяца, независимо от наличия движений), функционала одной лишь виртуальной таблицы ОстаткиИОбороты будет недостаточно. В таких случаях программисты создают временную таблицу дат (календарь) и связывают её левым соединением с виртуальной таблицей остатков на каждую конкретную дату.