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

← На главную

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

Разбор внутренней механики виртуальной таблицы

Для начала проанализируем ситуацию, описанную в исходном сообщении. При выполнении запроса к таблице РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты с периодичностью ДЕНЬ в SQL-профайлере наблюдается аномальное количество вызовов sp_execute и активная работа с временной таблицей #tt1. Выясним причину такого поведения.

Виртуальная таблица ОстаткиИОбороты не существует в базе данных физически. Это сложная конструкция, которую платформа генерирует «на лету» при обращении к ней. Когда мы указываем периодичность День, платформа вынуждена решать сложную задачу: сопоставить данные из таблицы итогов (обычно на начало месяца) с данными таблицы движений за все последующие дни, чтобы вычислить остаток на каждую дату внутри выбранного периода.

Рассмотрим, из чего «собирается» результат:

Получение начального остатка: Платформа обращается к таблице рассчитанных итогов _AccumRegTotals.
Досчет до даты начала: Если период начинается не с первого числа месяца, 1С вычисляет обороты от ближайшей точки итогов до ДатаНачала по основной таблице движений.
Развертка по периодам: Для каждого дня внутри выбранного интервала система должна рассчитать входящий остаток, приход, расход и исходящий остаток.

Проблема с #tt1, которую заметил автор темы, заключается в том, что платформа часто формирует временную таблицу со всеми комбинациями ключей аналитики (Номенклатура, Склад и т.д.) и периодов. Если в запросе нет отборов, и товаров тысячи, а период — месяц (30 дней), то 1С пытается «протянуть» остатки для каждой позиции через каждый день. Это порождает миллионы итераций и огромную нагрузку на tempdb.

Решение 1: Использование параметров виртуальной таблицы

Самая распространенная ошибка — наложение отборов в секции ГДЕ вместо параметров виртуальной таблицы. Разберем на примере. Посмотрим на неэффективный код:


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

В этом случае SQL-сервер сначала попытается вычислить остатки и обороты по всем складам, запишет их во временную таблицу, и только потом отфильтрует по нужному складу. Это крайне неэффективно. Проанализируем правильный подход:


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

Когда отбор перенесен в параметры, платформа передает его напрямую в SQL-запрос. Это позволяет оптимизатору использовать индексы и выбирать только те записи, которые относятся к конкретному складу, значительно сокращая объем данных в tempdb.

Решение 2: Индексация измерений регистра

Многие разработчики забывают, что при работе с виртуальными таблицами ОстаткиИОбороты платформа обращается не только к итогам, но и к основной таблице движений. В основной таблице индекс по умолчанию построен как Период + Регистратор + НомерСтроки. Если вы делаете отбор по измерению (например, Номенклатура), SQL-серверу придется сканировать всю таблицу движений за период.

Рекомендуем: Для измерений, по которым часто строятся отчеты через ОстаткиИОбороты, необходимо установить свойство «Индексировать». Это создаст дополнительный индекс в SQL (Dimension + Period), что позволит мгновенно находить движения по конкретному товару или складу. Как показывает практика, включение индексации на ключевых измерениях может ускорить такие запросы в десятки раз.

Решение 3: Комбинированный запрос вместо одной виртуальной таблицы

Если нам действительно нужны остатки на каждый день (например, для анализа оборачиваемости или простоев), стандартная виртуальная таблица может работать медленно даже с отборами. Рассмотрим альтернативный метод: получение данных через объединение таблицы остатков на начало периода и таблицы оборотов.

Проанализируем шаги реализации такого запроса:

Получаем Остатки на &ДатаНачала во временную таблицу.
Получаем Обороты с периодичностью ДЕНЬ во вторую временную таблицу.
Объединяем их и с помощью языка запросов (или программной обработки) вычисляем нарастающий итог.

Пример структуры такого запроса:


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

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

Такой подход зачастую работает быстрее, так как платформа использует гораздо более простые SQL-запросы к таблицам Остатки и Обороты по отдельности, не пытаясь построить монструозную конструкцию UNION ALL, характерную для ОстаткиИОбороты.

Решение 4: Порционная обработка данных

Если данных настолько много, что даже оптимизированный запрос вызывает таймаут, выясним причину. Часто это связано с нехваткой оперативной памяти для сортировки больших объемов данных на стороне SQL-сервера. В этом случае мы можем применить метод порционной выборки.

Рассмотрим алгоритм:

Выгружаем список всей необходимой номенклатуры (с учетом отборов) в массив.
Разбиваем массив на пакеты (например, по 500-1000 элементов).
В цикле выполняем запрос к ОстаткиИОбороты, передавая в параметры текущий пакет номенклатуры.
Результаты каждого шага объединяем в итоговой таблице значений.

Хотя количество запросов к базе увеличится, каждый отдельный запрос будет выполняться очень быстро, и общая нагрузка на сервер будет ниже, так как SQL-серверу не придется строить гигантские планы выполнения для всей базы сразу.

Решение 5: Создание оборотного регистра (Агрегаты)

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

Выясним, как это работает:

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

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

Выводы и рекомендации

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

Всегда передавайте отборы в параметры виртуальной таблицы, а не в ГДЕ.
Индексируйте измерения, которые используются в фильтрах.
Избегайте детализации День для больших периодов без жестких отборов по аналитике.
Если запрос все равно тормозит — разделяйте его на получение начального остатка и последующих оборотов.
Следите за состоянием tempdb и регулярно проводите обслуживание индексов и обновление статистики SQL-сервера.

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