Как ускорить запрос в 1С при проверке составного поля на Неопределено?

Программист 1С v8.3 (Управляемые формы) IT и автоматизация бизнеса
← На главную

Каждый разработчик 1С рано или поздно сталкивается с ситуацией, когда простейший запрос по выборке документов за период начинает выполняться мучительно долго. Мы открываем замер производительности и видим, что одна-единственная строчка кода забирает 97% времени работы всей процедуры. Ситуация усугубляется, когда в условиях ГДЕ присутствует проверка реквизита составного типа на равенство значению Неопределено.

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

Почему СУБД медленно обрабатывает «Неопределено» в составных полях

Для того чтобы понять суть проблемы, нам необходимо заглянуть под капот платформы 1С и посмотреть, как реквизиты составного типа хранятся на уровне СУБД (MS SQL Server, PostgreSQL и др.).

Когда мы создаем реквизит простого ссылочного типа (например, ссылку на конкретный справочник), в таблице базы данных под него выделяется всего одно поле — уникальный идентификатор (GUID), имеющий тип данных binary(16). Но как только мы делаем реквизит составного типа (например, допускаем ссылки на 5 разных документов), СУБД вынуждена создавать структуру из нескольких колонок для хранения одной логической сущности:

  1. Колонка типа данных (обычно суффикс _TYPE): Хранит маркер, определяющий, к какой именно таблице относится ссылка (или указывает на системный тип, такой как Число, Строка, Дата, Булево или Неопределено).
  2. Колонка уникального идентификатора (суффикс _RRef): Содержит сам GUID записи.
  3. Колонки простых типов (при наличии): Если составной тип включает Число, Строку или Дату, под них создаются отдельные физические колонки.

Когда мы пишем в запросе условие ПриходнаяНакладная.ДокументДоп = Неопределено, платформа 1С транслирует его в SQL-запрос, который проверяет значения сразу в нескольких физических колонках таблицы. Если база данных имеет внушительный объем, а оптимизатор СУБД не может эффективно применить индексы, происходит полное сканирование таблицы (Clustered Index Scan или Table Scan). Вместо быстрого точечного поиска СУБД перебирает миллионы записей, чтобы проверить состояние этих колонок.

Ловушка иллюзии «ЕСТЬ NULL»

Многие разработчики замечают интересную деталь: если заменить условие сравнения на ЕСТЬ NULL, запрос начинает выполняться мгновенно. Рассмотрим, почему это происходит и почему это решение является грубой ошибкой.

На физическом уровне СУБД колонки составного поля по умолчанию объявляются с ограничением NOT NULL. Даже если реквизит не заполнен, СУБД записывает туда вполне конкретные дефолтные значения (например, нулевой GUID и маркер неопределенного типа). Физически эти поля никогда не равны значению NULL в терминах СУБД.

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

Ловушка неявного разыменования («через точку»)

Иногда на форумах предлагают использовать конструкцию вида ПриходнаяНакладная.ДокументДоп.Ссылка ЕСТЬ NULL. Давайте разберем, к чему это приведет на практике.

Обращение "через точку" к реквизитам поля составного типа заставляет платформу 1С генерировать в итоговом SQL-запросе неявные левые соединения (LEFT JOIN) со всеми таблицами, входящими в этот составной тип. Если тип включает 5 документов, СУБД будет вынуждена выполнить 5 соединений таблиц. План запроса перегружается вложенными циклами (Nested Loops), что приводит к катастрофическому падению производительности при увеличении объема данных.

---

Разбор практических решений по оптимизации запроса

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

Способ 1. Явное перечисление пустых ссылок всех типов

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

Перепишем наш исходный запрос следующим образом:


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |	ПриходнаяНакладная.Ссылка КАК Ссылка
    |ИЗ
    |	Документ.ПриходнаяНакладная КАК ПриходнаяНакладная
    |ГДЕ
    |	ПриходнаяНакладная.Дата МЕЖДУ &Дата1 И &Дата2
    |	И НЕ ПриходнаяНакладная.ПометкаУдаления
    |	И (ПриходнаяНакладная.ДокументДоп = Неопределено
    |		ИЛИ ПриходнаяНакладная.ДокументДоп = ЗНАЧЕНИЕ(Документ.Документ1.ПустаяСсылка)
    |		ИЛИ ПриходнаяНакладная.ДокументДоп = ЗНАЧЕНИЕ(Документ.Документ2.ПустаяСсылка)
    |		ИЛИ ПриходнаяНакладная.ДокументДоп = ЗНАЧЕНИЕ(Документ.Документ3.ПустаяСсылка)
    |		ИЛИ ПриходнаяНакладная.ДокументДоп = ЗНАЧЕНИЕ(Документ.Документ4.ПустаяСсылка)
    |		ИЛИ ПриходнаяНакладная.ДокументДоп = ЗНАЧЕНИЕ(Документ.Документ5.ПустаяСсылка))";

