Работа со сложными отчетами в системе компоновки данных (СКД) часто приводит к ситуации, когда запрос разрастается до сотен таблиц. Со временем часть полей становится неактуальной, их убирают из настроек отчета, но сам запрос продолжает «ворочать» огромные массивы данных, создавая лишние временные таблицы и замедляя работу сервера. Разберем подробно, как выявить и устранить эти избыточные элементы, сохранив при этом целостность логики отчета.
Для начала проанализируем, как работает встроенный оптимизатор СКД. Платформа 1С обладает механизмом динамического изменения текста запроса. Если в настройках компоновки (в выбранных полях, отборах, группировках или сортировке) не используются поля из какой-либо таблицы, участвующей в соединении (JOIN), компоновщик может попытаться «выкинуть» это соединение из итогового запроса, отправляемого на SQL-сервер.
Однако здесь есть критически важный нюанс: автоматическая оптимизация соединений работает только в основном запросе. Если ваш отчет построен на пакете запросов, где данные последовательно помещаются во временные таблицы (ВТ), оптимизатор СКД зачастую игнорирует неиспользуемые ВТ. Если временная таблица описана в тексте — она будет создана и наполнена, даже если в конечном итоге ее данные никому не нужны. Рассмотрим, как заставить систему работать эффективнее.
Чтобы предоставить СКД право решать, нужно ли выполнять конкретный запрос в пакете, мы должны использовать расширение языка запросов. Разберем на примере. Если мы обернем выборку во временную таблицу в фигурные скобки, она станет необязательной.
Рассмотрим пример кода:
{ВЫБРАТЬ
Продажи.Номенклатура КАК Номенклатура,
Продажи.КоличествоОборот КАК Количество
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода) КАК Продажи}
В данном случае, если в последующих запросах пакета или в итоговой выборке поля из ВТ_Продажи не будут задействованы (в том числе через цепочку зависимостей других таблиц), СКД полностью исключит этот блок из исполнения. Это самый мощный инструмент для «полуавтоматической» очистки запроса из 220 таблиц.
Часто автоматическая оптимизация не срабатывает из-за настроек самой схемы. Выясним причину, почему таблицы остаются в запросе. Перейдем на закладку «Наборы данных» в СКД и проанализируем список полей. У каждого поля есть свойство «Обязательное».
Если для какого-то поля установлен этот флаг, компоновщик будет обязан включить в запрос все таблицы, необходимые для получения этого поля, даже если пользователь не выбрал его в отчете. Пройдитесь по списку и снимите флаг «Обязательное» везде, где это возможно. Это позволит стандартному механизму оптимизации соединений ({...} вокруг соединений) заработать в полную силу.
Когда запрос слишком велик для ручного анализа (как в случае с 220 таблицами), нам помогут специализированные инструменты. Лучший способ — использовать «Консоль компоновки данных» (входит в состав «Инструментов разработчика» или поставляется отдельно на ИТС).
Проанализируем ситуацию по шагам:
Система покажет вам «голый» SQL-запрос (или макетный запрос 1С), который реально уходит на СУБД. Сравните его с исходным текстом. Вы сразу увидите, какие таблицы СКД смогла выкинуть сама, а какие остались «мертвым грузом». Те таблицы, которые остались в исполняемом запросе, но не несут полезной нагрузки, — ваши главные кандидаты на удаление или оборачивание в фигурные скобки.
Проанализируем структуру пакета запросов. Временные таблицы (ВТ) хороши, когда данные из них используются многократно в разных частях пакета. Однако, если временная таблица создается только для того, чтобы один раз соединиться с другой таблицей в следующем запросе, это может мешать оптимизатору.
Попробуйте заменить такие «одноразовые» временные таблицы на вложенные запросы непосредственно в тексте основного соединения. Для вложенных запросов и соединений оптимизатор СКД работает гораздо агрессивнее и эффективнее.
Пример оптимизированного соединения:
ВЫБРАТЬ
ОсновнаяТаблица.Ссылка,
ДопДанные.Значение
ИЗ
Справочник.Номенклатура КАК ОсновнаяТаблица
{ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ Ссылка, Значение ИЗ Справочник.ДополнительныеРеквизиты) КАК ДопДанные
ПО ОсновнаяТаблица.Ссылка = ДопДанные.Ссылка}
Здесь использование фигурных скобок {...} вокруг ЛЕВОЕ СОЕДИНЕНИЕ гарантирует, что если поле ДопДанные.Значение не выбрано пользователем, то и вложенный запрос выполнен не будет.
Если автоматизация не помогает, применим надежный ручной метод «снизу вверх». Рассмотрим алгоритм:
Важный совет: Чтобы быстро проверить, используется ли временная таблица, попробуйте просто переименовать её (например, добавить в начало имени символ подчеркивания). Если при сохранении или проверке запроса 1С не выдала ошибку «Таблица не найдена», значит, на эту таблицу никто в пакете больше не ссылается и её можно безболезненно удалить.
Помните, что 220 временных таблиц — это колоссальная нагрузка на базу данных tempdb на стороне SQL-сервера. Даже если таблицы содержат всего по одной строке, создание метаданных для такого количества объектов занимает время и вызывает блокировки системных таблиц СУБД.
Проанализируем альтернативу: если данные в таблицах однотипные (например, получение различных свойств номенклатуры), лучше использовать механизм Характеристик в СКД. Это позволит вообще не описывать сотни соединений в запросе. Вы описываете только правила получения данных, а СКД сама построит оптимальный запрос с нужным количеством соединений «на лету», в зависимости от того, какие свойства пользователь вытащил в колонки отчета.
Таким образом, сочетание правильной разметки запроса фигурными скобками, использование консоли компоновки для аудита и отказ от «обязательных» полей позволит вам сократить время выполнения отчета в разы, не переписывая всю логику с нуля.