Как отключить или уменьшить оптимизацию запросов в СКД 1С, чтобы избежать некорректных изменений?

← На главную

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

1. Отключение автозаполнения — первый и самый важный шаг

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

Поймем суть проблемы с автозаполнением:
- СКД может самостоятельно добавлять отборы, периоды и параметры туда, где они не требуются, или, что еще хуже, брать поля не из тех временных таблиц, из которых мы ожидаем. Например, известны случаи, когда оптимизатор добавлял отбор по договору с условием NULL, что совершенно не соответствовало логике отчета.
- Автозаполнение может "пихать" отборы и периоды везде, где, по мнению СКД, это уместно, что особенно критично в сложных запросах с объединениями или вложенными временными таблицами.
Как отключить автозаполнение:

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

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

2. Эффективная работа с временными таблицами

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

Поймем суть проблемы с временными таблицами:
- СКД может исключать временные таблицы из пакета запросов, если считает, что данные из них не используются в конечном запросе. Это может произойти независимо от опции "Автозаполнение". Если вы создали временную таблицу для каких-то промежуточных расчетов или для более читабельной структуры запроса, а затем не выбрали из нее поля напрямую в основной запрос, СКД может ее просто "выбросить".
- Также могут возникать ошибки, если во временной таблице происходит группировка по полю, которое далее нигде не используется. СКД может посчитать это поле излишним и некорректно обработать его.
Использование "Менеджера временных таблиц":

При программном формировании отчетов на СКД этот механизм предоставляет полный контроль над выполнением запросов с временными таблицами. Мы можем выполнить запросы, создающие временные таблицы, один раз до основной компоновки отчета, а затем использовать эти уже заполненные временные таблицы в наборах данных СКД.
- Принцип работы: Мы создаем и заполняем временные таблицы, используя обычный объект Запрос, а затем передаем менеджер этих временных таблиц в метод Инициализировать процессора компоновки. В наших наборах данных СКД мы просто ссылаемся на эти уже существующие временные таблицы, заменяя исходные запросы, которые их формировали.
- Явное прописывание типов данных: Для повышения стабильности и предсказуемости запроса, особенно при работе с временными таблицами, рекомендуется явно указывать типы данных полей. Это можно сделать с помощью выражений ВЫРАЗИТЬ или ЕСТЬNULL. Например:
Индексирование временных таблиц:

Для повышения производительности и стабильности выполнения запросов, особенно при больших объемах данных, всегда рекомендуется индексировать временные таблицы. Это может помочь СКД более эффективно строить планы выполнения запросов — поможет инструмент анализа производительности и планов запросов.
```
ВЫБРАТЬ
    Документ.Ссылка КАК Ссылка,
    Документ.Дата КАК Дата
ПОМЕСТИТЬ ВТ_Документы
ИЗ
    Документ.МойДокумент КАК Документ
;

СОЗДАТЬ ИНДЕКС ПО ВТ_Документы.Ссылка
```
Преобразование подзапросов во временные таблицы:

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

3. Управление полями и отборами в СКД

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

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

Если какое-либо поле не выводится в отчете, но критически важно для корректной работы запроса (например, для условий соединения, агрегатных функций или для формирования правильного пакета запросов), мы должны установить для него признак "Обязательное" в ролях полей. Для этого на закладке "Наборы данных" выберем наш набор данных, перейдем на вкладку "Поля", найдем нужное поле и в его свойствах установим флажок "Обязательное". Это гарантирует, что СКД не исключит это поле из итогового запроса.
Управление местом применения отборов:

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

На закладке "Компоновка данных" в конструкторе запроса, на вкладке "Таблицы", мы можем снять флажки "Обязательная" для таблиц, выводом данных которых может управлять пользователь. Если снять этот флажок, соединение с этой таблицей будет заключено в фигурные скобки. При этом СКД будет формировать запрос только с теми таблицами, данные которых пользователь выбрал в настройках отчета.
```
ВЫБРАТЬ
    ОсновнаяТаблица.Поле1 КАК Поле1
ИЗ
    ОсновнаяТаблица КАК ОсновнаяТаблица
    {ЛЕВОЕ СОЕДИНЕНИЕ НеобязательнаяТаблица КАК НеобязательнаяТаблица
    ПО ОсновнаяТаблица.Поле = НеобязательнаяТаблица.Поле}
```
В таком случае, если поля из НеобязательнаяТаблица не выбраны в отчете, СКД просто не включит это соединение в итоговый запрос. Для каждой такой "необязательной" таблицы рекомендуется указывать разный номер группы, чтобы СКД могла корректно обрабатывать их связи.

