Почему временная таблица и вложенный запрос возвращают разные данные в СКД: разбираемся в работе оптимизатора 1С

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

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

В чем суть проблемы: пример из практики

Представим типовую задачу. Нам необходимо в рамках одного отчета получить срез последних и остатки товаров на каждый день, посчитать количество дней, когда остатки были нулевыми, и вывести среднюю цену за эти дни. Конечная группировка в отчете должна выполняться только по номенклатуре.

Логика построения запроса следующая:

1. Мы собираем промежуточные данные в разрезе полей Номенклатура и Дата во временную таблицу.
2. Во временной таблице мы группируем данные по этим двум полям, чтобы исключить дубли в рамках одного дня.
3. В финальном запросе пакета мы выбираем данные из этой временной таблицы, рассчитываем количество дней с помощью агрегатной функции вроде КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Дата) и вычисляем среднюю цену, группируя результат уже только по Номенклатуре (поле Дата в итоговую выборку отчета не выводится).

Запустив этот запрос в обычной консоли, мы получаем верные цифры (например, товар отсутствовал 5 дней). Однако в отчете СКД поле количества дней всегда возвращает 1. Стоит нам заменить временную таблицу на обычный вложенный запрос — и данные в СКД мгновенно становятся правильными. Давайте выясним, почему так происходит.

Причина поведения: агрессивная оптимизация СКД

Главный виновник этой ситуации — встроенный оптимизатор Системы компоновки данных. СКД — это не просто транслятор текста запроса в СУБД. Перед выполнением запроса СКД анализирует настройки компоновки (какие поля выбраны пользователем, какие группировки настроены в схеме, какие наложены отборы) и динамически перестраивает исходный SQL-код.

Рассмотрим, как оптимизатор обрабатывает временные таблицы и вложенные запросы:

1. Поведение при использовании временных таблиц (ВТ):

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

В итоге СКД выполняет следующие действия:

• Исключает неиспользуемое поле Дата из секции ВЫБРАТЬ временной таблицы.
• Автоматически удаляет это поле из секции СГРУППИРОВАТЬ ПО этой же временной таблицы.

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

2. Поведение при использовании вложенных запросов:

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

Почему вложенный запрос — это плохой выход?

Несмотря на то, что замена ВТ на вложенный запрос кажется простым и быстрым решением, использовать его в реальных задачах (особенно при работе со сложными отчетами на больших базах данных) крайне не рекомендуется по следующим причинам:

• Проблемы с планом запроса (Cardinality Estimation): СУБД (например, MS SQL Server) не может построить точную статистику распределения данных для вложенного запроса до его фактического выполнения. Это часто приводит к тому, что оптимизатор СУБД выбирает неэффективный план соединения таблиц (например, медленный Nested Loops вместо быстрого Hash Match).
• Невозможность индексации: Временную таблицу мы можем явно проиндексировать по ключевым полям связи (например, по Номенклатуре). Вложенный запрос проиндексировать невозможно, что приведет к колоссальному падению производительности при соединении с другими таблицами пакета.

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

Способ 1. Использование роли поля «Обязательное» (Рекомендуемый)

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

Разберем этот процесс по шагам:

1. Откроем схему компоновки данных и перейдем на вкладку «Наборы данных».
2. В списке полей найдем то поле, по которому должна сохраняться группировка во временной таблице (в нашем примере — поле Дата или вспомогательное поле связи).
3. В колонке «Роль» нажмем на кнопку выбора (три точки).
4. В открывшемся окне параметров роли установим флажок «Обязательное».

Флаг Обязательное сообщает оптимизатору СКД, что данное поле должно присутствовать в результирующем SQL-запросе всегда, независимо от того, выбрал ли его пользователь в настройках структуры отчета или нет. Группировка во временной таблице гарантированно сохранится.

Способ 2. Отключение свойства «Автозаполнение»

Если мы хотим полностью контролировать текст запроса, который будет отправлен в СУБД, мы можем отключить механизм автоматической модификации запросов компоновщиком.

Для этого выполним следующие действия:

1. На вкладке «Наборы данных» найдем наш набор данных (запрос).
2. Снимем флажок «Автозаполнение».
3. Перейдем на вкладку «Поля» и вручную добавим только те поля, которые должны быть доступны пользователю для вывода в отчет и настройки отборов.

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

Способ 3. Перенос расчетов в ресурсы СКД

Часто программисты пытаются выполнить все сложные математические расчеты (итоги, средние показатели, расчет дней) непосредственно внутри SQL-запроса. Однако СКД проектировалась с расчетом на то, что агрегатные функции должны вычисляться на уровне самого процессора компоновки данных.

Проанализируем альтернативный подход к проектированию отчета:

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

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

Способ 4. Использование фиктивных условий (Dummy conditions)

Если по каким-то причинам установить роль поля или отключить автозаполнение невозможно, можно прибегнуть к хитрости и «обмануть» оптимизатор СКД. Для этого достаточно сделать так, чтобы оптимизатор посчитал поле используемым.

Достичь этого можно добавлением фиктивного условия в секцию ГДЕ конечного запроса пакета, где выбираются данные из временной таблицы:

ВЫБРАТЬ
    ВТ_Данные.Номенклатура,
    ВТ_Данные.КоличествоДней
ИЗ
    ВТ_Данные КАК ВТ_Данные
ГДЕ
    ВТ_Данные.Дата <> ДАТАВРЕМЯ(1, 1, 1)

Поскольку поле Дата теперь участвует в условиях фильтрации конечного запроса, оптимизатор СКД не сможет исключить его из выборки промежуточной временной таблицы ВТ_Данные, и группировка внутри ВТ останется неизменной.

Как проверить, что именно СКД отправляет в СУБД?

Чтобы не гадать, почему расходятся данные, мы всегда можем посмотреть окончательный текст SQL-запроса, сгенерированный компоновщиком. Сделать это можно следующими способами:

• Воспользоваться специализированным инструментом — внешней обработкой «Консоль компоновки данных» (доступна на диске ИТС). Она позволяет открыть схему отчета, применить пользовательские настройки и просмотреть итоговый текст запроса с учетом оптимизации.
• Посмотреть запрос в отладчике. В модуле объекта отчета в процедуре ПриКомпоновкеРезультата мы можем перехватить макет компоновки и проанализировать свойство: МакетКомпоновки.НаборыДанных.НаборДанных1.Запрос

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