Как оптимизировать запросы с оператором ПОДОБНО, ключевым словом ПЕРВЫЕ N и RLS в 1С:УТ 10.3?

← На главную

В этой статье мы подробно рассмотрим распространенную проблему низкой производительности запросов в 1С:Предприятии, особенно когда они включают оператор ПОДОБНО, ключевое слово ПЕРВЫЕ N и ограничения прав доступа на уровне записей (RLS). Мы проанализируем, как каждый из этих элементов влияет на скорость выполнения, и предложим эффективное решение, основанное на практическом опыте. Нашей целью будет значительное ускорение запросов, которые могут занимать до нескольких минут.

Понимание ключевого слова "ПЕРВЫЕ N" (TOP)

Начнем с рассмотрения ключевого слова ПЕРВЫЕ N (или TOP N в SQL), которое используется в запросах 1С для ограничения количества возвращаемых записей. Часто возникает вопрос, обрабатывает ли система управления базами данных (СУБД) все записи, а затем выбирает первые N, или же прекращает обработку после нахождения N записей.

Без предложения УПОРЯДОЧИТЬ ПО: Если в запросе отсутствует явная сортировка с использованием УПОРЯДОЧИТЬ ПО, СУБД может прекратить поиск после нахождения первых N записей, удовлетворяющих условиям фильтрации. Это может существенно ускорить запрос, так как не требуется сканировать всю таблицу. Однако, важно понимать, что в этом случае порядок возвращаемых записей не гарантирован и может меняться от запуска к запуску, что может привести к непредсказуемым результатам.
С предложением УПОРЯДОЧИТЬ ПО: Если же в запросе присутствует сортировка, СУБД вынуждена сначала обработать все записи, соответствующие условиям фильтрации, чтобы затем их отсортировать. Только после полной сортировки будут выбраны первые N записей. В этом сценарии ПЕРВЫЕ N ограничивает лишь размер конечного набора результатов, но не объем данных, обрабатываемых СУБД для сортировки.

Таким образом, эффективность использования ПЕРВЫЕ N напрямую зависит от наличия и типа сортировки в вашем запросе. Если порядок не важен, отсутствие сортировки может дать значительный выигрыш в скорости. Если же нужен конкретный порядок, СУБД все равно выполнит полную сортировку.

Влияние оператора ПОДОБНО (LIKE) на производительность

Оператор ПОДОБНО (аналог LIKE в SQL) часто является одной из основных причин медленной работы запросов, особенно когда в условии поиска используются символы % (процента) в начале и в конце строки (например, '%строка%').

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

Возможные решения:

Полнотекстовый поиск: Для поиска по произвольным подстрокам наиболее эффективным решением в платформе 1С:Предприятие является полнотекстовый поиск. Он использует специализированные индексы (например, Microsoft SQL Server Full-Text Search или pg_trgm в PostgreSQL), которые позволяют значительно быстрее находить вхождения подстрок в текстовых полях. Полнотекстовый поиск можно использовать как через стандартные механизмы платформы, так и программно через объект ПолнотекстовыйПоиск. Для его корректной работы необходимо убедиться, что соответствующий индекс настроен и регулярно обновляется.
Индексирование для поиска по префиксу: Если поиск ведется только по префиксу (например, 'строка%', без % в начале), то обычные индексы по полю могут быть эффективно использованы. Убедитесь, что для полей, по которым вы часто ищете по префиксу, созданы индексы.
Альтернативные подходы: В некоторых сложных сценариях можно рассмотреть создание дополнительных полей для хранения нормализованных данных, хэш-значений или использования специализированных методов поиска, но это уже более продвинутые техники, требующие глубокого анализа структуры данных.

Ограничения прав доступа на уровне записей (RLS) и их оптимизация

RLS (Row-Level Security) – это механизм платформы 1С, который позволяет ограничивать доступ пользователей к данным на уровне отдельных записей. Несмотря на свою полезность для безопасности, RLS может оказывать значительное негативное влияние на производительность запросов.

Проблема с RLS: Когда RLS активно, платформа 1С автоматически добавляет дополнительные условия в запросы к базе данных. Эти условия могут быть достаточно сложными и часто препятствуют эффективному использованию индексов СУБД, что приводит к полному сканированию таблиц и замедлению выполнения запросов. Даже использование ПЕРВЫЕ N может оказаться неэффективным, так как СУБД может быть вынуждена обработать большой объем данных для применения RLS, прежде чем выбрать первые N разрешенных записей.

Оптимизация RLS с помощью привилегированного режима: Одним из радикальных, но эффективных способов обойти замедление от RLS является временное отключение RLS с помощью функции УстановитьПривилегированныйРежим(Истина). В этом режиме запросы выполняются без учета RLS, и платформа не добавляет скрытых условий. После получения данных, которые потенциально могут быть ограничены RLS, вы должны самостоятельно программно отфильтровать их в соответствии с требуемыми правами доступа, а затем обязательно отключить привилегированный режим функцией УстановитьПривилегированныйРежим(Ложь). Этот подход требует особой осторожности и тщательной проверки, чтобы не нарушить безопасность данных.

Пример оптимизации запроса в 1С:УТ 10.3

Рассмотрим реальный пример из форума, где пользователь столкнулся с медленным выполнением запроса в форме списка документов 1С:УТ 10.3. Изначальный запрос включал поиск по наименованию контрагентов с использованием ПОДОБНО и работал крайне медленно, до 3-4 минут, усугубляясь ограничениями RLS.

Проблемный запрос (упрощенный вариант автора):

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


Запрос.Текст = "ВЫБРАТЬ РАЗРЕШЕННЫЕ
             | Ссылка
             |ИЗ
             | Справочник.Контрагенты
             |ГДЕ
             | ( Наименование ПОДОБНО &пНаименование)";
// Параметр: &пНаименование = "%" + СокрЛП(ЭтаФорма.ИмяКонт) + "%"

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

Штатный запрос поиска контрагентов из формы (показательный пример сложности):

Для понимания масштаба проблемы, пользователь также привел пример штатного запроса поиска контрагентов в форме, который платформа 1С генерирует для автоподбора. Этот запрос демонстрирует, насколько сложными могут быть условия, особенно с использованием ОБЪЕДИНИТЬ и ПОДОБНО по нескольким полям:


ВЫБРАТЬ РАЗЛИЧНЫЕ РАЗРЕШЕННЫЕ Первые 1001
    СправочникКонтрагентыУникальноеИмя2.Ссылка КАК УникальныйОбъектПоиска
ИЗ
    Справочник.Контрагенты КАК СправочникКонтрагентыУникальноеИмя2
ГДЕ
    (СправочникКонтрагентыУникальноеИмя2.КОД ПОДОБНО &ТекстАвтоПодбора + "" СПЕЦСИМВОЛ "~" )
// {ГДЕ СправочникКонтрагентыУникальноеИмя2.Ссылка.* КАК СправочникКонтрагенты } - это условие RLS, добавляемое платформой
ОБЪЕДИНИТЬ
ВЫБРАТЬ РАЗЛИЧНЫЕ Первые 1001
    СправочникКонтрагентыУникальноеИмя2.Ссылка КАК УникальныйОбъектПоиска
ИЗ
    Справочник.Контрагенты КАК СправочникКонтрагентыУникальноеИмя2
ГДЕ
    (СправочникКонтрагентыУникальноеИмя2.НАИМЕНОВАНИЕ ПОДОБНО &ТекстАвтоПодбора + "" СПЕЦСИМВОЛ "~" )
// {ГДЕ СправочникКонтрагентыУникальноеИмя2.Ссылка.* КАК СправочникКонтрагенты }
ОБЪЕДИНИТЬ
ВЫБРАТЬ РАЗЛИЧНЫЕ Первые 1001
    СправочникКонтрагентыУникальноеИмя2.Ссылка КАК УникальныйОбъектПоиска
ИЗ
    Справочник.Контрагенты КАК СправочникКонтрагентыУникальноеИмя2
ГДЕ
    (СправочникКонтрагентыУникальноеИмя2.ИНН ПОДОБНО &ТекстАвтоПодбора + "" СПЕЦСИМВОЛ "~" )
// {ГДЕ СправочникКонтрагентыУникальноеИмя2.Ссылка.* КАК СправочникКонтрагенты }
ОБЪЕДИНИТЬ
ВЫБРАТЬ РАЗЛИЧНЫЕ Первые 1001
    СправочникКонтрагентыУникальноеИмя2.Ссылка КАК УникальныйОбъектПоиска
ИЗ
    Справочник.Контрагенты КАК СправочникКонтрагентыУникальноеИмя2
ГДЕ
    (СправочникКонтрагентыУникальноеИмя2.НАИМЕНОВАНИЕПОЛНОЕ ПОДОБНО &ТекстАвтоПодбора + "" СПЕЦСИМВОЛ "~" )
// {ГДЕ СправочникКонтрагентыУникальноеИмя2.Ссылка.* КАК СправочникКонтрагенты }

Этот штатный запрос, демонстрирующий поиск по нескольким полям с ПОДОБНО и множественными ОБЪЕДИНИТЬ, лишь подчеркивает сложность задачи по оптимизации.

Оптимизированное решение:

Автор реализовал следующее оптимизированное решение, которое включало использование привилегированного режима и встроенную фильтрацию RLS в сам запрос, а также последующую программную фильтрацию для динамического списка:


