Что быстрее в запросах 1С: оператор ПОДОБНО или сравнение через ПОДСТРОКА?

При работе с большими базами данных в 1С:Предприятии разработчики часто сталкиваются с необходимостью поиска или фильтрации по текстовым полям. На первый взгляд, задача кажется тривиальной, однако выбор неправильного инструмента в языке запросов может привести к катастрофическому падению производительности всей системы. В этой статье мы подробно разберем классическую дилемму: что работает быстрее на больших объемах данных — оператор ПОДОБНО (аналог SQL-оператора LIKE) или выделение фрагмента текста с помощью функции ПОДСТРОКА (аналог SUBSTRING) с последующим сравнением на равенство. Мы заглянем под капот СУБД, проанализируем планы запросов и выясним истинные причины разницы в скорости работы этих конструкций.

Анализ исходной ситуации: два подхода к поиску подстроки

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

Вариант 1. Использование оператора ПОДОБНО

В первом варианте мы используем стандартный оператор сопоставления с шаблоном:

ВЫБРАТЬ
    Таблица.Ссылка КАК Ссылка,
    Таблица.Наименование КАК Наименование
ИЗ
    Справочник.Номенклатура КАК Таблица
ГДЕ
    Таблица.Наименование ПОДОБНО "%кукареку%"

Вариант 2. Использование функции ПОДСТРОКА и временной таблицы

Во втором варианте разработчик сначала пытается выделить подстроку фиксированной длины из наименования, помещает результат во временную таблицу, а затем выполняет точное сравнение полученного строкового поля с параметром:

ВЫБРАТЬ
    Таблица.Ссылка КАК Ссылка,
    ПОДСТРОКА(Таблица.Наименование, 2, 5) КАК Подстрока
ПОМЕСТИТЬ втТаблица
ИЗ
    Справочник.Номенклатура КАК Таблица
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    втТаблица.Ссылка КАК Ссылка,
    втТаблица.Подстрока КАК Подстрока
ИЗ
    втТаблица КАК втТаблица
ГДЕ
    втТаблица.Подстрока = &Подстрока

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

Почему ПОДСТРОКА работает медленно: скалярные функции в СУБД

Главная причина низкой производительности второго варианта кроется в физических механизмах обработки данных современными системами управления базами данных (MS SQL Server, PostgreSQL, IBM DB2, Oracle). Когда мы пишем ПОДСТРОКА(Таблица.Наименование, 2, 5), платформа 1С транслирует эту конструкцию в стандартную скалярную функцию СУБД (например, SUBSTRING в MS SQL Server).

Проанализируем поведение СУБД при вычислении скалярной функции:

1. Построчная обработка (Row-by-Row): СУБД не может выполнить оптимизацию на уровне наборов данных. Чтобы вычислить значение подстроки, движок базы данных вынужден физически прочитать каждую запись из таблицы, поднять ее в оперативную память, обратиться к строковому значению, вычислить смещение (с какого символа начать) и длину, а затем скопировать выделенный фрагмент в новую область памяти.
2. Высокая нагрузка на CPU: Постоянное выделение памяти (аллокация) под новые строковые объекты и выполнение строковых операций для миллионов записей создает колоссальную нагрузку на центральный процессор сервера СУБД. Это приводит к росту показателя CPU Time в планах запросов.
3. Ограничения распараллеливания: Использование скалярных функций в некоторых случаях может мешать оптимизатору СУБД строить параллельные планы выполнения запросов, что заставляет сервер выполнять задачу в один поток.

Концепция SARGability: как функции блокируют использование индексов

В теории баз данных существует фундаментальный термин — SARGable (Search Argument Able, или способность запроса использовать индексы для поиска). Запрос считается SARGable, если оптимизатор СУБД может напрямую использовать индекс для быстрого позиционирования на нужной записи (операция Index Seek) без сканирования всей таблицы.

Рассмотрим, как применение функции ПОДСТРОКА влияет на индексную оптимизацию:

• Любая функция (будь то ПОДСТРОКА, ГОД, НАЧАЛОПЕРИОДА или математические операции), примененная к индексируемому полю в секции ГДЕ или в условиях связи ПО, делает выражение не-SARGable.
• Даже если поле Наименование проиндексировано в конфигураторе 1С, СУБД никогда не сможет использовать этот индекс для точечного поиска. Индекс хранит упорядоченные значения исходных строк (например, "Апельсин", "Банан", "Груша"), но он ничего не знает о результатах функции ПОДСТРОКА.
• В итоге СУБД вынуждена отказаться от быстрого поиска (Index Seek) и перейти к полному сканированию таблицы (Table Scan или Clustered Index Scan), что на больших таблицах занимает критически много времени.

Анатомия работы оператора ПОДОБНО (LIKE)

Теперь проанализируем, почему оператор ПОДОБНО выигрывает в скорости. Этот оператор является встроенным, нативным элементом языка SQL. СУБД изначально спроектированы так, чтобы максимально эффективно обрабатывать операции сопоставления по шаблону.

Поведение СУБД при использовании ПОДОБНО сильно зависит от структуры шаблона:

Случай А: Шаблон с процентом в начале (например, "%кукареку%")

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

