Запросы к регистру бухгалтерии, особенно с использованием виртуальной таблицы ДвиженияССубконто, традиционно относятся к категории наиболее тяжелых и критичных для производительности системы. При неправильном проектировании такие запросы могут приводить к длительным блокировкам базы данных и существенно замедлять работу пользователей. В рамках этой статьи мы детально разберем реальный практический кейс по оптимизации сложного бухгалтерского запроса, проанализируем основные ошибки и пошагово преобразуем исходный неоптимальный код в быстрое и масштабируемое решение.
Рассмотрим исходный запрос, с которым столкнулся разработчик. Задача состоит в том, чтобы выбрать данные по закупкам за определенный период, поместить список контрагентов во временную таблицу, а затем получить по ним бухгалтерские проводки из регистра бухгалтерии Управленческий, где эти контрагенты выступают в роли субконто на счетах определенной группы.
Давайте проанализируем первоначальный вариант запроса:
ВЫБРАТЬ
ЗакупкиОбороты.Контрагент
ПОМЕСТИТЬ ВТ_Контрагенты
ИЗ
РегистрНакопления.Закупки.Обороты(
&ДатаНачала,
&ДатаОкончания,
,
Организация = &Организация
И Валюта = &Валюта) КАК ЗакупкиОбороты
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВЫБОР
КОГДА УправленческийДвиженияССубконто.СчетДт В ИЕРАРХИИ (&СчетГруппа)
ТОГДА УправленческийДвиженияССубконто.СубконтоДт1
ИНАЧЕ УправленческийДвиженияССубконто.СубконтоКт1
КОНЕЦ КАК Контрагент,
ВЫБОР
КОГДА УправленческийДвиженияССубконто.СчетДт В ИЕРАРХИИ (&СчетГруппа)
ТОГДА УправленческийДвиженияССубконто.СубконтоДт1.Описание
ИНАЧЕ УправленческийДвиженияССубконто.СубконтоКт1.Описание
КОНЕЦ КАК ОписаниеКонтрагента,
УправленческийДвиженияССубконто.Сумма,
УправленческийДвиженияССубконто.Регистратор,
УправленческийДвиженияССубконто.Содержание
ИЗ
РегистрБухгалтерии.Управленческий.ДвиженияССубконто(
&ДатаНачала,
&ДатаОкончания,
Организация = &Организация
И ((СчетДт В ИЕРАРХИИ (&СчетГруппа)
И СубконтоДт1 В
(ВЫБРАТЬ
ВТ_Контрагенты.Контрагент
ИЗ
ВТ_Контрагенты))
ИЛИ (СчетКт В ИЕРАРХИИ (&СчетГруппа)
И СубконтоКт1 В
(ВЫБРАТЬ
ВТ_Контрагенты.Contragent
ИЗ
ВТ_Контрагенты))),
,
) КАК УправленческийДвиженияССубконто
Этот запрос таит в себе сразу несколько критических архитектурных проблем, которые гарантируют низкую скорость работы СУБД при увеличении объема данных:
.Описание). Поле Субконто на уровне базы данных всегда имеет составной тип. Платформа 1С при обращении через точку к реквизиту составного поля (например, СубконтоДт1.Описание) генерирует в SQL-запросе левое соединение (LEFT JOIN) со всеми физическими таблицами справочников и документов, которые могут входить в это субконто. Если в системе настроены десятки видов субконто, СУБД выполнит десятки лишних соединений, что колоссально снизит производительность.ИЛИ (OR) в параметрах виртуальной таблицы. Наличие условия ИЛИ между дебетом и кредитом (СчетДт... ИЛИ СчетКт...) не позволяет оптимизатору СУБД эффективно применить индексы. База данных будет вынуждена прибегнуть к полному сканированию таблиц (Table Scan или Index Scan) вместо быстрого поиска по индексу.ВТ_Контрагенты. Когда временная таблица используется в подзапросах или соединениях с другими таблицами, отсутствие индекса по ключевому полю заставляет СУБД выполнять вложенные циклы поиска для каждой строки, что крайне неэффективно на больших выборках.В ИЕРАРХИИ непосредственно в запросе. Конструкция В ИЕРАРХИИ заставляет систему динамически вычислять все подчиненные счета группы на уровне СУБД, что усложняет план запроса.Выяснив основные причины медленной работы, перейдем к их последовательному устранению.
Для того чтобы СУБД могла эффективно использовать индексы по дебету и кредиту отдельно, нам необходимо разделить один тяжелый запрос на два независимых ветвления и объединить их результаты.
Важный нюанс: при объединении результатов всегда следует использовать конструкцию ОБЪЕДИНИТЬ ВСЕ вместо простой конструкции ОБЪЕДИНИТЬ. Оператор ОБЪЕДИНИТЬ (аналог UNION в SQL) производит неявную группировку и сравнение всех строк для удаления дублей, затрачивая колоссальные ресурсы СУБД. Конструкция ОБЪЕДИНИТЬ ВСЕ (UNION ALL) просто склеивает результаты работы двух запросов «как есть». Если нам необходимо избавиться от дубликатов, гораздо быстрее сделать это явной группировкой на самом последнем этапе.
При заполнении временной таблицы ВТ_Контрагенты мы обязательно должны проиндексировать поле связи. Также крайне полезно применить ключевое слово РАЗЛИЧНЫЕ, чтобы исключить дублирование контрагентов еще на этапе подготовки выборки из оборотов закупок:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ЗакупкиОбороты.Contragent КАК Контрагент
ПОМЕСТИТЬ ВТ_Контрагенты
ИЗ
РегистрНакопления.Закупки.Обороты(&ДатаНачала, &ДатаОкончания, , Организация = &Организация И Валюта = &Валюта) КАК ЗакупкиОбороты
ИНДЕКСИРОВАТЬ ПО
Контрагент
;
Чтобы СУБД понимала, к какой именно физической таблице ей нужно обратиться для получения описания контрагента, мы обязаны явно типизировать наше субконто с помощью конструкции ВЫРАЗИТЬ.
Однако еще более надежным и производительным решением в стандартах разработки 1С является полный отказ от получения текстовых реквизитов (таких как наименование, описание, код) внутри тяжелых пакетов с виртуальными таблицами. Правильнее всего выбрать из регистра исключительно ссылки на контрагентов, а в самом финальном запросе пакета соединить полученные данные с плоским физическим справочником Справочник.Контрагенты и забрать описание одним легким левым соединением. Давайте реализуем именно этот подход.
Чтобы избежать использования медленной конструкции В ИЕРАРХИИ в самом запросе, мы можем заранее развернуть группу счетов в плоский массив на стороне сервера 1С перед выполнением запроса:
МассивСчетов = Новый Массив;
// Заполняем массив счетов, включая подчиненные субсчета группы &СчетГруппа
// Для этого можно использовать стандартные механизмы или запросы к плану счетов
После этого в параметрах виртуальной таблицы мы сможем использовать быстрое условие Счет В (&МассивСчетов) вместо тяжелого Счет В ИЕРАРХИИ (&СчетГруппа).
Применим все обсужденные паттерны оптимизации и соберем итоговый пакет запросов. Обратим внимание на то, как теперь организована выборка: мы отдельно выбираем движения по дебету, отдельно по кредиту, объединяем их через ОБЪЕДИНИТЬ ВСЕ во временную таблицу ВТ_Движения, а на выходе производим финальную группировку и соединение со справочником контрагентов.
ВЫБРАТЬ РАЗЛИЧНЫЕ
ЗакупкиОбороты.Контрагент КАК Контрагент
ПОМЕСТИТЬ ВТ_Контрагенты
ИЗ
РегистрНакопления.Закупки.Обороты(
&ДатаНачала,
&ДатаОкончания,
,
Организация = &Организация
И Валюта = &Валюта) КАК ЗакупкиОбороты
ИНДЕКСИРОВАТЬ ПО
Контрагент
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
УправленческийДвиженияССубконто.СубконтоДт1 КАК Контрагент,
УправленческийДвиженияССубконто.Сумма КАК Сумма,
УправленческийДвиженияССубконто.Регистратор КАК Регистратор,
УправленческийДвиженияССубконто.Содержание КАК Содержание
ПОМЕСТИТЬ ВТ_ДвиженияПредварительные
ИЗ
РегистрБухгалтерии.Управленческий.ДвиженияССубконто(
&ДатаНачала,
&ДатаОкончания,
Организация = &Организация
И СчетДт В ИЕРАРХИИ(&СчетГруппа)
И СубконтоДт1 В (ВЫБРАТЬ ВТ_Контрагенты.Контрагент ИЗ ВТ_Контрагенты),
,
) КАК УправленческийДвиженияССубконто
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
УправленческийДвиженияССубконто.СубконтоКт1,
УправленческийДвиженияССубконто.Сумма,
УправленческийДвиженияССубконто.Регистратор,
УправленческийДвиженияССубконто.Содержание
ИЗ
РегистрБухгалтерии.Управленческий.ДвиженияССубконто(
&ДатаНачала,
&ДатаОкончания,
Организация = &Организация
И СчетКт В ИЕРАРХИИ(&СчетГруппа)
И СубконтоКт1 В (ВЫБРАТЬ ВТ_Контрагенты.Контрагент ИЗ ВТ_Контрагенты),
,
) КАК УправленческийДвиженияССубконто
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Движения.Контрагент КАК Контрагент,
СУММА(Движения.Сумма) КАК Сумма,
Движения.Регистратор КАК Регистратор,
Движения.Содержание КАК Содержание
ПОМЕСТИТЬ ВТ_ДвиженияСгруппированные
ИЗ
ВТ_ДвиженияПредварительные КАК Движения
СГРУППИРОВАТЬ ПО
Движения.Контрагент,
Движения.Регистратор,
Движения.Содержание
ИНДЕКСИРОВАТЬ ПО
Контрагент
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_ДвиженияСгруппированные.Контрагент КАК Контрагент,
ВЫРАЗИТЬ(ВТ_ДвиженияСгруппированные.Контрагент КАК Справочник.Контрагенты).Описание КАК ОписаниеКонтрагента,
ВТ_ДвиженияСгруппированные.Сумма КАК Сумма,
ВТ_ДвиженияСгруппированные.Регистратор КАК Регистратор,
ВТ_ДвиженияСгруппированные.Содержание КАК Содержание
ИЗ
ВТ_ДвиженияСгруппированные КАК ВТ_ДвиженияСгруппированные
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Контрагенты КАК СправочникКонтрагенты
ПО ВТ_ДвиженияСгруппированные.Контрагент = СправочникКонтрагенты.Ссылка
Проанализируем изменения, которые произошли с нашим запросом, и то, как они повлияют на поведение СУБД:
ИЛИ на операцию ОБЪЕДИНИТЬ ВСЕ, СУБД теперь может использовать два независимых индекса для поиска записей: отдельно по дебету (СчетДт + СубконтоДт1) и отдельно по кредиту (СчетКт + СубконтоКт1). Это полностью исключает сканирование всей таблицы.ВТ_Контрагенты с ключевым словом РАЗЛИЧНЫЕ и обязательной индексацией гарантирует, что СУБД мгновенно выполнит фильтрацию по списку контрагентов в обоих плечах объединения.Описание в самый конец запроса и его левое соединение с физической таблицей справочника убрали десятки неявных LEFT JOIN из тела обращения к виртуальной таблице регистра бухгалтерии. Теперь запрос выполняется линейно и предсказуемо.ВЫРАЗИТЬ на последнем этапе страхует систему от неоптимального поведения в случае непредвиденных изменений в метаданных конфигурации.Рассмотренный подход к оптимизации является классическим шаблоном при работе с высоконагруженными регистрами бухгалтерии в платформе 1С. Применение этих правил позволит вашим отчетам работать максимально эффективно даже на базах данных объемом в несколько терабайт.