Как оптимизировать запрос к регистру бухгалтерии при использовании ИЛИ, В ИЕРАРХИИ и условий по субконто?

← На главную

Разработчики в 1С часто сталкиваются с проблемой низкой производительности запросов к регистру бухгалтерии. Особенно остро эта проблема проявляется при попытке наложить сложные условия на субконто, счета и организации непосредственно в параметрах виртуальных таблиц. Типичный пример неоптимального кода включает использование логического оператора ИЛИ для условий по дебету и кредиту, использование конструкции В ИЕРАРХИИ для плана счетов, а также неявные подзапросы к временным таблицам.

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

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

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


Организация = &Организация
И (СчетДт В ИЕРАРХИИ (&СчетГруппа)
        И СубконтоДт1 В
            (ВЫБРАТЬ
                ВТ_Контрагенты.Контрагент
            ИЗ
                ВТ_Контрагенты)
    ИЛИ СчетКт В ИЕРАРХИИ (&СчетГруппа)
        И СубконтоКт1 В
            (ВЫБРАТЬ
                ВТ_Контрагенты.Контрагент
            ИЗ
                ВТ_Контрагенты))

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

Использование логического "ИЛИ" между измерениями Дт и Кт. Для СУБД (будь то MS SQL Server или PostgreSQL) наличие ИЛИ в условии соединения или фильтрации по разным колонкам — это критическая преграда для использования индексов. СУБД не может применить точечный поиск по индексам дебета и кредита одновременно. В результате оптимизатор СУБД принимает решение полностью отказаться от поиска по индексу (Index Seek) и выполняет сканирование всей таблицы (Index Scan или Table Scan) за указанный период.
Подзапросы во временных таблицах внутри параметров ВТ. Конструкция СубконтоДт1 В (ВЫБРАТЬ ... ИЗ ВТ_Контрагенты) заставляет платформу генерировать сложный вложенный SQL-запрос. Оптимизатору СУБД крайне сложно корректно оценить кардинальность (количество строк) такого подзапроса до его выполнения. Ошибка в оценке приводит к выбору неэффективного плана соединения (например, тяжелый Nested Loops вместо быстрого Hash Match).
Конструкция "В ИЕРАРХИИ" для плана счетов. Платформа 1С транслирует В ИЕРАРХИИ в SQL-запрос с рекурсивным обходом таблицы плана счетов. Это создает дополнительную нагрузку на сервер СУБД, хотя список субсчетов практически никогда не меняется динамически в процессе выполнения транзакции.
Отсутствие параметра "ВидыСубконто". Если в виртуальной таблице не задан жесткий перечень видов субконто, платформа 1С строит SQL-запрос, который выполняет LEFT JOIN ко всем физическим таблицам субконто, которые теоретически могут быть привязаны к используемым счетам. Это раздувает размер SQL-текста и перегружает СУБД.

Рассмотрим по шагам, как исправить каждую из этих архитектурных проблем.

Шаг 1. Избавляемся от "ИЛИ" с помощью "ОБЪЕДИНИТЬ ВСЕ"

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

Вместо одного обращения к виртуальной таблице мы пишем два обращения:

В первом запросе получаем данные по условию: СчетДт В (&МассивСчетов) И СубконтоДт1 В (&МассивКонтрагентов).
Во втором запросе получаем данные по условию: СчетКт В (&МассивСчетов) И СубконтоКт1 В (&МассивКонтрагентов).

После этого объединяем их через конструкцию ОБЪЕДИНИТЬ ВСЕ. Важно использовать именно ОБЪЕДИНИТЬ ВСЕ (а не просто ОБЪЕДИНИТЬ), так как обычное объединение выполняет дополнительную тяжелую операцию по поиску и удалению дубликатов строк на стороне СУБД.

Шаг 2. Заменяем "В ИЕРАРХИИ" на плоский массив счетов

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

Для получения массива подчиненных счетов используем следующий код перед выполнением запроса:


МассивСчетов = Новый Массив;
МассивСчетов.Добавить(ПланыСчетов.Хозрасчетный.НалогиИСборы); // Или конкретный счет группы 68

// Получаем все подчиненные счета (включая группы и субсчета)
МассивПодчиненныхСчетов = ПланыСчетов.Хозрасчетный.ПолучитьМассивПодчиненныхСчетов(МассивСчетов);

