Почему запрос к регистру накопления "Себестоимость товаров" с условием по типу регистратора выполняется медленно или блокирует систему?

← К списку

Уважаемые коллеги, давайте вместе разберем одну из частых и весьма неприятных проблем, с которой мы можем столкнуться при работе с конфигурацией 1С:ERP, а именно – медленное выполнение или полная блокировка системы из-за неоптимизированного запроса к регистру накопления, особенно когда в условиях отбора участвует поле составного типа, такое как Регистратор. Мы проанализируем типовую ситуацию и предложим эффективные пути решения.

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

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


ВЫБРАТЬ
	Количество(*)
ИЗ
	РегистрНакопления.СебестоимостьТоваров КАК СебестоимостьТоваров
ГДЕ
	ТИПЗНАЧЕНИЯ(СебестоимостьТоваров.Регистратор) = ТИП(Документ.ПриобретениеТоваровУслуг)

В нашем случае, этот запрос, выполняемый в конфигурации 1С:ERP 2.5.22.114, заблокировал миллионы объектов на три часа и так и не завершился. Мы видим, что в регистре накопления СебестоимостьТоваров содержится около 40 миллионов строк, из которых 1 миллион соответствует типу Документ.ПриобретениеТоваровУслуг. При этом запрос вызывается в рамках обмена данными, для каждого выгружаемого документа "Приобретение услуг и прочих активов". Выясним причину такого поведения и рассмотрим, как можно оптимизировать подобные запросы.

Пошаговая диагностика производительности

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

Анализ плана запроса в СУБД (SQL Profiler)
Первым делом, рекомендуем использовать инструменты для анализа выполнения запросов на стороне СУБД, такие как Microsoft SQL Server Profiler или аналогичные для PostgreSQL. Эти инструменты позволяют увидеть, какой именно план выполнения запроса строит сервер базы данных. Мы сможем определить, какие операции занимают больше всего времени: сканирование таблиц, сортировки, временные таблицы, неоптимальные соединения.

Как правило, медленный запрос с условием по составному типу может приводить к полному сканированию всей таблицы регистра или к построению множества неэффективных соединений.
Проверка и оптимизация индексов
Индексы — это основа быстрой работы с большими объемами данных. Для регистров накопления стандартные индексы включают Период + Регистратор + НомерСтроки и Регистратор + НомерСтроки. Давайте проверим, какие индексы существуют на таблице РегистрНакопления.СебестоимостьТоваров и насколько они эффективны для нашего запроса.

В нашем примере, добавление фиктивного условия на период (например, УчетСебестоимости.Период >= '01.01.2020') позволило активировать индекс Период + Регистратор и сократить время выполнения запроса на тестовой базе до 2 минут. Это говорит о том, что без такого условия, индекс не использовался, и СУБД выполняла полное сканирование.

Важно: Если у вас нет прямого доступа к SQL-серверу, запросите информацию об индексах у администраторов базы данных. Также стоит рассмотреть возможность создания дополнительных индексов по измерениям, если они часто используются в условиях отбора.

Оптимизация условий отбора по составному типу "Регистратор"

Основная проблема в нашем запросе заключается в использовании функции ТИПЗНАЧЕНИЯ для поля Регистратор, которое является составным типом.

Проблема составных типов в запросах
Поле Регистратор в регистрах накопления 1С может ссылаться на различные типы документов. Когда мы используем его в условиях, СУБД не может напрямую использовать индексы по этому полю без дополнительных преобразований. Более того, при обращении к реквизитам объекта через поле составного типа (например, Регистратор.Склад), происходит неявное соединение не с одной таблицей, а со всеми таблицами типов, входящих в составное поле. Это может привести к созданию большого количества левых соединений, что значительно замедляет выполнение запроса.
Решение 1: Использование функции ВЫРАЗИТЬ
Для оптимизации работы с полями составного типа 1С рекомендует использовать функцию ВЫРАЗИТЬ(Поле КАК Тип). Эта функция позволяет явно указать оптимизатору СУБД, с каким типом данных мы работаем, что помогает ему выбрать более эффективный план запроса.

Рассмотрим пример, как мы можем модифицировать наш запрос:
```
ВЫБРАТЬ
    Количество(*)
ИЗ
    РегистрНакопления.СебестоимостьТоваров КАК СебестоимостьТоваров
ГДЕ
    ВЫРАЗИТЬ(СебестоимостьТоваров.Регистратор КАК Документ.ПриобретениеТоваровУслуг) ССЫЛКА Документ.ПриобретениеТоваровУслуг
```
Использование ВЫРАЗИТЬ(...) в условиях отбора может дать значительный прирост производительности, особенно если далее мы обращаемся к реквизитам этого документа "через точку" (например, ВЫРАЗИТЬ(СебестоимостьТоваров.Регистратор КАК Документ.ПриобретениеТоваровУслуг).Склад). В таком случае, оптимизатор СУБД сможет быстрее получить необходимые данные.
Решение 2: Использование конструкции Ссылка Документ.ВидДокумента
Эта конструкция является более лаконичной и эквивалентна использованию ТИПЗНАЧЕНИЯ(Регистратор) = ТИП(Документ.ВидДокумента), но может быть более понятной для оптимизатора СУБД.

