Как избежать переполнения таблиц SQL при обращении к реквизитам составного типа в запросе 1С?

← На главную

При написании запросов к регистрам со сложной структурой данных разработчики часто сталкиваются с ошибками производительности или аварийным завершением работы СУБД. Типичный сценарий — выборка данных из регистра сведений ДополнительныеСведения, где измерение Объект имеет составной тип, включающий десятки или сотни видов документов и справочников конфигурации. Попытка наложить условие на реквизит такого объекта через точку приводит к ошибкам нехватки памяти, переполнению дискового пространства tempdb или падению SQL-сервера.

Разберем детально, почему возникает эта проблема на уровне платформы 1С и СУБД, а также рассмотрим надежные практические способы её решения.

Выясним причину: почему запрос переполняет ресурсы SQL

Проанализируем исходный фрагмент запроса, вызывающий ошибку:


ВЫБРАТЬ
    ДополнительныеСведения.Объект КАК Ссылка
ИЗ
    РегистрСведений.ДополнительныеСведения КАК ДополнительныеСведения
ГДЕ
    ДополнительныеСведения.Свойство = &Свойство
    И ДополнительныеСведения.Значение = &Значение
    И ТИПЗНАЧЕНИЯ(ДополнительныеСведения.Объект) = ТИПЗНАЧЕНИЯ(&Объект)
    И ДополнительныеСведения.Объект.Дата МЕЖДУ &НачДата И &КонДата

На первый взгляд кажется, что условие ТИПЗНАЧЕНИЯ(ДополнительныеСведения.Объект) = ТИПЗНАЧЕНИЯ(&Объект) должно ограничить состав типов. Однако транслятор запросов 1С работает иначе:

Неявные соединения (LEFT JOIN): Поле Объект может хранить ссылку на любой документ конфигурации. Обращение через точку (ДополнительныеСведения.Объект.Дата) заставляет платформу сгенерировать неявное левое соединение со всеми таблицами документов базы данных, у которых есть реквизит Дата.
Ограничения СУБД: В Microsoft SQL Server действует строгое ограничение на количество объединяемых таблиц в одном запросе (256 таблиц). Если в конфигурации документов больше этого лимита, запрос падает с ошибкой процессора запросов. Даже если лимит не превышен, гигантское дерево соединений ломает расчет кардинальности оптимизатора, и СУБД сбрасывает огромные объемы промежуточных данных во временные таблицы tempdb, вызывая переполнение диска.
Бесполезность фильтра по типу: Конструкция ТИПЗНАЧЕНИЯ(...) = ... вычисляется уже на этапе фильтрации строк в секции WHERE. На этапе генерации плана и текста SQL-запроса платформа не знает конкретного значения параметра &Объект и соединяет все возможные таблицы без исключения.
Ограничение конструкции ВЫРАЗИТЬ: Платформа 1С не поддерживает передачу параметров в оператор приведения типов. Конструкция вида ВЫРАЗИТЬ(Объект КАК Документ.ТИПЗНАЧЕНИЯ(&Объект)) синтаксически некорректна, так как имя типа должно быть известно статически на этапе компиляции запроса.

Способ 1. Динамическая подстановка типа через ВЫРАЗИТЬ

Если в функцию передается объект конкретного типа (или мы можем определить его имя в коде перед выполнением запроса), самым быстрым решением является явное ограничение типа поля с помощью функции ВЫРАЗИТЬ через шаблонную замену в тексте запроса.

Рассмотрим, как реализовать этот подход на практике:


// Определяем имя метаданных переданного объекта
ИмяТипаДокумента = Объект.Метаданные().Имя;

ТекстЗапроса = 
"ВЫБРАТЬ
|   ДополнительныеСведения.Объект КАК Ссылка
|ИЗ
|   РегистрСведений.ДополнительныеСведения КАК ДополнительныеСведения
|ГДЕ
|   ДополнительныеСведения.Свойство = &Свойство
|   И ДополнительныеСведения.Значение = &Значение
|   И ВЫРАЗИТЬ(ДополнительныеСведения.Объект КАК Документ.%ИмяДокумента%).Дата МЕЖДУ &НачДата И &КонДата";

// Выполняем строковую замену под конкретный вид документа
ТекстЗапроса = СтрЗаменить(ТекстЗапроса, "%ИмяДокумента%", ИмяТипаДокумента);

Запрос = Новый Запрос;
Запрос.Текст = ТекстЗапроса;
Запрос.УстановитьПараметр("Свойство", Свойство);
Запрос.УстановитьПараметр("Значение", Значение);
Запрос.УстановитьПараметр("НачДата", НачалоДня(НачДата));
Запрос.УстановитьПараметр("КонДата", КонецДня(КонДата));

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

Когда платформа видит явную инструкцию ВЫРАЗИТЬ(... КАК Документ.РеализацияТоваровУслуг), она транслирует её в SQL с соединением ровно одной целевой таблицы. Лишние десятки и сотни LEFT JOIN полностью исключаются, нагрузка на СУБД падает до минимума.

Способ 2. Инверсия соединения (запрос от документа к регистру)

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


ИмяТипаДокумента = Объект.Метаданные().Имя;

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

ТекстЗапроса = СтрЗаменить(ТекстЗапроса, "%ИмяДокумента%", ИмяТипаДокумента);

Преимущества инверсии:

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

Способ 3. Использование журнала документов

Если в конфигурации присутствует общий журнал документов (например, складской или финансовый), включающий в себя все потенциально проверяемые типы документов, можно избежать динамической сборки текста запроса. Стандартные реквизиты журнала Ссылка и Дата проиндексированы.

Посмотрим на пример запроса через журнал:


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

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

Способ 4. Оптимизация пакетной загрузки (устранение запросов в цикле)

Если проверка документов происходит в потоковом режиме (например, при загрузке 6–8 тысяч входящих документов из XML в цикле), выполнять даже оптимизированный динамический запрос на каждую итерацию недопустимо: СУБД захлебнется от количества транзакций и компиляций планов запросов.

Разберем правильный алгоритм пакетной обработки с использованием временных таблиц:

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


// Формируем таблицу пакета
ВходящиеДанные = Новый ТаблицаЗначений;
ВходящиеДанные.Колонки.Добавить("Свойство", Новый ОписаниеТипов("ПланВидовХарактеристикСсылка.ДополнительныеРеквизитыИСведения"));
ВходящиеДанные.Колонки.Добавить("Значение");
ВходящиеДанные.Колонки.Добавить("НачДата", Новый ОписаниеТипов("Дата"));
ВходящиеДанные.Колонки.Добавить("КонДата", Новый ОписаниеТипов("Дата"));

// Заполняем ВходящиеДанные из входящего потока...

МенеджерВТ = Новый МенеджерВременныхТаблиц;
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = МенеджерВТ;

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

Запрос.УстановитьПараметр("ТаблицаПакета", ВходящиеДанные);
Результат = Запрос.ВыполнитьПакет();

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