УстановитьПривилегированныйРежим(Истина);
СтандартнаяОбработка = Ложь;

Запрос = Новый Запрос;
Запрос.УстановитьПараметр("пНаименование", "%" + СокрЛП(ЭтаФорма.ИмяКонт1) + "%");
Запрос.УстановитьПараметр("ОграничениеКА", ПараметрыСеанса.ОграничениеКА);
Запрос.УстановитьПараметр("ОграничениеКА2", ПараметрыСеанса.ОграничениеКА2);

// В запрос включены ПЕРВЫЕ 1001 и условия RLS
Запрос.Текст = "ВЫБРАТЬ РАЗРЕШЕННЫЕ Первые 1001
             | Ссылка
             |ИЗ
             | Справочник.Контрагенты
             |ГДЕ
             | ( Ссылка Не в Иерархии (&ОграничениеКА))
             | И ( Ссылка Не в Иерархии (&ОграничениеКА2) )
             | И ( Наименование ПОДОБНО &пНаименование)";

Результат = Запрос.Выполнить(); // Не забываем скобки для метода

// Создаем список значений и загружаем ссылки
сз = Новый СписокЗначений;
сз.ЗагрузитьЗначения(Результат.Выгрузить().ВыгрузитьКолонку("Ссылка"));

// Применяем фильтр к динамическому списку на форме
ЭтаФорма.ДокументСписок.Отбор.Контрагент.ВидСравнения = ВидСравнения.ВСписке;
ЭтаФорма.ДокументСписок.Отбор.Контрагент.Значение = сз;
ЭтаФорма.ДокументСписок.Отбор.Контрагент.Использование = Истина;

// Дополнительная программная фильтрация, имитирующая RLS для "ДокументыПоОтветсвенному"
Если НЕ(РольДоступна("ДокиПоОтветственному")) Тогда
    СпрПользователь = УправлениеПользователями.ОпределитьТекущегоПользователя;
    СписокПользователей = Новый СписокЗначений;
    СписокПользователей.Добавить(СпрПользователь);
    ЭтаФорма.ДокументСписок.Отбор.ДокументыПоОтветсвенному.ВидСравнения = ВидСравнения.ВСписке;
    ЭтаФорма.ДокументСписок.Отбор.ДокументыПоОтветсвенному.Значение = СписокПользователей;
    ЭтаФорма.ДокументСписок.Отбор.ДокументыПоОтветсвенному.Использование = Истина;
КонецЕсли;

УстановитьПривилегированныйРежим(Ложь);

Разберем ключевые моменты решения:

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

Результат: После внедрения этого подхода, скорость выполнения запроса выросла в 40 раз, и исчезли длительные зависания, которые ранее наблюдались.

Общие рекомендации по оптимизации производительности запросов

В дополнение к рассмотренному решению, предлагаем общие рекомендации, которые помогут вам оптимизировать производительность запросов в 1С:Предприятии:

Использование полнотекстового поиска: Для поиска по произвольным подстрокам в текстовых полях всегда отдавайте предпочтение полнотекстовому поиску перед ПОДОБНО '%строка%'. Убедитесь, что полнотекстовый индекс актуален и настроен для нужных полей.
Индексирование полей: Создавайте или проверяйте наличие индексов для полей, используемых в условиях ГДЕ, СОЕДИНЕНИЕ, УПОРЯДОЧИТЬ ПО. В 1С это делается через свойства полей в конфигураторе ("Индексировать", "Индексировать (дополнительно)"). Правильно настроенные индексы могут кардинально ускорить выборку данных.
Анализ плана запроса СУБД: Если запрос все еще медленный, используйте средства СУБД (например, SQL Server Management Studio для MS SQL Server или pgAdmin для PostgreSQL) для анализа плана выполнения запроса. Это поможет понять, какие операции занимают больше всего времени, какие индексы используются (или не используются) и где возникают "узкие места".
Актуальность статистики СУБД: Регулярное обновление статистики в СУБД (часто происходит при тестировании и исправлении базы или при обслуживании средствами СУБД) помогает оптимизатору запросов принимать более эффективные решения по выбору планов выполнения.
Избегайте ОБЪЕДИНИТЬ для однотипных условий: Если возможно, объединяйте условия с ИЛИ вместо множественных ОБЪЕДИНИТЬ для почти одинаковых выборок. Однако, в случае ПОДОБНО по разным полям, ОБЪЕДИНИТЬ может быть единственным корректным способом, но в этом случае каждый подзапрос должен быть максимально оптимизирован.
Пересмотр RLS: Если RLS является основной причиной замедлений, оцените возможность упрощения его логики в конфигурации или рассмотрите сценарии, где его можно временно отключать с последующей программной фильтрацией, как показано в примере.

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