Почему простой запрос к остаткам регистров накопления стал выполняться 40 минут вместо 1 секунды?

← На главную

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

Анализ проблемного запроса

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


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

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

Причина 1: Устаревание статистики СУБД и неоптимальный план выполнения

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

Вместо эффективных соединений Hash Match или Merge Join оптимизатор переключается на соединение вложенными циклами (Nested Loops). При таком сценарии для каждой из десятков тысяч строк левой таблицы СУБД выполняет отдельный поиск в правой таблице, что и приводит к замедлению выполнения до 40 минут.

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

Решение 1: Актуализация статистики и очистка кэша планов

Разберем регламентные действия на уровне СУБД (например, Microsoft SQL Server), которые необходимо выполнять при подобных симптомах:

Выполним обновление статистики по проблемным таблицам базы данных с полным сканированием (FULLSCAN). Это позволит оптимизатору корректно рассчитывать стоимость операторов соединения.
Если обновление статистики не помогло сбросить залипший неоптимальный план, выполним принудительную очистку процедурного кэша с помощью команды DBCC FREEPROCCACHE. В результате СУБД построит новый актуальный план при следующем вызове запроса.
Проверим настройку максимальной степени параллелизма (max degree of parallelism, MAXDOP). В высоконагруженных базах 1С некорректно выбранное значение параллелизма может провоцировать неэффективные планы запросов.

Причина 2: Проблемы точки актуальности и накопление нулевых итогов

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

Кроме того, со временем в таблицах итогов накапливаются записи с нулевыми остатками (когда весь приход был полностью списан). Физически эти строки остаются в таблице итогов, увеличивая размер индексов и время их сканирования.

Решение 2: Управление итогами регистров накопления

Чтобы исключить деградацию со стороны таблиц итогов, выполним следующие шаги:

Проверим актуальность границы итогов в обработке «Управление итогами» или программно с помощью метода РегистрыНакопления.УстановитьПериодРассчитанныхИтогов().
Выполним полный пересчет итогов через процедуру «Тестирование и исправление» либо методом РегистрыНакопления.ПересчитатьИтоги(). Полный пересчет физически очищает таблицу итогов от строк с нулевыми остатками.

Решение 3: Оптимизация текста запроса (Агрегация и Временные таблицы)

Полагаться только на оптимизатор СУБД рискованно. Грамотно спроектированный запрос должен минимизировать вероятность ошибки оптимизатора при любых объемах данных. Разберем два надежных подхода к переписыванию запроса.

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

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


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

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

Обратим внимание на функцию ЕСТЬNULL: при левом соединении для позиций без резерва значение поля будет равно NULL, поэтому его необходимо заменять на ноль для корректных математических операций.

Вариант Б: Замена соединения на объединение (ОБЪЕДИНИТЬ ВСЕ)

С точки зрения архитектуры СУБД один из самых быстрых и масштабируемых способов сопоставления данных двух регистров — объединение с итоговой группировкой:


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

    ОБЪЕДИНИТЬ ВСЕ

    ВЫБРАТЬ
        РезервыОстатки.Номенклатура,
        0,
        РезервыОстатки.КоличествоОстаток
    ИЗ
        РегистрНакопления.ПАУЭР_Резервы.Остатки(&Период, ) КАК РезервыОстатки) КАК Вложенный
СГРУППИРОВАТЬ ПО
    Вложенный.Номенклатура

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