Как оптимизировать запрос для получения даты последней операции по регистру бухгалтерии в 1С?

← На главную

Рассмотрим типичную, но очень интересную задачу, с которой часто сталкиваются разработчики при построении отчетов по взаиморасчетам и дебиторской задолженности. Предположим, у нас есть рабочий запрос, получающий остатки задолженности в разрезе контрагентов и договоров на определенную дату. Нам необходимо расширить этот запрос и вывести для каждой строки дату последней хозяйственной операции по выбранному договору в рамках определенного списка счетов (например, счета расчетов с покупателями: 62.01, 62.02 и т.д.).

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

Разбор исходной архитектуры и типичных ошибок

Проанализируем классический подход, который часто пытаются применить разработчики. Изначально предпринимается попытка обратиться к виртуальной таблице РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт с ограничением по периоду и передачей параметров контрагента и договора непосредственно в условия виртуальной таблицы. Однако этот подход имеет несколько критических проблем:

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

Выбор оптимального источника данных (Физические таблицы против виртуальных)

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

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

Важный нюанс: Начиная с платформы 8.3.15 (при использовании режима совместимости 8.3.15 и выше), разработчики 1С существенно оптимизировали виртуальную таблицу ДвиженияССубконто. На уровне СУБД это денормализованная физическая таблица, куда сразу записываются все необходимые поля. Запросы к ней выполняются в разы быстрее, чем в старых версиях. Единственное, о чем нужно помнить при обращении к ДвиженияССубконто — это обязательная фильтрация по активным записям: Активность = Истина.

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

Одной из главных ошибок при написании запросов с левыми соединениями является попытка соединить основную выборку (наших контрагентов с долгами) с подзапросом, выполняющим поиск максимума по всей таблице регистра. Если написать левое соединение со вложенным запросом, СУБД может выполнить операцию Nested Loops (вложенные циклы) с полным сканированием регистра на каждую строку основных данных.

Чтобы этого избежать, мы применим классический паттерн разработки в 1С:

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

Оптимизация условий «ИЛИ» (OR) по дебету и кредиту

Посмотрим на классическое условие отбора по счетам:

СчетДт В ИЕРАРХИИ (&СписокСчетов) ИЛИ СчетКт В ИЕРАРХИИ (&СписокСчетов)

Использование оператора ИЛИ в условиях соединения или фильтрации по разным полям (дебет и кредит) — это главный враг производительности СУБД. При наличии такого условия оптимизатор баз данных SQL не может применить индексы по полям дебета и кредита одновременно. В результате СУБД переходит к полному сканированию таблиц.

Как решить эту проблему? Нам поможет замена условия ИЛИ на конструкцию ОБЪЕДИНИТЬ ВСЕ (UNION ALL). Мы разобьем получение периодов на два независимых и очень быстрых запроса:

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

Подобный подход позволяет СУБД выполнить два изолированных, но чрезвычайно быстрых поиска по индексам дебета и кредита соответственно, а затем моментально слить результаты в оперативной памяти.

Избавление от оператора «В ИЕРАРХИИ»

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

Разыменование составных типов (Обязательное использование «ВЫРАЗИТЬ»)

В физической таблице субконто поле Значение имеет составной тип данных, так как на разных счетах могут использоваться совершенно разные виды субконто (Контрагенты, Договоры, Номенклатура и т.д.). Когда мы пишем условие сравнения напрямую по составному полю:

тзСубконто.Значение В (ВЫБРАТЬ Договор ИЗ ВТ_ИсходныеДанные)

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

ВЫРАЗИТЬ(тзСубконто.Значение КАК Справочник.ДоговорыКонтрагентов)

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

Разбор ошибки с типом «Граница»

Часто разработчики сталкиваются с ошибкой несовместимости типов при использовании условий МЕЖДУ &ДатаНачала И &ДатаОстатков в физических таблицах, если в качестве параметров передается объект Граница.

Выясним причину: Физические таблицы СУБД работают исключительно с системным типом даты-времени. Они «не знают», что такое объект Граница. Этот объект может корректно обрабатываться только виртуальными таблицами платформы. Если мы обращаемся к физической таблице в секции ГДЕ, в параметры запроса необходимо передавать исключительно стандартный тип Дата.

---

Пошаговое построение оптимизированного запроса

Давайте объединим все правила оптимизации и посмотрим на итоговый пример реализации алгоритма на встроенном языке 1С:


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

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

Запрос.УстановитьПараметр("Организация", ПараметрОрганизация);
Запрос.УстановитьПараметр("СписокСчетов", СписокСчетовДляЗапроса);

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

// Для условий ГДЕ физической таблицы передаем только чистую Дату
Запрос.УстановитьПараметр("ДатаОстатковЧистая", КонецДня(ПараметрДатаОстатков));
Запрос.УстановитьПараметр("ДатаНачала", ПараметрДатаНачала);

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

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