Как оптимизировать сложный запрос с виртуальными таблицами и вложенными запросами в 1С

← На главную

При работе с большими объемами данных в 1С (миллионы документов или записей в регистрах) стандартные подходы к написанию запросов могут привести к существенным задержкам. Часто разработчики сталкиваются с ситуацией, когда запрос, работавший быстро на тестовой базе, начинает "тормозить" в реальных условиях. Разберем по шагам, как оптимизировать такой запрос, используя рекомендации опытных специалистов.

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

В представленном примере кода мы видим классический случай неоптимального построения запроса. Основные проблемы заключаются в следующем:

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

Шаг 1: Оптимизация получения первичных данных

Рассмотрим первый блок запроса, где мы выбираем список лицевых счетов из документов. Хотя автор утверждает, что этот кусок работает быстро, при росте количества документов до 6 миллионов он станет "узким местом".

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


ВЫБРАТЬ РАЗЛИЧНЫЕ
    Событие.ЛицевойСчет КАК ЛицевойСчет
ПОМЕСТИТЬ СписокЛС
ИЗ
    Документ.Событие КАК Событие
ГДЕ
    Событие.Дата МЕЖДУ &НачалоПериода И &КонецПериода
    И НЕ Событие.ПометкаУдаления
    И Событие.Ответственный В(&СписокСотрудников)
    И Событие.ЛицевойСчет <> ЗНАЧЕНИЕ(Справочник.ЛицевыеСчета.ПустаяСсылка)
ИНДЕКСИРОВАТЬ ПО
    ЛицевойСчет
;

Важное изменение: мы добавили инструкцию ИНДЕКСИРОВАТЬ ПО ЛицевойСчет. Это критически важно для всех последующих соединений с этой временной таблицей. Без индекса SQL-сервер будет выполнять полное сканирование таблицы СписокЛС при каждом обращении.

Шаг 2: Избавление от вложенных запросов

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

Рассмотрим подробнее, как правильно передавать условия в виртуальные таблицы. Вместо вложенного запроса мы будем использовать саму временную таблицу в условиях связи или предварительно выгруженные массивы. Но самый эффективный способ — фильтрация внутри параметров &Условие виртуальной таблицы.

Шаг 3: Переход к виртуальной таблице ОстаткиИОбороты

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

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


ВЫБРАТЬ
    Расчеты.ЛицевойСчет КАК ЛицевойСчет,
    ЕСТЬNULL(Расчеты.СуммаНачальныйОстаток, 0) КАК СальдоНачальное,
    ЕСТЬNULL(Расчеты.СуммаКонечныйОстаток, 0) КАК СальдоКонечное,
    ЕСТЬNULL(Расчеты.СуммаПриход, 0) КАК Начисления,
    ЕСТЬNULL(Расчеты.СуммаРасход, 0) КАК Оплата
ПОМЕСТИТЬ РасчетыИтого
ИЗ
    РегистрНакопления.РасчетыСАбонентами.ОстаткиИОбороты(
        &НачалоПериода,
        &КонецПериода,
        Авто,
        ,
        ЛицевойСчет В (ВЫБРАТЬ СписокЛС.ЛицевойСчет ИЗ СписокЛС)) КАК Расчеты
;

Обратите внимание на использование ЕСТЬNULL. При левых соединениях или работе с виртуальными таблицами могут возникать значения NULL, которые "испортят" арифметические операции и итоги в отчете. Мы всегда приводим числовые показатели к нулю.

Шаг 4: Использование объекта Граница для точности данных

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

Проанализируем ситуацию: если документ записан в 23:59:59, обычное указание даты может его не подхватить (зависит от настроек виртуальной таблицы). В СКД мы можем настроить параметры так, чтобы использовалось выражение:

Новый Граница(&КонецПериода, ВидГраницы.Включая)

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

Шаг 5: Итоговый оптимизированный запрос

Соберем все рекомендации воедино. Мы избавились от вложенных подзапросов, внедрили индексацию и объединили получение данных в одну виртуальную таблицу.


// 1. Формируем индексированный список ЛС
ВЫБРАТЬ РАЗЛИЧНЫЕ
    Событие.ЛицевойСчет КАК ЛицевойСчет
ПОМЕСТИТЬ ВТ_СписокЛС
ИЗ
    Документ.Событие КАК Событие
ГДЕ
    Событие.Дата МЕЖДУ &НачалоПериода И &КонецПериода
    И НЕ Событие.ПометкаУдаления
    И Событие.Ответственный В(&СписокСотрудников)
    И Событие.ЛицевойСчет <> ЗНАЧЕНИЕ(Справочник.ЛицевыеСчета.ПустаяСсылка)

ИНДЕКСИРОВАТЬ ПО
    ЛицевойСчет
;

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

Почему это будет работать быстрее?

Выясним причину ускорения:

Nested Loops (Вложенные циклы): Благодаря индексу по ЛицевойСчет во временной таблице, SQL-сервер сможет мгновенно находить нужные строки при соединении с виртуальными таблицами регистров.
Снижение нагрузки на TempDB: Мы убрали ОБЪЕДИНИТЬ ВСЕ, который часто вызывает неявную сортировку и нагружает временную базу данных сервера.
Эффективный фильтр: Передача списка ЛС в параметры виртуальной таблицы (ЛицевойСчет В (...)) позволяет платформе использовать индексы самого регистра (Index Seek), а не перебирать все записи (Index Scan).

Совет: Если вы используете СКД, старайтесь не писать сложные запросы вручную в тексте запроса набора данных. Используйте механизмы компоновки, они часто умеют оптимизировать простые соединения автоматически, добавляя нужные фильтры в виртуальные таблицы на лету.