Каждый разработчик 1С рано или поздно сталкивается с удивительным явлением: один и тот же запрос, выполненный в обычной Консоли запросов и в Системе компоновки данных (СКД), выдает совершенно разные результаты. Наиболее частый симптом этой проблемы — внезапное задвоение или мультипликация суммовых показателей (ресурсов) при использовании левых соединений, хотя в консоли запросов всё группируется и сворачивается идеально.
В этой статье мы подробно разберем, почему так происходит, заглянем под капот оптимизатора СКД, выясним роль механизма автозаполнения и рассмотрим несколько надежных способов решения этой проблемы.
---
Рассмотрим классическую ситуацию, с которой столкнулся автор темы. У нас есть две таблицы данных:
Заказ1 на сумму 5000 руб.Заказ1 есть два акта: Акт1 на 1000 руб. и Акт2 на 4000 руб.Чтобы сопоставить эти данные, мы пишем пакетный запрос. Во второй временной таблице мы группируем акты по заказам и суммируем их, получая для Заказ1 свернутую сумму актов 5000 руб. Затем мы соединяем левым соединением таблицу заказов с нашей сгруппированной временной таблицей актов по полю Заказ поставщику.
В обычной Консоли запросов мы получаем вполне ожидаемый и корректный результат:
Заказ1 | Сумма заказа: 5000 | Сумма акта: 5000Однако при запуске этого же запроса через СКД результат по сумме заказа внезапно удваивается:
Заказ1 | Сумма заказа: 10000 | Сумма акта: 5000Выясним причину этого поведения. Она кроется в работе оптимизатора запросов СКД.
---
Система компоновки данных — это не просто инструмент для последовательного выполнения SQL-запросов. СКД представляет собой мощный динамический генератор запросов. Перед отправкой запроса в СУБД компоновщик анализирует настройки отчета:
Исходя из этого анализа, СКД пытается оптимизировать исходный текст запроса, чтобы исключить из него "избыточные", по ее мнению, операции и выборки полей. И здесь кроется главная ловушка.
Если в результирующих полях компоновщика (или в настройках группировок отчета) не выбрано детальное поле связи, по которому во временной таблице происходила группировка (например, конкретный документ или аналитика), СКД считает это поле ненужным. В результате работы оптимизатора происходит следующее:
ВЫБРАТЬ временной таблицы.СГРУППИРОВАТЬ ПО внутри временной таблицы.СуммаЗаказа), она просто складывает значения из всех получившихся строк соединения. В итоге сумма заказа дублируется столько раз, сколько детальных актов нашлось во второй таблице.Посмотрим на пример исходного запроса, который в консоли запросов работает верно, а в СКД может привести к ошибке:
// Временная таблица 1: Заказы
ВЫБРАТЬ
ЗаказПоставщику.Ссылка КАК Заказ,
ЗаказПоставщику.СуммаДокумента КАК СуммаЗаказа
ПОМЕСТИТЬ ВТ_Заказы
ИЗ
Документ.ЗаказПоставщику КАК ЗаказПоставщику
ГДЕ
ЗаказПоставщику.Дата МЕЖДУ &НачалоПериода И &КонецПериода
;
// Временная таблица 2: Акты с группировкой
ВЫБРАТЬ
ПриобретениеТоваровУслуг.ЗаказПоставщику КАК Заказ,
СУММА(ПриобретениеТоваровУслуг.СуммаДокумента) КАК СуммаАкта
ПОМЕСТИТЬ ВТ_Акты
ИЗ
Документ.ПриобретениеТоваровУслуг КАК ПриобретениеТоваровУслуг
СГРУППИРОВАТЬ ПО
ПриобретениеТоваровУслуг.ЗаказПоставщику
;
// Итоговое левое соединение
ВЫБРАТЬ
ВТ_Заказы.Заказ КАК Заказ,
ВТ_Заказы.СуммаЗаказа КАК СуммаЗаказа,
ЕСТЬNULL(ВТ_Акты.СуммаАкта, 0) КАК СуммаАкта
ИЗ
ВТ_Заказы КАК ВТ_Заказы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Акты КАК ВТ_Акты
ПО ВТ_Заказы.Заказ = ВТ_Акты.Заказ
Если СКД посчитает поле Заказ во второй таблице неиспользуемым напрямую, она исключит его из группировки в ВТ_Акты. В результате временная таблица ВТ_Акты вернет строки в разрезе физических документов приобретения, а не свернутые по заказам суммы. Левое соединение размножит строки заказов, и сумма заказов задвоится.
---
Прежде чем исправлять запрос, мы должны научиться видеть его реальное "поведение" внутри СКД. Обычная консоль запросов здесь бесполезна, так как она выполняет исходный, статичный текст запроса.
Для анализа динамически измененного запроса нам понадобится специальный инструмент — Консоль компоновки данных (Консоль СКД). Разберем по шагам, как выполнить этот анализ:
ВЫБРАТЬ и СГРУППИРОВАТЬ ПО.---
Рассмотрим подробнее практические методы борьбы с этой особенностью СКД. Выбор конкретного метода зависит от сложности вашего отчета и архитектурных предпочтений.
Это самый простой и быстрый способ победить избыточную оптимизацию СКД без изменения структуры самого запроса. Нам нужно принудительно указать системе компоновки, что поле связи во временной таблице ни в коем случае нельзя исключать из выборки и группировки.
Разберем, как это сделать:
Заказ из таблицы ВТ_Акты).Установка этого флага гарантирует, что оптимизатор СКД сохранит данное поле в секции ВЫБРАТЬ и, как следствие, сохранит группировку по нему во временной таблице, даже если пользователь не выбрал это поле в настройках структуры отчета.
Если вы строите сложный пакет запросов с большим количеством временных таблиц, вложенных условий и связей, поведение оптимизатора СКД становится труднопредсказуемым. В таких случаях мы рекомендуем полностью отключить автозаполнение.
Для этого:
Отключение автозаполнения заставляет СКД выполнять запрос практически в том "чистом" виде, в котором вы его написали в конфигураторе, без принудительной модификации структуры временных таблиц.
Использование левых соединений для таблиц типа "один-ко-многим" в рамках одного запроса СКД с последующим суммированием ресурсов главной таблицы — это классическая архитектурная ошибка. Правильнее переложить задачу соединения таблиц на сам механизм СКД.
Проанализируем этот подход по шагам:
НаборДанных_Заказы, а приемником — НаборДанных_Акты.Заказ из обоих наборов.СуммаЗаказа как СУММА(СуммаЗаказа), а для поля СуммаАкта — как СУММА(СуммаАкта).При таком подходе СКД сначала получит уникальные записи по заказам, затем выберет подчиненные записи по актам и самостоятельно сопоставит их при выводе в отчет. Суммы по заказам никогда не задвоятся, так как они рассчитываются строго по первому (главному) набору данных.
Если левое соединение в одном запросе обойти невозможно (например, этого требуют жесткие условия производительности или специфика СУБД), мы можем скорректировать расчет итогов на уровне настроек ресурсов СКД.
Вместо стандартного выражения СУММА(СуммаЗаказа) на вкладке Ресурсы мы можем применить следующие формулы:
МАКСИМУМ(СуммаЗаказа) вместо суммы. Если в рамках одной группировки по заказу сумма заказа всегда одинакова, то максимум вернет корректное значение без суммирования дублей.Заказ. Это заставит СКД игнорировать детальные строки подчиненной таблицы при расчете промежуточных сумм по заказу.---
Различие результатов в СКД и Консоли запросов — это не мистика и не баг платформы 1С. Это следствие работы интеллектуального оптимизатора запросов СКД. Чтобы избежать подобных проблем в будущем, придерживайтесь простых правил: