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

← На главную

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

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

Рассмотрим стандартную ситуацию: нам необходимо получить данные об основном долге (остатки) и начисленных процентах (обороты) в разрезе контрагентов и договоров. В исходном запросе автора сообщения №1 мы видим типичные ошибки:

Множественные обращения к одной и той же виртуальной таблице: Запрос дважды обращается к Хозрасчетный.ОстаткиИОбороты с одинаковой периодичностью и фильтрами. Это заставляет СУБД дважды выполнять сложнейшие вычисления итогов.
Соединение по полям составного типа: Субконто в регистре бухгалтерии имеют составной тип. Соединение с ними «напрямую» без явного приведения типов заставляет SQL-сервер просматривать множество таблиц, что катастрофически замедляет выполнение.
Использование периодичности «День»: Это самая «дорогая» настройка для виртуальной таблицы. Она требует от системы генерации временных таблиц для каждой даты внутри периода.
Фильтрация в секции ГДЕ: Проверка условий СуммаНачальныйОстатокДт > 0 после соединения таблиц отсекает данные уже после того, как они были выбраны из базы, вместо того чтобы ограничить выборку на уровне параметров виртуальной таблицы.

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

Первым делом проанализируем совет из сообщения №8 и №22. Вместо того чтобы соединять основную таблицу с регистром бухгалтерии «в лоб», мы сначала подготовим данные об объектах учета (контрагентах и договорах) во временную таблицу.

Рассмотрим, как это реализовать. Сначала выберем все счета и договоры из регистра сведений:


ВЫБРАТЬ
    СчетаУчета.ДоговорКонтрагента КАК ДоговорКонтрагента,
    СчетаУчета.ДоговорКонтрагента.Владелец КАК Контрагент,
    СчетаУчета.СчетОсновногоДолга КАК СчетОсновногоДолга,
    СчетаУчета.СчетУчетаПроцентов КАК СчетУчетаПроцентов
ПОМЕСТИТЬ ВТ_Аналитика
ИЗ
    РегистрСведений.СчетаУчетаРасчетовПоЗаймамИКредитам КАК СчетаУчета
ГДЕ
    СчетаУчета.ДоговорКонтрагента.Владелец = &Контрагент
;
ИНДЕКСИРОВАТЬ ПО
    ДоговорКонтрагента

Важный момент: Обратите внимание на инструкцию ИНДЕКСИРОВАТЬ ПО. Без индексации временной таблицы последующее соединение с ней будет выполняться перебором (Full Scan), что нивелирует пользу от оптимизации на больших объемах данных.

Стратегия 2: Оптимизация обращения к виртуальной таблице бухгалтерии

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


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

Разберем ситуацию: мы передали список счетов и список договоров напрямую в параметры виртуальной таблицы. Это позволяет платформе 1С построить оптимальный SQL-запрос, который будет использовать индексы регистра бухгалтерии.

Стратегия 3: Явное приведение типов (ВЫРАЗИТЬ)

Как упоминалось в сообщениях №23 и №25, работа с полями Субконто требует осторожности. Поля Субконто1, Субконто2 являются составными. При соединении таблиц система не знает заранее, из какой таблицы справочника брать данные. Чтобы ускорить процесс, используем функцию ВЫРАЗИТЬ.

Рассмотрим пример правильного соединения таблиц на финальном этапе:


ВЫБРАТЬ
    ВТ_Аналитика.ДоговорКонтрагента КАК ДоговорКонтрагента,
    ВТ_Аналитика.Контрагент КАК Контрагент,
    ЕСТЬNULL(ДанныеДолга.НачОстаток, 0) КАК СальдоНаНачало,
    ЕСТЬNULL(ДанныеДолга.КонОстаток, 0) КАК СальдоНаКонец,
    ЕСТЬNULL(ДанныеПроцентов.Оборот, 0) КАК НачисленныеПроценты
ИЗ
    ВТ_Аналитика КАК ВТ_Аналитика
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ДанныеРегистра КАК ДанныеДолга
        ПО ВТ_Аналитика.ДоговорКонтрагента = ВЫРАЗИТЬ(ДанныеДолга.Договор КАК Справочник.ДоговорыКонтрагентов)
        И ВТ_Аналитика.СчетОсновногоДолга = ДанныеДолга.Счет
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ДанныеРегистра КАК ДанныеПроцентов
        ПО ВТ_Аналитика.ДоговорКонтрагента = ВЫРАЗИТЬ(ДанныеПроцентов.Договор КАК Справочник.ДоговорыКонтрагентов)
        И ВТ_Аналитика.СчетУчетаПроцентов = ДанныеПроцентов.Счет

Использование ВЫРАЗИТЬ(... КАК Справочник.ДоговорыКонтрагентов) подсказывает планировщику SQL, что соединение нужно выполнять только с одной таблицей справочника договоров. Это значительно снижает нагрузку на дисковую подсистему сервера.

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

Помимо оптимизации текста запроса, следует проверить следующие технические аспекты:

Актуальность итогов: Проверьте в консоли управления, установлены ли итоги регистра бухгалтерии на текущую дату. Если итоги выключены или не рассчитаны, виртуальная таблица ОстаткиИОбороты будет вычислять данные по таблице движений (всех проводок с начала времен), что сделает отчет нерабочим на реальных базах.
Разыменование ссылок через точку: Старайтесь избегать конструкций ДоговорКонтрагента.Владелец в финальной выборке. Каждая такая точка — это неявное левое соединение. Лучше выбрать владельца один раз во временную таблицу ВТ_Аналитика, как мы сделали в первом примере.
Проверка на 0: При расчете процентных ставок (деление оборотов на остатки) обязательно используйте конструкцию ВЫБОР КОГДА Остаток = 0 ТОГДА 0 ИНАЧЕ Оборот / Остаток КОНЕЦ. Это предотвратит ошибки выполнения запроса при отсутствии данных.

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

← На главную