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

← На главную

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

Анализ исходных условий и вариантов запросов

Рассмотрим регистр накопления остатков ТоварыНаСкладах со следующей последовательностью реквизитов:

Измерения: Склад, Номенклатура, ХарактеристикаНоменклатуры, СерияНоменклатуры, Качество.
Ресурсы: Количество, КоличествоЛ.

Проанализируем три типичных запроса на получение остатков номенклатуры:

Запрос 1 (Отбор только по номенклатуре):


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

Запрос 2 (Отбор по номенклатуре и складу):


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

Запрос 3 (Отбор по номенклатуре и качеству):


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

В чем отличие запросов с точки зрения производительности

Выясним причину, почему данные запросы будут кардинально отличаться по времени выполнения в реальной информационной базе:

Запрос № 2 — наиболее производительный. В условии отбора участвуют первые поля составного индекса без пропусков (Склад и Номенклатура). СУБД выполняет операцию поиска по индексу (Index Seek), мгновенно позиционируясь на нужной ветке данных.
Запрос № 1 — неоптимальный. В условии отбора пропущено первое измерение (Склад). СУБД не может выполнить точечный поиск по кластерному индексу и выполняет полное или частичное сканирование индекса (Index Scan), считывая значительно больше страниц с диска и из оперативной памяти.
Запрос № 3 — наименее производительный. Пропущено ведущее измерение Склад, а также измерения ХарактеристикаНоменклатуры и СерияНоменклатуры. Запрос приводит к сканированию индекса с последующей фильтрацией по предикату качества, что создает максимальную нагрузку.

Логика выполнения запросов на уровне СУБД

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

Кластерный индекс таблицы итогов формируется строго в порядке следования измерений в дереве конфигурации: Период + Измерение1 + Измерение2 + ... + ИзмерениеN. В нашем случае структура индекса выглядит так: Период + Склад + Номенклатура + ХарактеристикаНоменклатуры + СерияНоменклатуры + Качество.

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

Почему свойство «Индексировать» у измерения не решает проблему

Распространенное заблуждение — включить свойство «Индексировать» у измерения Номенклатура в надежде ускорить работу виртуальной таблицы остатков. Разберем, почему это не поможет:

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

Как добиться оптимальности выполнения данных запросов

Для оптимизации работы с регистром применим следующие инженерные подходы:

Перестановка измерений в конфигураторе. Если в подавляющем большинстве запросов требуется остаток конкретной позиции номенклатуры (например, при подборе в документы, контроле отрицательных остатков, формировании прайс-листов), необходимо переместить измерение Номенклатура на первое место в списке измерений регистра. Номенклатура обладает высокой селективностью (большое количество уникальных значений), что обеспечит мгновенную локализацию данных при поиске по индексу.
Передача всех ведущих измерений в параметры виртуальной таблицы. Если изменение структуры регистра невозможно, необходимо всегда явно передавать в параметры отбора виртуальной таблицы значение первого измерения (в исходном случае — Склад).
Использование архитектурных альтернатив. Если в системе одинаково часто используются тяжелые запросы с отбором только по складу (например, инвентаризация) и только по номенклатуре/качеству, рекомендуется разделить бизнес-логику на два отдельных регистра накопления с разным порядком измерений либо использовать периодический регистр сведений для специализированных срезов.

Влияние на управляемые блокировки и разделение итогов

Проанализируем сопутствующие риски неоптимальных запросов при параллельной работе пользователей:

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

Регламентные процедуры для поддержания высокой производительности

Для обеспечения стабильной скорости выполнения запросов к итогам регистров накопления администратор базы данных и программист должны контролировать выполнение следующих обязательных регламентов:

Сдвиг границы рассчитанных итогов платформы 1С. Необходимо настроить регламентное задание УправлениеИтогамиИАгрегатами. Граница итогов должна быть установлена на текущий или будущий месяц. Если граница отстает от текущей даты, любой запрос остатков будет считывать устаревший срез и динамически досчитывать остаток по таблице движений, что снижает скорость работы на порядки.
Пересчет итогов регистра. Регулярное выполнение пересчета итогов очищает неактуальные нулевые срезы (записи, где остаток по всем ресурсам равен 0), существенно уменьшая физический размер таблицы итогов.
Обновление статистик СУБД (UPDATE STATISTICS). Актуальная статистика позволяет оптимизатору СУБД строить корректные планы запросов и оценивать стоимость выборки по индексам.
Дефрагментация и реорганизация индексов (Reindex / Reorganize). Регулярное обслуживание индексов исключает фрагментацию страниц данных и предотвращает лишние операции ввода-вывода (I/O).
Очистка процедурного кэша (при необходимости). Сброс неоптимальных кэшированных планов выполнения после масштабных изменений данных или обновления статистик.