• СУБД использует низкоуровневые, глубоко оптимизированные алгоритмы посимвольного сравнения "на лету" (включая векторные инструкции процессора SIMD).
• Не происходит выделения памяти под новые строковые объекты для каждой записи, данные анализируются непосредственно в буферном пуле кэша СУБД.
• Если индекс по полю существует, СУБД выполнит сканирование индекса (Index Scan), а не самой таблицы. Поскольку индекс содержит только ключевое поле и ссылку на запись, он занимает на диске значительно меньше места, чем вся таблица с десятками других реквизитов. Чтение меньшего объема данных с диска или из кэша памяти экономит драгоценное время ввода-вывода (I/O).

Случай Б: Шаблон без процента в начале (например, "кукареку%")

Это идеальный сценарий для оптимизатора. Если поле проиндексировано, СУБД мгновенно преобразует это условие в диапазон (например, поле >= 'кукареку' И поле < 'кукарекф') и выполняет быструю операцию Index Seek. Запрос отрабатывает за считанные миллисекунды даже на таблицах объемом в десятки миллионов строк.

Накладные расходы на временные таблицы в 1С

Вернемся ко второму варианту нашего примера, где использовалась временная таблица. Помимо медленной работы скалярной функции ПОДСТРОКА, этот подход порождает колоссальные накладные расходы на уровне платформы 1С и СУБД:

1. Запись во временную базу данных (I/O): Конструкция ПОМЕСТИТЬ втТаблица заставляет СУБД физически создать таблицу во временной базе данных (например, в tempdb для MS SQL Server), рассчитать подстроку для каждой записи исходной таблицы и записать весь этот массив данных на диск или в кэш временной базы. Операции записи — самые дорогие и медленные в СУБД.
2. Проблема типизации в 1С: При трансляции функции ПОДСТРОКА платформа 1С накладывает жесткие правила типизации. Чтобы гарантировать стабильность типов во временной таблице, поле Подстрока может быть принудительно типизировано как строка фиксированной или максимальной ограниченной длины (например, NVARCHAR(4000)). Это резко увеличивает "ширину" строк временной таблицы, приводя к нерациональному использованию оперативной памяти и дискового пространства.
3. Двойное чтение данных: Вместо однократного прохода по таблице СУБД сначала полностью сканирует исходную таблицу для создания временной, записывает результат, а затем снова полностью считывает временную таблицу для выполнения фильтрации ГДЕ втТаблица.Подстрока = &Подстрока.

Таким образом, вариант с промежуточным помещением во временную таблицу объединяет в себе все возможные антипаттерны оптимизации запросов СУБД.

Рекомендации и лучшие практики по оптимизации строкового поиска

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

1. Никогда не используйте функции над полями БД в условиях ГДЕ и связях ПО

Если вам нужно сравнить начало строки с определенным значением, заменяйте функцию ПОДСТРОКА оператором ПОДОБНО с концевым шаблоном. Посмотрим на пример оптимизации:

Плохо:

ГДЕ
    ПОДСТРОКА(Таблица.Наименование, 1, 5) = "Аванс"

Хорошо (СУБД сможет использовать индекс):

ГДЕ
    Таблица.Наименование ПОДОБНО "Аванс%"

2. Своевременное индексирование текстовых полей

Если по какому-то строковому полю (например, артикул, ИНН, код) часто происходит поиск или фильтрация, обязательно установите свойство "Индексировать" (или "Индексировать с доп. упорядочиванием") для этого реквизита в конфигураторе 1С. Это позволит СУБД выполнять операцию Index Seek при поиске по точному совпадению или по началу строки с помощью оператора ПОДОБНО "Значение%".

3. Использование Полнотекстового Поиска (ППД)

Если перед вами стоит задача осуществлять поиск подстроки в любой части текстового поля (шаблон вида "%кукареку%") на таблицах сверхбольшого объема (миллионы строк), то ни оператор ПОДОБНО, ни тем более ПОДСТРОКА не обеспечат приемлемой скорости работы. Полное сканирование индекса или таблицы при высокой интенсивности запросов парализует работу системы.

В этом случае необходимо использовать встроенные механизмы полнотекстового поиска 1С:

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

4. Метод предварительного разделения данных (Денормализация)

Если вам регулярно требуется анализировать строго определенную часть строки (например, выделять код региона из номера телефона или категорию из артикула), не делайте этого динамически в запросах. Оптимальным архитектурным решением будет выделение этой подстроки в отдельный реквизит объекта при записи:

1. Создайте новый индексируемый реквизит в документе или справочнике (например, КодРегиона).
2. В событии ПередЗаписью модуля объекта программно выделяйте подстроку и записывайте ее в этот реквизит.
3. В запросах выполняйте простое и быстрое сравнение напрямую по новому полю: ГДЕ Таблица.КодРегиона = &КодРегиона.

Подведем итоги

Проанализировав физику процессов на уровне СУБД, мы выяснили, что оператор ПОДОБНО работает быстрее функции ПОДСТРОКА, поскольку он является нативным оператором сравнения СУБД и оптимизирован на низком уровне. Использование же скалярной функции ПОДСТРОКА приводит к ресурсоемким вычислениям для каждой строки, лишает запрос свойства SARGability (блокирует использование индексов), а в случае использования временных таблиц — генерирует тяжелые дисковые операции записи и чтения в tempdb. Проектируйте ваши запросы правильно, избегайте функций в условиях фильтрации, и ваши информационные базы 1С будут работать быстро и стабильно!