Запрос.УстановитьПараметр("Дата1", НачалоДня);
Запрос.УстановитьПараметр("Дата2", КонецДня);
РезультатЗапроса = Запрос.Выполнить();

Преимущества: Запрос остается простым, не требует изменения структуры метаданных конфигурации, работает корректно и позволяет СУБД оптимально построить план выполнения, используя индекс по дате и составному полю.

Способ 2. Очистка пустых ссылок при записи («ПередЗаписью»)

Очень часто проблема медленной работы кроется в "грязных" данных. Если пользователь сначала заполнил реквизит ДокументДоп, а затем стер его значение, в базе сохраняется пустая ссылка конкретного типа (например, пустая ссылка Документ.Документ1), а не чистое Неопределено.

Мы можем стандартизировать хранение данных. Для этого в модуле объекта документа ПриходнаяНакладная в обработчике события ПередЗаписью внедрим принудительное приведение пустых значений к единому виду Неопределено:


Процедура ПередЗаписью(Отказ, РежимЗаписи, РежимПроведения)
    
    // Если значение реквизита логически не заполнено, 
    // принудительно очищаем его до Неопределено
    Если Не ЗначениеЗаполнено(ДокументДоп) Тогда
        ДокументДоп = Неопределено;
    КонецЕсли;
    
КонецПроцедуры

Преимущества: Мы гарантируем абсолютную чистоту данных. Все незаполненные поля теперь физически содержат только Неопределено. Наш исходный запрос автора темы с условием ПриходнаяНакладная.ДокументДоп = Неопределено начнет отрабатывать значительно быстрее, так как СУБД больше не придется проверять пустые ссылки различных типов.

Способ 3. Денормализация данных (введение булева флага)

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

Мы можем добавить в документ ПриходнаяНакладная новый реквизит ДокументДопЗаполнен с типом Булево и установить для него свойство "Индексировать".

Алгоритм реализации выглядит следующим образом:

  1. В обработчике ПередЗаписью документа автоматически рассчитываем значение флага: 
    
ДокументДопЗаполнен = ЗначениеЗаполнено(ДокументДоп);
  2. Переписываем наш запрос, заменяя тяжелое условие по составному полю на простую проверку булева флага: 
    
Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |	ПриходнаяНакладная.Ссылка КАК Ссылка
    |ИЗ
    |	Документ.ПриходнаяНакладная КАК ПриходнаяНакладная
    |ГДЕ
    |	ПриходнаяНакладная.Дата МЕЖДУ &Дата1 И &Дата2
    |	И НЕ ПриходнаяНакладная.ПометкаУдаления
    |	И НЕ ПриходнаяНакладная.ДокументДопЗаполнен";

Преимущества: Это самый производительный промышленный метод. СУБД мгновенно находит нужные строки по индексу булева поля. Время выполнения запроса сокращается до сотых долей секунды даже на гигантских базах данных.

Способ 4. Использование временных таблиц для сужения выборки

Если мы не можем менять метаданные и дорабатывать проведение документов, можно оптимизировать работу СУБД на уровне логики выполнения пакета запросов. Сначала мы выберем во временную таблицу документы за период без наложения тяжелого фильтра по составному полю (что позволит эффективно отработать индексу по дате), а уже вторым шагом отсечем лишнее.

Проанализируем следующий пакетный запрос:


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |	ПриходнаяНакладная.Ссылка КАК Ссылка,
    |	ПриходнаяНакладная.ДокументДоп КАК ДокументДоп
    |ПОМЕСТИТЬ ВТ_НакладныеЗаПериод
    |ИЗ
    |	Документ.ПриходнаяНакладная КАК ПриходнаяНакладная
    |ГДЕ
    |	ПриходнаяНакладная.Дата МЕЖДУ &Дата1 И &Дата2
    |	И НЕ ПриходнаяНакладная.ПометкаУдаления
    |ИНДЕКСИРОВАТЬ ПО
    |	Ссылка
    |;
    |
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ
    |	ВТ.Ссылка КАК Ссылка
    |ИЗ
    |	ВТ_НакладныеЗаПериод КАК ВТ
    |ГДЕ
    |	ВТ.ДокументДоп = Неопределено";

Запрос.УстановитьПараметр("Дата1", НачалоДня);
Запрос.УстановитьПараметр("Дата2", КонецДня);
РезультатЗапроса = Запрос.Выполнить();

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

Резюме и рекомендации

Мы разобрали ключевые механизмы работы платформы с составными типами данных и методы их оптимизации. При выборе конкретного решения мы рекомендуем придерживаться следующих правил:

← На главную