Как заставить 1С эффективно использовать секционирование таблиц итогов в MS SQL Server?

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

Суть проблемы: Почему секционирование «не работает» в 1С?

Рассмотрим ситуацию: вы выполнили секционирование таблицы итогов _AccumRgT по полю _Period (например, по годам). Логично ожидать, что при запросе актуальных остатков (где _Period = '5999-11-01') SQL Server должен мгновенно обращаться только к одной последней секции. Но на практике 1С генерирует запрос через процедуру sp_executesql, передавая дату как параметр.

Проанализируем типичный кусок кода, который ловится в SQL Profiler:

exec sp_executesql N'SELECT ...
FROM dbo._AccumRgT27531 T2 WITH(NOLOCK)
WHERE T2._Period = @P1 ...', N'@P1 datetime2(3)', '5999-11-01 00:00:00'

В этом случае оптимизатор MS SQL Server часто не может заранее определить, в какой секции находятся данные, так как значение @P1 становится известно только в момент выполнения (динамическая фильтрация). Вместо обращения к одной секции, сервер «поднимает» (сканирует) все секции таблицы. Разница в производительности может быть колоссальной: 1 секунда при прямом указании константы против нескольких минут при использовании параметров 1С.

Решение 1: Использование прямых SQL-запросов

Самый надежный способ заставить СУБД использовать преимущества секционирования — это обойти механизм генерации запросов платформы. Если мы передаем дату как литерал (константу), механизм Partition Elimination (исключение секций) срабатывает безотказно.

Для реализации этого метода можно использовать ADODB.Connection или Внешние источники данных. Выясним, как должен выглядеть запрос, чтобы SQL Server прочитал только нужную секцию:

-- Прямой запрос к таблице итогов
SELECT _Fld1, SUM(_Fld2)
FROM dbo._AccumRgT27531 WITH(NOLOCK)
WHERE _Period = '5999-11-01 00:00:00' -- Указываем дату константой
  AND _Fld3RRef IN (SELECT Ссылка FROM #TempTable)
GROUP BY _Fld1

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

Решение 2: Пересчет и оптимизация итогов в 1С

Часто проблема медленной работы кроется не только в секциях, но и в «мусоре» внутри таблицы итогов. Выясним важную особенность: платформа 1С при активном использовании Разделения итогов может оставлять миллионы избыточных записей (нулевых или несхлопнутых).

Проанализируем шаги для исправления ситуации стандартными средствами:

1. Выполните полный пересчет итогов через конфигуратор или обработку «Управление итогами». Это «схлопнет» записи и может уменьшить размер таблицы итогов в 2–3 раза.
2. Если используется платформа 8.3.5 и выше, попробуйте метод УстановитьМинимальныйПериодРассчитанныхИтогов(). Это позволит физически удалить старые итоги (например, за 2010–2015 годы), которые вам уже не нужны в оперативном доступе.
3. Проверьте наличие пустых записей в регистре. 1С не всегда эффективно очищает итоги с нулевыми ресурсами при включенном разделении итогов.

Решение 3: Использование Plan Guides (Сценарные планы SQL)

Если вы не хотите отказываться от запросов на языке 1С, существует «хитрое» решение на стороне СУБД — использование Plan Guides. Рассмотрим этот механизм: он позволяет принудительно заставить SQL Server использовать определенный план или хинты для конкретного текста запроса.

Вы можете создать структуру, которая будет ловить запрос от 1С и добавлять к нему подсказку OPTION(RECOMPILE). Это заставит SQL Server перекомпилировать план при каждом выполнении с учетом текущих значений параметров (включая дату периода), что позволит активировать исключение секций.

Решение 4: Оптимизация структуры индекса и условий отбора

Посмотрим на структуру кластерного индекса таблицы итогов. Обычно он начинается с поля _Period. Если в вашем запросе 1С в условии ГДЕ виртуальной таблицы используется сложный отбор (например, В (ВЫБРАТЬ...) по 50 тысячам строк), оптимизатор может выбрать Index Scan вместо Index Seek.

Рассмотрим, как можно улучшить ситуацию:

• Индексация временных таблиц: Если вы используете конструкцию Измерение В (ВЫБРАТЬ Ссылка ИЗ ВТ_Отбор), обязательно индексируйте ВТ_Отбор по полю Ссылка. Это поможет SQL Server выбрать Merge Join или Nested Loops.
• Уменьшение количества параметров: Если список значений в отборе огромен (десятки тысяч), SQL Server может отказаться от использования индекса из-за высокой стоимости поиска для каждой строки. В таких случаях эффективнее сначала выбрать все данные по периоду во временную таблицу, а затем соединить их с фильтром.

Практические рекомендации

Проанализируем итоговый алгоритм действий для администратора БД и ведущего разработчика:

1. Проверьте актуальность статистики: Секционированные таблицы крайне чувствительны к актуальности статистики. Регулярно выполняйте UPDATE STATISTICS.
2. Включите флаг трассировки 4199: 1С рекомендует этот флаг для активации исправлений оптимизатора SQL Server, которые могут помочь в работе с параметризованными запросами.
3. Мониторинг через Query Store: В современных версиях SQL Server (2016+) используйте Query Store, чтобы найти запросы, которые внезапно сменили план с «одной секции» на «все секции».
4. Разнесение данных: Секционирование позволяет разместить актуальную секцию (текущий год) на быстрых SSD-дисках, а архивные — на медленных HDD. Это даст прирост скорости даже при полном сканировании секции.

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