Почему различаются результаты запроса в консоли отчетов и в СКД? Разбираемся с оптимизатором компоновки данных

← На главную

Каждый разработчик 1С рано или поздно сталкивается с загадочной ситуацией: один и тот же запрос, выполненный в обычной консоли запросов и внутри Системы компоновки данных (СКД), выдает совершенно разные результаты. В консоли мы видим правильные, выверенные до копейки суммы, а в отчете СКД по тому же самому контрагенту или договору цифры начинают «плыть» или удваиваться. Давайте разберем по шагам, почему так происходит, как устроен внутренний механизм оптимизации СКД и как быстро победить эту проблему.

В чем кроется главная причина расхождения данных?

Основной виновник этой проблемы — встроенный оптимизатор запросов СКД. В отличие от обычной консоли запросов, которая выполняет ваш код «как есть», СКД пытается сделать запрос максимально эффективным для СУБД. Она анализирует настройки отчета, выбранные пользователем поля, группировки, отборы и на основе этого анализа динамически перестраивает исходный текст запроса перед отправкой его на сервер базы данных.

Этот процесс называется интеллектуальной оптимизацией, но иногда эта «интеллектуальность» играет с разработчиком злую шутку. Проанализируем основные механизмы, с помощью которых СКД переписывает наши запросы:

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

Анализ практического примера: где ошибся оптимизатор?

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

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


ВЫБРАТЬ
    ВтПроливПоФильтру.ИсточникТранзакций,
    ВтПроливПоФильтру.Контрагент,
    ВтПроливПоФильтру.ДоговорКонтрагента КАК ДоговорКонтрагента,
    СУММА(ВЫБОР
            КОГДА ВтПроливПоФильтру.ДатаТранзакции >= НАЧАЛОПЕРИОДА(&Дата1, МЕСЯЦ)
                ТОГДА ВтПроливПоФильтру.Сумма
            ИНАЧЕ 0
        КОНЕЦ) КАК СуммаПролив
ИЗ
    ВтПроливПоФильтру КАК ВтПроливПоФильтру
СГРУППИРОВАТЬ ПО
    ВтПроливПоФильтру.Контрагент,
    ВтПроливПоФильтру.ДоговорКонтрагента,
    ВтПроливПоФильтру.ИсточникТранзакций

Если в настройках отчета СКД пользователь решит вывести только Контрагент и СуммаПролив, не выбирая поля ДоговорКонтрагента и ИсточникТранзакций, оптимизатор СКД сочтет эти поля «ненужными» для вывода и исключит их из промежуточных временных таблиц. В итоге группировка в таблице ВтПроливПоФильтру выполнится только по контрагенту. Вся логика расчета детальных сумм по договорам и источникам транзакций будет нарушена, что приведет к неверному расчету итоговой суммы.

Также в запросе присутствует работа с виртуальной таблицей остатков:


ЛЕВОЕ СОЕДИНЕНИЕ РегистрБухгалтерии.Хозрасчетный.Остатки(
        {(НАЧАЛОПЕРИОДА(&Дата1, МЕСЯЦ))},
        Счет В ИЕРАРХИИ (ЗНАЧЕНИЕ(плансчетов.Хозрасчетный.РасчетыСПоставщикамиИПодрядчиками))
            ИЛИ Счет В ИЕРАРХИИ (ЗНАЧЕНИЕ(плансчетов.Хозрасчетный.РасчетыСПокупателямиИЗаказчиками)),
        ,
        Субконто2 В
            (ВЫБРАТЬ
                ВтДоговорыКатегорииПартнеры.ДоговорКонтрагента
            ИЗ
                ВтДоговорыКатегорииПартнеры)) КАК ХозрасчетныйОстатки

Использование фигурных скобок {(НАЧАЛОПЕРИОДА(&Дата1, МЕСЯЦ))} указывает СКД на то, что этот параметр является необязательным и может управляться системой компоновки. В консоли запросов эти скобки игнорируются, и запрос отрабатывает строго по значению параметра. В СКД же настройки компоновщика могут переопределить или вообще очистить этот параметр, если он не задан в пользовательских настройках явным образом.

Как увидеть реальный запрос, который выполняет СКД?

Выясним причину расхождения, посмотрев на тот запрос, который СКД реально отправляет в СУБД после всех своих оптимизаций. Для этого мы можем использовать два надежных метода.

