Как ускорить отчет при соединении регистра накопления и регистра сведений по регистратору

← На главную

В процессе разработки сложных систем на платформе 1С программисты часто сталкиваются с ситуацией, когда необходимо вывести в одном отчете данные о взаиморасчетах и дополнительные сведения о документах, которые хранятся в отдельных регистрах. Если база данных имеет значительный объем (сотни гигабайт), стандартное левое соединение виртуальных таблиц может привести к катастрофическому падению производительности, перегрузке базы tempdb и аварийному завершению работы SQL-сервера. Рассмотрим подробнее, почему это происходит и как правильно оптимизировать такие запросы.

Анализ проблемы: почему запрос работает медленно

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

Составной тип поля Регистратор. Поле Регистратор в регистрах накопления является ссылкой на множество видов документов. При выполнении соединения в SQL-запросе сервер вынужден выполнять поиск по всем физическим таблицам документов, входящим в этот тип.
Тяжесть виртуальной таблицы. Таблица ОстаткиИОбороты при детализации до регистратора вычисляется «на лету» для каждой точки во времени. Если мы соединяем ее с большой физической таблицей регистра сведений, объем промежуточных вычислений растет экспоненциально.
Разрастание TempDB. Использование временных таблиц без предварительной фильтрации или индексации заставляет SQL Server выгружать огромные массивы данных во временное хранилище, что часто приводит к ошибке нехватки памяти.

Решение 1: Использование оператора ВЫРАЗИТЬ

Первое, что мы должны сделать для оптимизации соединения по регистратору, — это ограничить тип данных. Если мы знаем, что нас интересуют только определенные документы (например, РеализацияТоваровУслуг), необходимо явно указать это в запросе. Это позволит оптимизатору SQL обращаться к конкретной таблице, а не перебирать все возможные варианты.


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

Решение 2: Разделение получения остатков и оборотов

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

Рассмотрим пошагово алгоритм оптимизации:

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

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

Рассмотрим пример кода, который позволяет избежать «зависания» базы данных при формировании отчета за месяц:


// Шаг 1: Получаем только нужные обороты и сразу фильтруем по типам
ВЫБРАТЬ РАЗРЕШЕННЫЕ
    Взаиморасчеты.Регистратор КАК Регистратор,
    Взаиморасчеты.Период КАК Период,
    Взаиморасчеты.Контрагент КАК Контрагент,
    Взаиморасчеты.СуммаВзаиморасчетовПриход КАК Приход,
    Взаиморасчеты.СуммаВзаиморасчетовРасход КАК Расход
ПОМЕСТИТЬ ВТ_Обороты
ИЗ
    РегистрНакопления.РасчетыСКонтрагентами.Обороты(&ДатаНач, &ДатаКон, Регистратор, ) КАК Взаиморасчеты
ГДЕ
    Взаиморасчеты.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг
ИНДЕКСИРОВАТЬ ПО
    Регистратор
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Получаем данные из регистра сведений только для отобранных регистраторов
ВЫБРАТЬ
    Проекты.Документ КАК Документ,
    Проекты.Проект КАК Проект,
    Проекты.Статус КАК Статус
ПОМЕСТИТЬ ВТ_Статусы
ИЗ
    РегистрСведений.Проекты_и_документы КАК Проекты
ГДЕ
    Проекты.Документ В (ВЫБРАТЬ Регистратор ИЗ ВТ_Обороты)
ИНДЕКСИРОВАТЬ ПО
    Документ
;

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

Решение 3: Работа с временными таблицами и индексация

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

Важные правила работы с ВТ:

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

Особенности работы с Построителем отчета и СКД

Проанализируем ситуацию с выбором инструмента вывода. Автор темы упоминает, что использует «Построитель отчета». Этот механизм в старых конфигурациях (на обычных формах) менее гибок в плане оптимизации планов запроса, чем СКД.

Если вы все же решили остаться на Построителе, убедитесь, что:

Параметры виртуальных таблиц (период, фильтры) передаются напрямую в запрос, а не накладываются позже в секции ГДЕ.
Используются фигурные скобки {} для возможности расширения запроса пользователем без потери производительности.
При использовании ИТОГИ вы не перегружаете запрос лишними группировками.

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

Заключение

Подведем итог. Чтобы отчет по взаиморасчетам с дополнительными статусами из регистра сведений «летал», необходимо придерживаться стратегии «Разделяй и властвуй». Не пытайтесь получить всё одним огромным запросом к сложной виртуальной таблице. Используйте временные таблицы, ограничивайте типы данных через ВЫРАЗИТЬ, индексируйте ключевые поля и разделяйте расчет итоговых остатков и детальных оборотов. Такой подход гарантирует стабильную работу системы даже на базах объемом в несколько терабайт.