Почему оптимизация запроса в 1С помогла только на один день и как это исправить?

← На главную

Разберем ситуацию, которая часто встречается при работе с высоконагруженными и объемными базами данных «1С» (в нашем случае — более 800 ГБ). Проблема заключается в том, что после внесения изменений в код или выполнения регламентных процедур производительность системы резко возрастает, но спустя короткое время (например, через сутки) снова падает до прежних низких показателей. Рассмотрим причины этого явления и методы окончательного решения проблемы.

Анализируем причину: почему эффект оптимизации был временным?

Когда мы меняем текст запроса или пересчитываем статистику, СУБД (чаще всего Microsoft SQL Server) сбрасывает закэшированные планы выполнения. При первом запуске оптимизатор строит новый, максимально эффективный план. Однако существует несколько факторов, из-за которых производительность может «откатиться» назад:

Параметрический сниффинг (Parameter Sniffing): Это наиболее вероятная причина. При первом запуске SQL Server создает план на основе конкретных параметров запроса (например, выборка за один день). Если этот план попадает в кэш, то при следующем запуске с другими параметрами (например, выборка за год) СУБД попытается использовать тот же старый план, который окажется крайне неэффективным.
Неактуальная статистика: В базах объемом под 1 ТБ стандартные пороги автоматического обновления статистики могут не срабатывать. Если статистика «протухла», оптимизатор начинает ошибочно выбирать Index Scan (полное сканирование индекса) вместо Index Seek (поиск по индексу).
Фрагментация индексов и TempDB: При интенсивной записи данных индексы быстро фрагментируются. Кроме того, активное использование временных таблиц в запросах создает огромную нагрузку на базу TempDB, которая может стать «узким горлышком».

Разбор и оптимизация первого запроса: работа с остатками

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


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

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

Оптимизируем этот фрагмент, добавив индексацию:


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

Разбор и оптимизация второго запроса: проблема виртуальных таблиц в соединениях

Во втором запросе используется левое соединение с виртуальной таблицей СрезПоследних. Это одна из самых тяжелых операций в 1С на больших объемах данных.


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

Проанализируем выполнение этого кода СУБД. При соединении с виртуальной таблицей СрезПоследних платформа часто генерирует сложный подзапрос к физической таблице регистра с использованием функций MAX и группировок. Если во временной таблице ОстатокКредитногоПортфеля много строк, SQL Server может уйти в бесконечные циклы Nested Loops.

Решение: Сначала выберем данные среза последних во временную таблицу только для нужных нам займов, проиндексируем её, и только потом будем соединять.


// Шаг 1: Получаем список займов
ВЫБРАТЬ РАЗЛИЧНЫЕ Заем ПОМЕСТИТЬ СписокЗаймов ИЗ ОстатокКредитногоПортфеля;

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

Устранение неявных соединений (обращений через точку)

Выясним причину еще одного серьезного тормоза во втором запросе автора. Рассмотрим условие:

ГДЕ ОстатокКредитногоПортфеля.Заем.МетодОплатыВознаграждения = &МетодОплатыВознаграждения

В языке запросов 1С обращение через точку от ссылочного поля (Заем) к его реквизиту (МетодОплатыВознаграждения) приводит к неявному левому соединению со всей таблицей справочника. На базе в 800 ГБ таблица справочника Займы может содержать миллионы записей. Если такое условие стоит в блоке ГДЕ, это крайне неоптимально.

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

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

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

Обновление статистики перед выполнением: Настроим регламентное задание в SQL Server Management Studio (SSMS), которое будет выполнять UPDATE STATISTICS с опцией FULLSCAN для таблиц, участвующих в запросе, непосредственно за 15-20 минут до старта регламентного задания 1С.
Использование временных таблиц вместо подзапросов: Как мы выяснили, любая виртуальная таблица в соединении — это риск. Правило «один пакет — одна логическая операция» должно стать законом для разработчика.
Проверка актуальности итогов: Проанализируем состояние регистров накопления. Если итоги не рассчитаны на текущий месяц или есть записи далеко в будущем (ошибки ввода), получение остатков будет крайне медленным, так как системе придется вычислять их «на лету» от ближайшей рассчитанной точки.
Борьба с кэшированием планов: Если проблема параметрического сниффинга сохраняется, можно попробовать добавить в запрос «фиктивное» условие, которое будет меняться (например, текущее время в секундах), что заставит SQL Server каждый раз пересобирать план. Однако лучше использовать более чистые методы оптимизации кода, описанные выше.
Мониторинг дисковой активности: Выясним, не запускается ли в это же время на сервере процесс создания бэкапа или антивирусного сканирования. На базах такого объема конкуренция за дисковую подсистему (особенно за TempDB) фатальна для производительности.

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

← На главную