В практике разработки на платформе 1С:Предприятие 8 часто возникает задача проверки целостности данных или выборки документов, которые еще не были обработаны какими-Либо механизмами. Одним из типичных примеров является поиск документов определенного типа, по которым отсутствуют записи в конкретном регистре сведений. Начинающие разработчики часто сталкиваются с трудностями при составлении таких запросов, совершая синтаксические и логические ошибки при работе со значением NULL и механизмом левого соединения.
В этой статье мы подробно разберем, как правильно построить такой запрос, проанализируем типичные ошибки, рассмотрим альтернативные варианты решения и уделим внимание оптимизации производительности СУБД. Также мы выясним, почему при создании внешних обработок с использованием СКД может открываться пустая форма и как это исправить.
Давайте проанализируем ошибки, которые совершил автор темы в своих первых попытках. Это поможет нам глубже понять специфику работы реляционных баз данных и языка запросов 1С.
Ошибка №1: Прямое сравнение с NULL через знак равенства.
В первом сообщении автор пытается связать таблицы следующим образом:
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СЦентр_ЗагрузкаМастеров КАК СЦентр_ЗагрузкаМастеров
ПО (СЦентр_ВыездМастера.Ссылка = NULL)
В базах данных и языке запросов 1С значение NULL означает «отсутствие значения» или «неизвестность». По правилам трехзначной логики SQL, любое прямое сравнение с NULL (будь то = NULL или <> NULL) никогда не возвращает истину. Оно всегда возвращает «Неизвестно» (Unknown), что трактуется условием соединения или фильтрации как «Ложь». Для проверки поля на отсутствие значения необходимо использовать исключительно специальный оператор ЕСТЬ NULL.
Ошибка №2: Попытка фильтрации в условии соединения (секциях ПО / ON).
Во втором сообщении автор пишет:
ПО (СЦентр_ЗагрузкаМастеров.Документ = NULL)
И в шестом сообщении:
ПО СЦентр_ЗагрузкаМастеров.Документ = СЦентр_ВыездМастера.Ссылка
И (СЦентр_ЗагрузкаМастеров.Документ ЕСТЬ NULL)
Здесь кроется важная логическая ошибка. При левом соединении сопоставление строк происходит по условию, указанному после ключевого слова ПО. Если мы пытаемся наложить проверку ЕСТЬ NULL непосредственно в секции соединения, мы нарушаем саму логику операции. На этапе соединения физических таблиц в регистре сведений нет значений NULL в поле связи (если только туда случайно не записали пустую ссылку, но это другая история). Значение NULL во временной результирующей таблице появляется только после того, как левое соединение не нашло соответствия в правой таблице. Поэтому проверку на NULL необходимо выносить в секцию фильтрации всего запроса — в предложение ГДЕ.
Ошибка №3: Нарушение структуры синтаксиса запроса (пропуск таблицы).
В шестом сообщении автор допускает синтаксическую ошибку, которая приводит к падению конструктора запросов:
ВЫБРАТЬ
СЦентр_ВыездМастера.Ссылка КАК Ссылка
ИЗ
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СЦентр_ЗагрузкаМастеров КАК СЦентр_ЗагрузкаМастеров
Здесь после ключевого слова ИЗ сразу идет оператор соединения, при этом полностью пропущена основная (левая) таблица, к которой мы пытаемся присоединить регистр. Конструктор запроса справедливо выдает ошибку «Неверное присоединение».
Рассмотрим правильный алгоритм решения задачи. Чтобы выбрать документы, по которым нет записей в регистре, мы должны использовать следующую логику:
ЛЕВОЕ СОЕДИНЕНИЕ по полю связи (например, по ссылке на документ).ГДЕ укажем фильтр, отсекающий все строки, где поле присоединенной таблицы регистра НЕ ЕСТЬ NULL. То есть мы оставим только те строки документов, для которых сопоставление с регистром вернуло пустоту.Давайте посмотрим, как будет выглядеть этот запрос на практике:
ВЫБРАТЬ
ВыездМастера.Ссылка КАК Ссылка
ИЗ
Документ.СЦентр_ВыездМастера КАК ВыездМастера
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СЦентр_ЗагрузкаМастеров КАК ЗагрузкаМастеров
ПО ВыездМастера.Ссылка = ЗагрузкаМастеров.Документ
ГДЕ
ЗагрузкаМастеров.Документ ЕСТЬ NULL
В данном примере мы берем таблицу документов Документ.СЦентр_ВыездМастера и связываем её с регистром сведений по равенству реквизита (или измерения) Документ ссылке самого документа. В секции ГДЕ мы проверяем поле ЗагрузкаМастеров.Документ на равенство NULL. Те документы, для которых в регистре нашлась хотя бы одна запись, будут иметь в этом поле ссылку, а те, для которых записей нет — получат NULL и попадут в финальную выборку.
Существует и другой способ решения этой задачи — использование отрицания и подзапроса с оператором НЕ В. Давайте проанализируем этот вариант:
ВЫБРАТЬ
ВыездМастера.Ссылка КАК Ссылка
ИЗ
Документ.СЦентр_ВыездМастера КАК ВыездМастера
ГДЕ
НЕ ВыездМастера.Ссылка В
(ВЫБРАТЬ РАЗЛИЧНЫЕ
ЗагрузкаМастеров.Документ КАК Документ
ИЗ
РегистрСведений.СЦентр_ЗагрузкаМастеров КАК ЗагрузкаМастеров)
Как работает этот запрос: Платформа сначала сформирует список всех документов, которые уже фигурируют в регистре сведений (внутренний запрос), а затем выберет из основной таблицы документов только те ссылки, которых нет в этом списке.
Сравнение производительности методов:
ЛЕВЫМ СОЕДИНЕНИЕМ и фильтром ЕСТЬ NULL является стандартной рекомендацией фирмы «1С». Современные системы управления базами данных (СУБД), такие как MS SQL Server или PostgreSQL, отлично оптимизируют такие запросы, превращая их в эффективный план выполнения (Left Anti Semi Join).НЕ В в старых версиях СУБД могла приводить к неоптимальным планам и полному сканированию таблиц (Table Scan), особенно если вложенный запрос возвращал большое количество строк. В современных СУБД оптимизаторы научились приводить оба этих варианта к схожим планам выполнения, однако использование левого соединения все еще остается более надежным и предсказуемым решением с точки зрения производительности.Если в вашей информационной базе хранятся сотни тысяч или миллионы документов, неоптимальный запрос может привести к серьезному зависанию системы и блокировкам таблиц. Чтобы этого избежать, крайне важно убедиться в правильном индексировании полей связи.
Проанализируем структуру регистра сведений СЦентр_ЗагрузкаМастеров:
Документ является измерением регистра, то платформа 1С автоматически создает индекс по этому полю (или оно входит в состав кластерного индекса). В таком случае запрос будет работать максимально быстро, используя поиск по индексу (Index Seek).Документ является всего лишь реквизитом или ресурсом регистра сведений, СУБД будет вынуждена сканировать весь регистр целиком для выполнения соединения. В этой ситуации необходимо зайти в Конфигуратор, открыть свойства данного реквизита/ресурса и в свойстве Индексировать установить значение Индексировать или Индексировать с дополнительным упорядочиванием.В реальных задачах автоматизации практически никогда не требуется выбирать абсолютно все документы за всю историю ведения учета в базе. Это избыточно нагружает сервер. Мы рекомендуем всегда ограничивать выборку рабочим периодом и состоянием проведения документов.
Давайте посмотрим на пример оптимизированного запроса с параметрами:
ВЫБРАТЬ
ВыездМастера.Ссылка КАК Ссылка,
ВыездМастера.Дата КАК Дата
ИЗ
Документ.СЦентр_ВыездМастера КАК ВыездМастера
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СЦентр_ЗагрузкаМастеров КАК ЗагрузкаМастеров
ПО ВыездМастера.Ссылка = ЗагрузкаМастеров.Документ
ГДЕ
ВыездМастера.Проведен = ИСТИНА
И ВыездМастера.Дата МЕЖДУ &НачалоПериода И &КонецПериода
И ЗагрузкаМастеров.Документ ЕСТЬ NULL
Благодаря наложению фильтра по дате СУБД сможет использовать индекс по полю Дата документа, что мгновенно отсечет миллионы старых записей и обработает только актуальный для пользователя интервал времени.
В финальной части обсуждения на форуме автор столкнулся с еще одной технической проблемой (сообщения 9 и 10): он создал внешнюю обработку со схемой компоновки данных (СКД), но при ее запуске в режиме «1С:Предприятие» открывалась абсолютно пустая форма. После переноса схемы во внешний отчет всё заработало автоматически.
Давайте разберем причину этого поведения платформы:
Резюме по СКД: Для любых задач, результатом выполнения которых является вывод таблицы или диаграммы пользователю на экран, следует создавать именно **Внешний отчет** (файлы с расширением .erf), а не внешнюю обработку (.epf). Это сбережет ваше время и позволит использовать все богатые возможности платформы по автоматической генерации пользовательского интерфейса.