Способ 1. Использование специальной консоли отчетов СКД

Для диагностики рекомендуется использовать специализированную обработку с диска ИТС — «Консоль компоновки данных». Она позволяет загрузить схему компоновки (из XML или прямо из отчета), применить текущие настройки (отборы, параметры, выбранные поля) и нажать кнопку «Выполнить» или «Показать запрос». В результате вы увидите сгенерированный SQL-подобный текст запроса. Сравнив его с вашим исходным текстом, вы сразу заметите, какие поля пропали из временных таблиц и где изменилась группировка.

Способ 2. Программный перехват запроса в отладчике

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


КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновкиДанных, КомпоновщикНастроек.ПолучитьНастройки());

// Установим точку останова ниже этой строки и исследуем МакетКомпоновки
Для Каждого НаборДанных Из МакетКомпоновки.НаборыДанных Цикл
    Если НаборДанных.Имя = "НаборДанных1" Тогда // Укажите имя вашего набора данных
        ТекстРеальногоЗапроса = НаборДанных.Запрос;
        ЗаписьЖурналаРегистрации("РеальныйЗапросСКД", УровеньЖурналаРегистрации.Информация,,, ТекстРеальногоЗапроса);
    КонецЕсли;
    
    // Также можно скопировать текст запроса в буфер обмена в режиме отладки через табло
КонецЦикла;

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

Пошаговое руководство по решению проблемы

Выяснив, в каком месте оптимизатор СКД искажает логику, мы можем применить несколько приемов для исправления ситуации и принудительного сохранения структуры запроса.

Шаг 1. Использование флага «Обязательное» для полей

Если вам критически важно, чтобы определенное поле всегда участвовало во временных таблицах, связях и группировках (даже если пользователь не выводит его в отчет), укажите это в схеме компоновки данных:

Откройте схему компоновки данных (вкладка «Наборы данных»).
Найдите нужное поле в таблице полей набора данных (например, ДоговорКонтрагента).
В колонке «Обязательное» установите галочку (ИСТИНА).

Этот флаг запретит оптимизатору СКД исключать данное поле из выборки временных таблиц на всех этапах выполнения запроса.

Шаг 2. Отключение автозаполнения набора данных

Если оптимизатор СКД слишком агрессивно вмешивается в структуру сложного пакетного запроса, лучшим решением будет полное отключение автозаполнения:

Перейдите на вкладку «Наборы данных» в конструкторе СКД.
Снимите флаг «Автозаполнение» у вашего набора данных-запроса.

После снятия этого флага СКД перестанет автоматически модифицировать структуру запроса, добавлять скрытые поля и изменять параметры виртуальных таблиц. Однако помните, что теперь вам придется вручную описать все доступные поля, параметры и отборы на соответствующих вкладках схемы компоновки данных, используя при необходимости явные фигурные скобки {} в тексте запроса для указания мест интеграции пользовательских отборов.

Шаг 3. Использование оператора ВЫРАЗИТЬ для приведения типов

Оптимизатор СКД может некорректно строить левые соединения, если поля связи имеют составной тип данных. Чтобы избежать неявного приведения типов СУБД, всегда явно приводите типы полей в условиях соединения:


ВЫБРАТЬ
    КатегорииОбъектов.Объект КАК ОбъектДоговор,
    ВЫРАЗИТЬ(КатегорииОбъектов.Объект КАК Справочник.ДоговорыКонтрагентов).Владелец КАК Контрагент
ПОМЕСТИТЬ ВтДоговорыКатегорииПартнеры
ИЗ
    РегистрСведений.КатегорииОбъектов КАК КатегорииОбъектов

Явное использование оператора ВЫРАЗИТЬ помогает СКД однозначно определить типы полей, что исключает ошибки при формировании временных таблиц и гарантирует совпадение результатов с консолью запросов.

Резюме

Различие результатов в консоли и СКД — это не ошибка платформы, а следствие работы оптимизатора запросов. Чтобы избежать подобных проблем при разработке сложных многокомпонентных отчетов, всегда контролируйте использование флага «Обязательное» для ключевых полей группировок, вовремя отключайте «Автозаполнение» и проверяйте итоговый сгенерированный SQL-запрос с помощью специализированных инструментов отладки.