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