4. Программное управление запросом для полного контроля

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

Передача объекта - таблицы значений в качестве набора данных:

Это самый радикальный, но и самый надежный способ получить требуемый результат. Мы можем выполнить запрос с помощью обычного объекта Запрос, получить результат в ТаблицуЗначений и затем передать эту ТаблицуЗначений в СКД как набор данных типа "объект". Таким образом, мы полностью минуем этап выполнения запроса СКД, и она будет работать уже с готовыми, немодифицируемыми данными.


// Пример формирования ТаблицыЗначений и передачи в СКД
Запрос = Новый Запрос;
Запрос.Текст = "ВЫБРАТЬ ... ИЗ ... ГДЕ ...";
ТаблицаДанных = Запрос.Выполнить().Выгрузить();

// Далее в схеме СКД создаем НаборДанныхОбъект
// и программно передаем в него ТаблицаДанных
НаборДанныхОбъект = СхемаКомпоновкиДанных.НаборыДанных.Найти("МойНаборДанныхОбъект");
Если НаборДанныхОбъект <> Неопределено Тогда
    НаборДанныхОбъект.Имя = "ТаблицаДанных"; // Имя в СКД
КонецЕсли;

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

ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, , Новое Соответствие("ТаблицаДанных", ТаблицаДанных));

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

Изменение текста запроса программно в обработчике события:

Если нам нужно сохранить возможность использования параметров, отборов и условного оформления СКД, но при этом динамически менять текст запроса, мы можем сделать это в процедуре модуля объекта отчета ПриКомпоновкеРезультата. Здесь у нас есть доступ к объекту МакетКомпоновкиДанных и его свойству НаборыДанных.Запрос. В диагностике ошибок компоновки поможет инструмент пошаговой отладки и просмотра переменных.


// Пример программной модификации текста запроса
Процедура ПриКомпоновкеРезультата(ДокументРезультат, ДанныеРасшифровки, СтандартнаяОбработка)

    // Получаем текущий макет компоновки
    МакетКомпоновки = ЭтотОбъект.КомпоновщикМакета.ПоследнийМакетКомпоновкиДанных;

    Если МакетКомпоновки <> Неопределено Тогда
        // Находим наш набор данных запроса
        НаборДанныхЗапрос = МакетКомпоновки.НаборыДанных.Найти("МойНаборДанныхЗапрос");

        Если НаборДанныхЗапрос <> Неопределено Тогда
            // Можем изменить текст запроса в зависимости от условий
            Если КакойТоПараметр = Истина Тогда
                НаборДанныхЗапрос.ТекстЗапроса = "ВЫБРАТЬ ... ИЗ ... (УСЛОВИЕ 1)";
            Иначе
                НаборДанныхЗапрос.ТекстЗапроса = "ВЫБРАТЬ ... ИЗ ... (УСЛОВИЕ 2)";
            КонецЕсли;
        КонецЕсли;
    КонецЕсли;

КонецПроцедуры

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

5. Дополнительные рекомендации для диагностики и контроля

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

Использование Консоли СКД:

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

Мы уже упоминали директивы #Если / #КонецЕсли. Существуют и другие управляющие конструкции расширения языка запросов 1С для СКД, которые могут влиять на результирующий запрос. Изучите их, чтобы получить больше контроля над процессом.
Особенности использования групп в условиях:

Обратите внимание, что условия, помещенные в группы на закладке "Отборы" в настройках СКД, могут вести себя иначе, чем условия вне групп. Иногда условия в группе могут не включаться в итоговый запрос, тогда как условия, заданные без группировки, включаются всегда. Это может быть связано с логикой СКД по объединению и применению условий.
Пометка полей как "Обязательные", даже если они не выводятся:

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

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