При разработке отчетов на базе Системы компоновки данных (СКД) часто возникает задача предоставить пользователю выбор: фильтровать данные по наличию связанного документа-основания, по его отсутствию или вовсе выводить все записи без учета этого критерия. На первый взгляд задача кажется тривиальной, но при ее реализации разработчики часто сталкиваются с аппаратными ошибками СУБД, несоответствием типов параметров и снижением производительности отчета.
В этой статье мы подробно разберем, почему возникают ошибки при передаче незаполненных параметров в конструкцию ВЫБОР КОГДА, как правильно построить логику условий в запросе и как оптимизировать производительность за счет отказа от неявных левых соединений (разыменования полей).
Рассмотрим типичную ошибку, с которой сталкиваются разработчики при построении условий фильтрации. Допустим, на форме отчета создано поле выбора со следующими вариантами:
ДокументОснование);Для управления фильтрацией в запросе СКД создаются три булевых параметра: &НеУчитывать, &ДокЗаполнен и &ДокНеЗаполнен. Разработчики часто пишут условие в секции ГДЕ следующим образом:
ГДЕ ПеремещениеТоваров.Дата МЕЖДУ &ДатаНачало И &ДатаОкончание
И ВЫБОР
КОГДА &ДокНеЗаполнен ТОГДА ПеремещениеТоваров.ДокументОснование.Ссылка ЕСТЬ NULL
КОГДА &ДокЗаполнен ТОГДА ПеремещениеТоваров.ДокументОснование.Ссылка IS NOT NULL
КОГДА &НеУчитывать ТОГДА ИСТИНА
КОНЕЦ
При попытке сформировать отчет с такими настройками система генерирует ошибку несоответствия типов СУБД. Выясним причину этого поведения.
Когда мы пишем условие КОГДА &ДокЗаполнен, платформа 1С транслирует это выражение в SQL-запрос. СУБД ожидает, что на входе будет строго логический (булев) тип данных — ИСТИНА (True) или ЛОЖЬ (False). Однако, если на форме отчета пользователь выбрал один режим, то параметры для других режимов могут остаться неинициализированными. В системе 1С неинициализированный параметр принимает значение Неопределено (или NULL при трансляции в СУБД).
Попытка подставить значение Неопределено непосредственно в логическое ветвление КОГДА &ИмяПараметра приводит к сбою, так как СУБД не может неявно преобразовать этот тип в булево значение. Чтобы избежать этой ошибки, необходимо всегда явно сравнивать параметры с булевой константой:
КОГДА &ДокЗаполнен = ИСТИНА ТОГДА ...
При таком написании выражение &ДокЗаполнен = ИСТИНА вернет корректное значение ЛОЖЬ, даже если сам параметр равен Неопределено. Запрос отработает стабильно и без ошибок.
Рассмотрим первый способ решения проблемы. Нам необходимо исправить синтаксис условий в запросе и скорректировать обработчик изменения параметров на форме отчета.
Шаг 1. Перепишем секцию ГДЕ нашего запроса, добавив явное сравнение параметров с ИСТИНА и оптимизировав условие "Не учитывать" с помощью возврата значения ИСТИНА:
ВЫБРАТЬ
ПеремещениеТоваровТовары.Номенклатура,
ПеремещениеТоваровТовары.Количество,
ПеремещениеТоваров.Ссылка КАК Перемещение,
ПеремещениеТоваров.Комментарий,
ПеремещениеТоваров.ДокументОснование
ИЗ
Документ.ПеремещениеТоваров.Товары КАК ПеремещениеТоваровТовары
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ПеремещениеТоваров КАК ПеремещениеТоваров
ПО ПеремещениеТоваровТовары.Ссылка = ПеремещениеТоваров.Ссылка
ГДЕ
ПеремещениеТоваров.Дата МЕЖДУ &ДатаНачало И &ДатаОкончание
И ВЫБОР
КОГДА &ДокНеЗаполнен = ИСТИНА
ТОГДА ПеремещениеТоваров.ДокументОснование ЕСТЬ NULL
КОГДА &ДокЗаполнен = ИСТИНА
ТОГДА НЕ ПеремещениеТоваров.ДокументОснование ЕСТЬ NULL
КОГДА &НеУчитывать = ИСТИНА
ТОГДА ИСТИНА
ИНАЧЕ ЛОЖЬ
КОНЕЦ
Шаг 2. На форме отчета (в данном случае рассматривается пример для обычных форм) напишем процедуру инициализации списка выбора и обработчик события изменения, который гарантирует, что все три параметра будут заполнены корректными булевыми значениями (один в Истина, остальные в Ложь):
Процедура ПередОткрытием(Отказ, СтандартнаяОбработка)
ЭлементыФормы.ПолеВыбора.СписокВыбора.Добавить(1, "Не учитывать");
ЭлементыФормы.ПолеВыбора.СписокВыбора.Добавить(2, "Заполнен");
ЭлементыФормы.ПолеВыбора.СписокВыбора.Добавить(3, "Не заполнен");
// Установим значение по умолчанию
ПолеВыбора = 1;
КонецПроцедуры
Процедура ПолеВыбораПриИзменении(Элемент)
// Получаем коллекцию параметров компоновщика настроек
ПараметрыДанных = КомпоновщикНастроек.Настройки.ПараметрыДанных;
// Инициализируем переменные параметров
ПараметрНеУчитывать = ПараметрыДанных.Элементы.Найти("НеУчитывать");
ПараметрЗаполнен = ПараметрыДанных.Элементы.Найти("ДокЗаполнен");
ПараметрНеЗаполнен = ПараметрыДанных.Элементы.Найти("ДокНеЗаполнен");
// Включаем использование параметров
ПараметрНеУчитывать.Использование = Истина;
ПараметрЗаполнен.Использование = Истина;
ПараметрНеЗаполнен.Использование = Истина;
// Распределяем значения в зависимости от выбора пользователя
Если ПолеВыбора = 1 Тогда
ПараметрНеУчитывать.Значение = Истина;
ПараметрЗаполнен.Значение = Ложь;
ПараметрНеЗаполнен.Значение = Ложь;
ИначеЕсли ПолеВыбора = 2 Тогда
ПараметрНеУчитывать.Значение = Ложь;
ПараметрЗаполнен.Значение = Истина;
ПараметрНеЗаполнен.Значение = Ложь;
ИначеЕсли ПолеВыбора = 3 Тогда
ПараметрНеУчитывать.Значение = Ложь;
ПараметрЗаполнен.Значение = Ложь;
ПараметрНеЗаполнен.Значение = Истина;
КонецЕсли;
КонецПроцедуры
Проанализируем внимательно конструкцию ПеремещениеТоваров.ДокументОснование.Ссылка ЕСТЬ NULL. Использование обращения через точку (разыменование) к реквизиту Ссылка у поля ДокументОснование — это грубейшая ошибка, негативно влияющая на производительность базы данных.
Реквизит ДокументОснование, как правило, является полем составного типа данных (он может ссылаться на документы "ЗаказПокупателя", "ПоступлениеТоваровУслуг", "СчетНаОплату" и т.д.). Когда в запросе мы пишем ДокументОснование.Ссылка, платформа 1С на уровне СУБД вынуждена выполнить неявное левое соединение (LEFT OUTER JOIN) основной таблицы со всеми таблицами документов, входящими в этот составной тип.
Если в составной тип входит 10 видов документов, СУБД построит 10 дополнительных левых соединений! Это приводит к колоссальному падению скорости выполнения отчета, избыточному потреблению оперативной памяти на сервере базы данных, а при наличии настроенных прав доступа на уровне записей (RLS) может вызвать падение запроса по таймауту.
Правило разработки 1С: Для проверки заполненности ссылочного поля (включая поля составного типа) никогда не используйте обращение через точку к реквизитам. Достаточно проверять само поле на NULL или пустое значение:
// Правильно:
ПеремещениеТоваров.ДокументОснование ЕСТЬ NULL
// Неправильно (вызывает падение производительности):
ПеремещениеТоваров.ДокументОснование.Ссылка ЕСТЬ NULL
Управление тремя булевыми параметрами избыточно и усложняет код на форме отчета (приходится вручную сбрасывать и устанавливать три разных значения). Гораздо проще и элегантнее использовать один целочисленный параметр &РежимОтбора:
1 — Не учитывать;2 — Заполнен;3 — Не заполнен.Посмотрим, как упростится наш запрос в этом случае. Мы можем полностью отказаться от тяжелой конструкции ВЫБОР КОГДА в секции ГДЕ и написать простое линейное логическое условие:
ГДЕ ПеремещениеТоваров.Дата МЕЖДУ &ДатаНачало И &ДатаОкончание
И (&РежимОтбора = 1
ИЛИ (&РежимОтбора = 2 И НЕ ПеремещениеТоваров.ДокументОснование ЕСТЬ NULL И ПеремещениеТоваров.ДокументОснование <> НЕОПРЕДЕЛЕНО)
ИЛИ (&РежимОтбора = 3 И (ПеремещениеТоваров.ДокументОснование ЕСТЬ NULL ИЛИ ПеремещениеТоваров.ДокументОснование = НЕОПРЕДЕЛЕНО)))
Обратите внимание, что для полей составного типа пустая ссылка может возвращаться СУБД как значение НЕОПРЕДЕЛЕНО, поэтому для надежности мы добавили проверку на оба состояния пустоты.
Код управления параметром на форме при этом сокращается до нескольких строк:
Процедура ПолеВыбораПриИзменении(Элемент)
ПараметрРежим = КомпоновщикНастроек.Настройки.ПараметрыДанных.Элементы.Найти("РежимОтбора");
ПараметрРежим.Использование = Истина;
ПараметрРежим.Значение = ПолеВыбора; // Передаем число 1, 2 или 3
КонецПроцедуры
Такой подход делает структуру отчета прозрачной, упрощает поддержку кода и исключает ошибки, связанные с неинициализированными булевыми переменными. Для изучения других приемов настройки отчетов в коде подойдут примеры программной работы со схемами компоновки данных и формами.
Самым правильным решением с точки зрения стандартов разработки 1С является полный отказ от программного кода на форме и ручного написания условий фильтрации в тексте запроса. Платформа СКД содержит мощный встроенный механизм динамического построения условий.
Рассмотрим, как настроить данный отбор без единой строчки кода:
ДокументОснование.В режиме "1С:Предприятие" пользователь получит стандартное и интуитивно понятное поле отбора:
При использовании штатного отбора СКД платформа самостоятельно генерирует максимально оптимизированный SQL-запрос под каждый конкретный выбор пользователя. Если отбор отключен, условие вообще не попадет в итоговый SQL-текст, что обеспечит наивысшую скорость выполнения отчета. Для тонкой настройки интерфейса управляемой формы отчета можно использовать методику стыковки элементов формы и пользовательских настроек СКД.