Почему СКД неправильно определяет период во вложенном запросе и как это исправить?

← На главную

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

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

Выясняем причину: влияние механизма «Автозаполнение»

Основная причина такого поведения кроется во встроенном механизме СКД под названием Автозаполнение (Autofill). По умолчанию этот флаг всегда включен для новых наборов данных-запросов. Когда СКД видит, что в тексте запроса используются виртуальные таблицы одного и того же регистра накопления (в нашем случае это ОстаткиИОбороты и Обороты регистра дм_ВзаиморасчетыСДолжниками), она пытается оптимизировать запрос перед отправкой в СУБД.

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

Кроме того, использование вложенного запроса в секции ЛЕВОЕ СОЕДИНЕНИЕ напрямую нарушает стандарты разработки фирмы 1С (в частности, стандарт #std655). Оптимизатор СУБД не может заранее определить размер выборки из вложенного запроса, что часто приводит к выбору неоптимального плана выполнения, зависаниям и медленной работе отчета на больших объемах данных.

Рассмотрим по шагам четыре практических метода, которые помогут обойти это ограничение платформы.

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

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

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

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

Посмотрим на пример реализации такого пакетного запроса:


ВЫБРАТЬ
    дм_ВзаиморасчетыСДолжникамиОбороты.Должник КАК Должник,
    дм_ВзаиморасчетыСДолжникамиОбороты.ВидДолга КАК ВидДолга,
    СУММА(дм_ВзаиморасчетыСДолжникамиОбороты.СуммаРасход) КАК СуммаРасход
ПОМЕСТИТЬ ВТ_ОборотыДоНачала
ИЗ
    РегистрНакопления.дм_ВзаиморасчетыСДолжниками.Обороты(, &НачалоПериода, Период, ) КАК дм_ВзаиморасчетыСДолжникамиОбороты
СГРУППИРОВАТЬ ПО
    дм_ВзаиморасчетыСДолжникамиОбороты.Должник,
    дм_ВзаиморасчетыСДолжникамиОбороты.ВидДолга

ИНДЕКСИРОВАТЬ ПО
    Должник,
    ВидДолга
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ТаблицаОстаткиОбороты.Регистратор,
    ТаблицаОстаткиОбороты.Организация,
    ТаблицаОстаткиОбороты.Должник,
    ТаблицаОстаткиОбороты.ВидДолга,
    ТаблицаОстаткиОбороты.СуммаНачальныйОстаток КАК СуммаНачальныйОстаток,
    ТаблицаОстаткиОбороты.СуммаКонечныйОстаток КАК СуммаКонечныйОстаток,
    ТаблицаОстаткиОбороты.СуммаПриход КАК СуммаПриход,
    ТаблицаОстаткиОбороты.СуммаРасход КАК СуммаРасход,
    ЕСТЬNULL(ВТ_ОборотыДоНачала.СуммаРасход, 0) КАК РасходДоНачала
ИЗ
    РегистрНакопления.дм_ВзаиморасчетыСДолжниками.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Авто, Движения, ) КАК ТаблицаОстаткиОбороты
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОборотыДоНачала КАК ВТ_ОборотыДоНачала
        ПО ТаблицаОстаткиОбороты.Должник = ВТ_ОборотыДоНачала.Должник
        И ТаблицаОстаткиОбороты.ВидДолга = ВТ_ОборотыДоНачала.ВидДолга

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

Способ 2. Связи наборов данных (Архитектурно верный подход СКД)

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

Разберем этот алгоритм по шагам:

Создадим Набор данных 1 (Запрос). В нем опишем получение основных данных из таблицы ОстаткиИОбороты за период с &НачалоПериода по &КонецПериода.
Создадим Набор данных 2 (Запрос), который будет получать обороты до даты начала отчета. В параметрах его виртуальной таблицы укажем уникальный параметр, например &ПредельнаяДата. Код этого набора будет выглядеть максимально просто:
```
ВЫБРАТЬ
    Должник,
    ВидДолга,
    СуммаРасход КАК СуммаРасход
ИЗ
    РегистрНакопления.дм_ВзаиморасчетыСДолжниками.Обороты(, &ПредельнаяДата, Период, )
```
Перейдем на вкладку Связи источников данных в СКД. Настроим связь, где источником связи выступит Набор 1, а приемником — Набор 2. Полями связи будут выступать Должник и ВидДолга.
В качестве параметра связи для Набора 2 укажем, что в его параметр ПредельнаяДата должно передаваться значение &НачалоПериода из Набора 1.

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

Способ 3. Явная настройка параметров компоновки в Конструкторе запроса

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

Проанализируем этот процесс:

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

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

Способ 4. Отключение автозаполнения и использование расширения языка запросов

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

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

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


ИЗ
    РегистрНакопления.дм_ВзаиморасчетыСДолжниками.ОстаткиИОбороты({&НачалоПериода}, {&КонецПериода}, Авто, Движения, ) КАК дм_ВзаиморасчетыСДолжникамиОстаткиИОбороты
    ЛЕВОЕ СОЕДИНЕНИЕ (
        ВЫБРАТЬ 
            Обороты.Должник, 
            Обороты.ВидДолга, 
            СУММА(Обороты.СуммаРасход) КАК СуммаРасход
        ИЗ 
            РегистрНакопления.дм_ВзаиморасчетыСДолжниками.Обороты(, {&НачалоПериода}, Период, ) КАК Обороты
        СГРУППИРОВАТЬ ПО 
            Обороты.Должник, 
            Обороты.ВидДолга
    ) КАК ВложенныйЗапрос 
    ПО дм_ВзаиморасчетыСДолжникамиОстаткиИОбороты.Должник = ВложенныйЗапрос.Должник

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