Теперь в самом запросе мы можем использовать простое и быстрое условие сравнения с массивом: СчетДт В (&МассивПодчиненныхСчетов).

Шаг 3. Передача массива контрагентов вместо подзапроса к ВТ

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

Пример подготовки параметра на встроенном языке:


// Предположим, у нас уже есть заполненная ТаблицаЗначений или результат предыдущего запроса
ТаблицаКонтрагентов = ЗапросКонтрагенты.Выполнить().Выгрузить();
МассивКонтрагентов = ТаблицаКонтрагентов.ВыгрузитьКолонку("Контрагент");

В параметрах запроса условие примет максимально простой вид: СубконтоДт1 В (&МассивКонтрагентов).

Шаг 4. Настройка параметра "ВидыСубконто"

Для того чтобы платформа 1С не делала лишних соединений с таблицами субконто других типов, мы обязаны жестко ограничить состав субконто в параметрах виртуальной таблицы. В качестве параметра Субконто (это третий параметр большинства виртуальных таблиц регистра бухгалтерии) необходимо передать массив, содержащий вид субконто "Контрагенты".


МассивВидовСубконто = Новый Массив;
МассивВидовСубконто.Добавить(ПланыВидовХарактеристик.ВидыСубконтоХозрасчетные.Контрагенты);

Это гарантирует, что поле Субконто1 в виртуальной таблице будет строго типизировано типом СправочникСсылка.Контрагенты, а SQL-запрос будет содержать соединение только с одной нужной таблицей значений субконто.

Шаг 5. Выбор правильной виртуальной таблицы

Если перед нами стоит задача показать движения по счету или обороты за период, важно выбрать правильную виртуальную таблицу:

Если нам нужны детальные проводки с документами-регистраторами, используется таблица ДвиженияССубконто. Но помните, что это самая "тяжелая" таблица.
Если нам нужны только суммовые и количественные итоги в разрезе контрагентов и счетов без детальных документов, использование таблицы движений — это грубейшая ошибка. В этом случае необходимо использовать таблицу Обороты или ОборотыДтКт. Они работают на основе предварительно рассчитанных ежемесячных итогов регистра, что ускоряет выполнение запроса в сотни раз.

Итоговый пример оптимизированного кода запроса

Объединим все озвученные рекомендации в один готовый шаблон решения. Рассмотрим пример кода на встроенном языке 1С, который подготавливает параметры, выполняет быстрый оптимизированный запрос и возвращает итоговый результат:


// 1. Подготовка параметров счетов
МассивСчетовИерархии = Новый Массив;
МассивСчетовИерархии.Добавить(ПланыСчетов.Хозрасчетный.НДСПриИсполненииОбязанностейНалоговогоАгента); // Например, субсчет 68.32 или группа 68

МассивСчетов = ПланыСчетов.Хозрасчетный.ПолучитьМассивПодчиненныхСчетов(МассивСчетовИерархии);

// 2. Подготовка видов субконто
МассивВидовСубконто = Новый Массив;
МассивВидовСубконто.Добавить(ПланыВидовХарактеристик.ВидыСубконтоХозрасчетные.Контрагенты);

// 3. Выгрузка контрагентов из временной таблицы в массив (если они были получены ранее)
// Для примера предположим, что МассивКонтрагентов уже заполнен ссылками на контрагентов

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

// Установка параметров запроса
ЗапросОптимизированный.УстановитьПараметр("НачалоПериода", НачалоПериода);
ЗапросОптимизированный.УстановитьПараметр("КонецПериода", КонецПериода);
ЗапросОптимизированный.УстановитьПараметр("Организация", Организация);
ЗапросОптимизированный.УстановитьПараметр("МассивСчетов", МассивСчетов);
ЗапросОптимизированный.УстановитьПараметр("МассивКонтрагентов", МассивКонтрагентов);
ЗапросОптимизированный.УстановитьПараметр("МассивВидовСубконто", МассивВидовСубконто);

РезультатЗапроса = ЗапросОптимизированный.Выполнить();

Благодаря такому подходу мы полностью исключили тяжелое сканирование таблиц на уровне СУБД. Теперь каждый из двух подзапросов выполняется мгновенно с использованием точечного поиска по индексам дебетовых и кредитовых оборотов (Index Seek), а итоговое объединение результатов через ОБЪЕДИНИТЬ ВСЕ происходит моментально без затрат на уникализацию строк.