Каждый разработчик 1С рано или поздно сталкивается с удивительной ситуацией: один и тот же запрос, выполненный в обычной консоли запросов и в Системе компоновки данных (СКД), выдает совершенно разные результаты. Например, консоль возвращает правильное значение 983, а СКД при формировании того же отчета выводит лишь 945. При этом сам текст запроса не меняется ни на символ.
В рамках этой статьи мы подробно разберем, почему так происходит, заглянем во внутренние механизмы работы платформы 1С:Предприятие и найдем несколько надежных способов решения этой проблемы.
Главное отличие консоли запросов от СКД заключается в том, как они работают с исходным текстом запроса. Обычная консоль выполняет запрос «как есть» — отправляет написанный вами SQL-код в СУБД без каких-либо изменений. СКД же представляет собой интеллектуальный генератор запросов.
Перед тем как отправить запрос в СУБД, СКД анализирует настройки вашего отчета (выбранные поля, группировки, отборы, условное оформление). С целью повышения производительности СКД выполняет оптимизацию запроса. Она стремится выбросить из итогового запроса все таблицы, соединения и поля, которые, по ее мнению, не влияют на конечный результат отчета. Именно эта «забота» о производительности часто оборачивается искажением данных.
Рассмотрим по шагам, как именно оптимизатор СКД может изменить логику вашего запроса:
ВЫБРАТЬ промежуточных запросов.СГРУППИРОВАТЬ ПО. Это критический момент. Если СКД удаляет неиспользуемые поля из секции ВЫБРАТЬ временной таблицы, она автоматически обязана удалить их и из секции СГРУППИРОВАТЬ ПО этой же таблицы. В результате группировка происходит по меньшему количеству полей, строки «схлопываются» сильнее, а агрегатные функции (такие как КОЛИЧЕСТВО, СУММА или СРЕДНЕЕ) начинают считать данные по совершенно другим группам. Именно это привело к уменьшению результата с 983 до 945 в рассматриваемой теме.СрезПоследних) СКД пытается автоматически подставить условия из отборов отчета в параметры этих таблиц для ускорения работы. Консоль запросов этого не делает, что также ведет к разным выборкам.Проанализируем фрагмент запроса, с которым возникла проблема у автора темы. В первом запросе пакета создается временная таблица с группировкой:
ВЫБРАТЬ
НаливПродуктаНов.Период КАК Период,
НаливПродуктаНов.ВидТоплива КАК ВидТоплива,
НаливПродуктаНов.ТипВЦ КАК ТипВЦ,
НаливПродуктаНов.ГрузоподъемностьВЦ КАК ГрузоподъемностьВЦ,
НаливПродуктаНов.Регистратор КАК Регистратор,
НаливПродуктаНов.dWeight * 1000 - НаливПродуктаНов.Вес * 1000 КАК СредняяРазница,
КОЛИЧЕСТВО(НаливПродуктаНов.Вагон) КАК КоличествоПогрузок,
НаливПродуктаНов.Вагон КАК Вагон
ПОМЕСТИТЬ ВремТаб
ИЗ
РегистрСведений.НаливПродуктаНов КАК НаливПродуктаНов
ГДЕ
НаливПродуктаНов.Период МЕЖДУ &НачПериода И &КонПериода
СГРУППИРОВАТЬ ПО
НаливПродуктаНов.Период,
НаливПродуктаНов.ВидТоплива,
НаливПродуктаНов.ТипВЦ,
НаливПродуктаНов.ГрузоподъемностьВЦ,
НаливПродуктаНов.Регистратор,
НаливПродуктаНов.Вагон
Если в настройках отчета СКД мы выводим только поля КоличествоПогрузок и Вагон, СКД решает, что поля Период, ВидТоплива, ТипВЦ, ГрузоподъемностьВЦ и Регистратор не нужны. Она вырезает их из секции ВЫБРАТЬ и, как следствие, из секции СГРУППИРОВАТЬ ПО временной таблицы. Запрос превращается в обычную группировку по полю Вагон. Количество записей резко уменьшается, а функция КОЛИЧЕСТВО выдает неверный итоговый результат.
Существует несколько практических способов заставить СКД выполнять запрос именно в том виде, в котором его задумал разработчик.
Это самый правильный и штатный способ управления поведением компоновщика. Мы можем явно указать СКД, какие поля нельзя исключать из запроса, даже если они не выбраны пользователем в настройках отчета.
Разберем по шагам, как это настроить:
Период, ВидТоплива, ТипВЦ).Теперь СКД «поймет», что данные поля критически важны для внутренней логики расчета, и сохранит их в итоговом SQL-запросе, не нарушая структуру группировки.
Если ваш запрос написан сложно, содержит много временных таблиц, вложенных условий и вы хотите полностью контролировать его выполнение, вы можете отключить механизм автоматической модификации.
Для этого на вкладке «Наборы данных» найдите ваш набор данных (запрос) и снимите галочку «Автозаполнение». При отключенном автозаполнении СКД отправит запрос в СУБД в первозданном виде. Однако помните, что в этом случае вам придется вручную объявить все доступные поля и параметры на вкладках набора данных, а также самостоятельно прописать связи для отборов.
Как совершенно верно подсказали автору в Сообщении 11, СКД гораздо реже вмешивается в структуру подзапросов (вложенных запросов). Если обернуть группировку во вложенный запрос, а во внешнем запросе временной таблицы просто выбирать готовые агрегатные данные, оптимизатор СКД не сможет «схлопнуть» группировку.
Рассмотрим пример рефакторинга кода:
ВЫБРАТЬ
ВложенныйЗапрос.Вагон КАК Вагон,
ВложенныйЗапрос.КоличествоПогрузок КАК КоличествоПогрузок,
ВложенныйЗапрос.СредняяРазница КАК СредняяРазница
ПОМЕСТИТЬ ВремТаб
ИЗ
(ВЫБРАТЬ
НаливПродуктаНов.Вагон КАК Вагон,
КОЛИЧЕСТВО(НаливПродуктаНов.Вагон) КАК КоличествоПогрузок,
НаливПродуктаНов.dWeight * 1000 - НаливПродуктаНов.Вес * 1000 КАК СредняяРазница
ИЗ
РегистрСведений.НаливПродуктаНов КАК НаливПродуктаНов
ГДЕ
НаливПродуктаНов.Период МЕЖДУ &НачПериода И &КонПериода
СГРУППИРОВАТЬ ПО
НаливПродуктаНов.Вагон,
НаливПродуктаНов.Период,
НаливПродуктаНов.ВидТоплива,
НаливПродуктаНов.ТипВЦ,
НаливПродуктаНов.ГрузоподъемностьВЦ,
НаливПродуктаНов.Регистратор) КАК ВложенныйЗапрос
При такой структуре СКД будет оптимизировать только внешний запрос к ВложенныйЗапрос, но саму группировку внутри подзапроса она трогать не станет.
Существует также быстрый «обходной» путь (лайфхак), позволяющий удержать поле в запросе. Если добавить поле в секцию ГДЕ конечного запроса пакета с условием, которое всегда истинно, СКД будет вынуждена оставить это поле и всю цепочку его получения во всех временных таблицах.
Например:
ГДЕ
ВремТаб.ВидТоплива ЕСТЬ НЕ NULL ИЛИ ВремТаб.ВидТоплива = &ПустойВидТоплива
Подобное условие заблокирует удаление поля ВидТоплива оптимизатором, сохранив корректность промежуточных группировок.
Чтобы не гадать, в какой момент СКД изменила ваш запрос, никогда не ограничивайтесь тестированием в обычной консоли запросов. Используйте специализированный инструмент — Консоль компоновки данных.
Этот инструмент позволяет сделать следующее:
Именно этот исполняемый запрос отправляется на сервер СУБД. Сравнив его текст с исходным текстом запроса в конфигураторе, вы сразу увидите, какие секции группировок или временные таблицы были безжалостно удалены оптимизатором СКД, и сможете оперативно исправить ситуацию одним из описанных выше способов.