Почему запрос в 1С на точную границу месяца выполняется в 30 раз медленнее, чем со смещением в одну секунду?

← На главную

В процессе разработки и оптимизации запросов в системе "1С:Предприятие" разработчики иногда сталкиваются с удивительными парадоксами производительности. Один из наиболее ярких примеров — резкое изменение времени выполнения запроса при сдвиге параметра даты всего на одну секунду. Рассмотрим классическую ситуацию: запрос по остаткам регистра накопления на дату 30.09.2018 23:59:59 выполняется за 1 секунду, на 01.10.2018 00:00:00 — за 30 секунд, а на 01.10.2018 00:00:01 — снова за 1 секунду.

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

Анализ исходного запроса и сути проблемы

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


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

Этот запрос демонстрирует нестабильное время работы в зависимости от передаваемого параметра Период. Изменение времени на одну секунду кардинально меняет поведение СУБД. Проанализируем, почему это происходит.

Выясняем причину: как платформа 1С получает остатки

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

Таблица движений (_AccumRg) — хранит все записи (регистрации хозяйственных операций), сделанные документами.
Таблица итогов (_AccumRgT) — хранит рассчитанные остатки на начало каждого месяца (точнее, на 01.01.0001 00:00:00, 01.02.0001 00:00:00 и т.д.), а также актуальные (текущие) итоги на максимальную дату (в SQL это дата 5999-11-01 00:00:00).

Когда мы обращаемся к виртуальной таблице Остатки, транслятор запросов платформы (компилятор SDBL) генерирует различные SQL-запросы в зависимости от того, попадает ли целевая дата на рассчитанную точку итогов:

Сценарий 1. Запрос на точную дату начала месяца (например, 01.10.2018 00:00:00)
Платформа "знает", что на эту дату в базе данных уже есть готовые рассчитанные остатки в таблице помесячных итогов (_AccumRgT). Ей нет необходимости обращаться к таблице движений и производить какие-либо математические вычисления. Транслятор строит максимально простой SQL-запрос, который напрямую читает данные из таблицы итогов за указанный месяц.

Сценарий 2. Запрос на "неграничную" дату (например, 30.09.2018 23:59:59 или 01.10.2018 00:00:01)
Для таких дат готовых итогов в таблице _AccumRgT нет. Платформа вынуждена "докручивать" остатки. Она берет ближайшую известную точку итогов (обычно это точка актуальных текущих итогов на 5999-11-01) и выполняет сложный запрос с конструкцией UNION ALL, в котором вычитает или прибавляет движения из таблицы _AccumRg за период от целевой даты до точки актуальности. В этом SQL-запросе обязательно присутствует группировка GROUP BY и фильтрация по условию HAVING SUM(...) <> 0.

Феномен "нулевых итогов" и его влияние на левое соединение

Казалось бы, прямой запрос к таблице готовых итогов на дату 01.10.2018 00:00:00 должен выполняться мгновенно, так как СУБД делает меньше математических операций. Но на практике он работает в 30 раз медленнее. Почему?

Ответ кроется в наличии нулевых записей в таблице помесячных итогов и особенностях выполнения операции LEFT JOIN оптимизатором СУБД.

В процессе работы базы данных (особенно в старых, активно используемых и переписанных конфигурациях, таких как УПП 1.3) в таблице итогов накапливается огромное количество пустых записей. Когда взаиморасчеты закрываются в ноль, в таблице итогов текущего месяца ресурсы (сумма, количество) становятся равными нулю. Физически эти строки не удаляются автоматически СУБД, они продолжают храниться в таблице итогов _AccumRgT со значениями ресурсов 0.00.

Проанализируем поведение СУБД при выполнении двух типов запросов:

При запросе на "быструю" дату (01.10.2018 00:00:01) сложный SQL-запрос с UNION ALL предварительно сворачивает данные с помощью секции GROUP BY и жестко отсекает все нулевые остатки конструкцией HAVING SUM(...) <> 0. СУБД получает очень маленький, "чистый" набор данных, и только после этого выполняет LEFT JOIN с таблицей движений. Операция соединения выполняется мгновенно.
При запросе на "медленную" дату (01.10.2018 00:00:00) платформа обращается к таблице итогов напрямую. Из-за отсутствия вычислений в этом запросе нет группировки и автоматического отсечения нулей. СУБД пытается выполнить LEFT JOIN физической таблицы со всей таблицей итогов за месяц, в которой содержатся десятки (или сотни) тысяч записей с нулевыми остатками. Ситуация усугубляется тем, что соединение идет по составному измерению ДокументРасчетовСКонтрагентом (которое на уровне СУБД состоит из трех полей: тип, идентификатор таблицы и ссылка). Сопоставление огромного количества мусорных "нулевых" строк по тяжелому составному ключу приводит к лавинообразному росту нагрузки на дисковую подсистему и процессор.

Проанализируем также влияние механизма Parameter Sniffing (чувствительности к параметрам). Оптимизатор MS SQL Server строит план выполнения запроса при его первом запуске. Если первый запуск произошел с параметром границы месяца, СУБД строит план под прямое чтение таблицы итогов. При устаревшей статистике оптимизатор может выбрать неэффективный тип соединения (например, Nested Loops вместо Hash Match), что превращает выполнение запроса в бесконечный процесс.

Если выполнить простой запрос остатков без левого соединения на дату 01.10.2018 00:00:00, он отработает быстро, поскольку СУБД просто прочитает таблицу итогов без ресурсоемкого сопоставления строк. Тормоза возникают именно на стыке прямого обращения к таблице итогов и операции LEFT JOIN.

Решение проблемы: практические методы оптимизации

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

Способ 1. Оптимизация запроса с помощью временных таблиц

Самый надежный и архитектурно правильный способ — отказаться от соединения виртуальной таблицы остатков "на лету". Перепишем запрос по правилам разработки высоконагруженных систем 1С. Разделим его выполнение на шаги:

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

Рассмотрим пример реализации этого решения:


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

////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Индексируем временную таблицу для быстрого соединения
ИНДЕКСИРОВАТЬ ПО
    Контрагент,
    ДокументРасчетовСКонтрагентом
;

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

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

Способ 2. Очистка и пересчет итогов в 1С

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

Запустим базу данных в режиме "Конфигуратор".
Откроем меню Администрирование — Тестирование и исправление.
Установим флажок напротив пункта Реиндексация таблиц информационной базы и Пересчет итогов.
Нажмем кнопку Выполнить.

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

Также полезно использовать стандартную обработку "Управление итогами" (в режиме "1С:Предприятие" меню Все функции — Стандартные — Управление итогами). Если база данных ведется много лет, установим минимальный период хранения итогов (например, начало текущего года). Итоги за прошлые, давно закрытые периоды будут удалены, что существенно разгрузит СУБД. При обращении к историческим данным за старые года остатки будут рассчитываться динамически по движениям.

Способ 3. Обслуживание СУБД и обновление статистики

Для предотвращения проблем с выбором неоптимальных планов запросов (Parameter Sniffing) на стороне MS SQL Server настроим регулярные регламентные задания:

Обновление статистики (Update Statistics): Настроим ежедневное обновление статистики для таблиц баз данных 1С с параметром FULLSCAN. Это позволит оптимизатору SQL всегда знать точное количество строк и распределение данных в таблицах итогов и движений.
Очистка процедурного кэша: При существенном изменении данных в регистрах полезно сбрасывать кэш планов запросов СУБД, чтобы старые, неэффективные планы не использовались повторно.

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