Посмотрим на пример:
```
ВЫБРАТЬ
    Количество(*)
ИЗ
    РегистрНакопления.СебестоимостьТоваров КАК СебестоимостьТоваров
ГДЕ
    СебестоимостьТоваров.Регистратор ССЫЛКА Документ.ПриобретениеТоваровУслуг
```
Мы рекомендуем попробовать оба варианта (`ВЫРАЗИТЬ` и `Ссылка`) и сравнить производительность, так как результаты могут зависеть от версии платформы 1С, СУБД и структуры данных.

Применение временных таблиц (ВТ) для сложных запросов

В некоторых случаях, когда запрос становится слишком сложным, содержит множество соединений, подзапросов или агрегатных функций (например, ИМЕЮЩИЕ СУММА(...) > 0), использование временных таблиц может значительно улучшить производительность.

Рассмотрим подробнее:

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

Пример использования ВТ (концептуально):

Предположим, наш запрос был более сложным и включал агрегацию, как это обсуждалось в форуме, например, ИМЕЮЩИЕ СУММА(УчетСебестоимости.Количество) > 0. Мы могли бы сначала выбрать нужные регистраторы в ВТ:


ВЫБРАТЬ
    СебестоимостьТоваров.Регистратор КАК Регистратор
ПОМЕСТИТЬ ВТ_Регистраторы
ИЗ
    РегистрНакопления.СебестоимостьТоваров КАК СебестоимостьТоваров
ГДЕ
    СебестоимостьТоваров.Регистратор ССЫЛКА Документ.ПриобретениеТоваровУслуг
СГРУППИРОВАТЬ ПО
    СебестоимостьТоваров.Регистратор
ИМЕЮЩИЕ
    СУММА(СебестоимостьТоваров.Количество) > 0;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Количество(*)
ИЗ
    ВТ_Регистраторы КАК ВТ_Регистраторы

Этот подход позволяет разбить сложный запрос на более простые шаги, каждый из которых может быть оптимизирован.

Дополнительные методы оптимизации запросов и системы в целом

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

Перенос условий из ГДЕ в условия СОЕДИНЕНИЯ
Если вы используете внутренние соединения (ВНУТРЕННЕЕ СОЕДИНЕНИЕ), то все условия, относящиеся к соединяемым таблицам, можно и нужно переносить из секции ГДЕ в секцию ПО условия соединения. Это также помогает оптимизатору СУБД лучше понять логику запроса и построить более эффективный план.
Ограничение по периоду
Как мы уже выяснили, добавление условия на период (например, УчетСебестоимости.Период >= '01.01.2020') может активировать индексы по полю Период и значительно сократить объем сканируемых данных. Всегда старайтесь максимально сужать выборку данных с помощью временных границ.
Анализ контекста вызова запроса
В нашей ситуации запрос выполняется для каждого выгружаемого документа "Приобретение услуг и прочих активов" во время синхронизации данных. Если этот запрос сам по себе занимает 2 минуты (даже после оптимизации), а таких документов тысячи, то общее время выполнения операции синхронизации будет неприемлемым.

Мы должны проанализировать, действительно ли этот запрос должен выполняться так часто. Возможно, есть способы кешировать результаты, выполнять запрос один раз для пакета документов или пересмотреть логику обмена, чтобы избежать такой нагрузки.
Аппаратное обеспечение и настройки СУБД
Не забывайте проверять настройки аппаратного обеспечения сервера (например, режим энергосбережения на сервере SQL) и оптимизировать настройки СУБД (PostgreSQL или Microsoft SQL Server). Даже самый оптимизированный запрос будет работать медленно на слабом или некорректно настроенном сервере.
Регулярное обновление платформы и конфигураций
Обновления 1С часто содержат исправления ошибок и оптимизации производительности, которые могут быть критически важны для работы системы. Убедитесь, что вы используете актуальные версии платформы и конфигурации ERP.
Анализ технологического журнала 1С
Для выявления долгих запросов и их оптимизации, особенно в контексте расчета себестоимости и других регламентных операций, эффективно использовать анализ технологического журнала 1С. Он позволит точно определить, какие запросы и в каком контексте вызывают проблемы.

В заключение, решение проблем с производительностью в 1С:ERP требует комплексного подхода. Мы должны не только оптимизировать код запросов, но и анализировать контекст их выполнения, проверять индексы, настройки СУБД и общую архитектуру системы. Совместными усилиями мы сможем добиться стабильной и быстрой работы нашей системы!

← К списку