Как в 1С реализовать последовательный поиск (сначала по коду, затем по наименованию) в одном запросе с оптимальной производительностью?

← На главную

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

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

1. Единичный поиск: ловушка «одного запроса»

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

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

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

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


// Пример быстрого единичного поиска
СсылкаНаОбъект = Справочники.Номенклатура.НайтиПоКоду(ИскомыйКод);

Если СсылкаНаОбъект.Пустая() Тогда
    СсылкаНаОбъект = Справочники.Номенклатура.НайтиПоНаименованию(ИскомоеНаименование, Истина);
КонецЕсли;

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

2. Почему простое условие ИЛИ снижает производительность?

Рассмотрим классическую ошибку, которую часто предлагают начинающие разработчики:


ВЫБРАТЬ
    Номенклатура.Ссылка КАК Ссылка
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    Номенклатура.Код = &Код
    ИЛИ Номенклатура.Наименование = &Наименование

Выясним причину, почему это условие работает крайне медленно на больших таблицах. Использование логического оператора ИЛИ (OR) в секции ГДЕ мешает оптимизатору СУБД использовать индексы эффективно. СУБД зачастую не может применить индекс по коду и индекс по наименованию одновременно в рамках одного простого сканирования. В результате СУБД переходит к полному сканированию таблицы (Table Scan / Index Scan), что приводит к колоссальным задержкам при росте базы данных.

Кроме того, такой запрос вернет две разные строки, если в базе найдутся разные элементы (один с указанным кодом, другой — с указанным наименованием). Нам же по условию задачи требуется получить строго один элемент: приоритетно по коду, и только при его отсутствии — по наименованию.

3. Пакетный поиск для таблиц: правильный алгоритм с временными таблицами

Если перед нами стоит задача сопоставить целую таблицу данных (например, импортируемый файл из Excel на 10 000 строк), то поочередное обращение к базе данных в цикле для каждой строки абсолютно недопустимо. В этом случае мы обязаны использовать пакетный запрос и временные таблицы.

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

Рассмотрим структуру пакета запросов:

Шаг 1: Помещение исходных данных во временную таблицу

Сначала мы передаем нашу исходную таблицу в качестве параметра запроса и помещаем её во временную таблицу. Критически важно сразу проиндексировать поля связи, чтобы последующие соединения работали мгновенно.


ВЫБРАТЬ
    ИсходнаяТаблица.Код КАК Код,
    ИсходнаяТаблица.Наименование КАК Наименование
ПОМЕСТИТЬ ВТ_ИсходныеДанные
ИЗ
    &ТаблицаДанных КАК ИсходнаяТаблица
;
////////////////////////////////////////////////////////////////////////////////
ИНДЕКСИРОВАТЬ ПО
    Код,
    Наименование
;

Шаг 2: Поиск по коду

На втором шаге мы выполняем левое соединение нашей временной таблицы со справочником по индексированному полю Код. Результат мы также помещаем во временную таблицу.


ВЫБРАТЬ
    ВТ_ИсходныеДанные.Код КАК Код,
    ВТ_ИсходныеДанные.Наименование КАК Наименование,
    Номенклатура.Ссылка КАК СсылкаПоКоду
ПОМЕСТИТЬ ВТ_ПоискПоКоду
ИЗ
    ВТ_ИсходныеДанные КАК ВТ_ИсходныеДанные
        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК Номенклатура
        ПО ВТ_ИсходныеДанные.Код = Номенклатура.Код
;
////////////////////////////////////////////////////////////////////////////////
ИНДЕКСИРОВАТЬ ПО
    Наименование,
    СсылкаПоКоду
;

Шаг 3: Поиск по наименованию (строго для ненайденных)

Теперь мы отсекаем те строки, для которых СсылкаПоКоду заполнилась (то есть НЕ СсылкаПоКоду ЕСТЬ NULL). Поиск по наименованию мы будем выполнять только для тех строк, где ссылка осталась пустой.


ВЫБРАТЬ
    ВТ_ПоискПоКоду.Код КАК Код,
    ВТ_ПоискПоКоду.Наименование КАК Наименование,
    Номенклатура.Ссылка КАК СсылкаПоНаименованию
ПОМЕСТИТЬ ВТ_ПоискПоНаименованию
ИЗ
    ВТ_ПоискПоКоду КАК ВТ_ПоискПоКоду
        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК Номенклатура
        ПО ВТ_ПоискПоКоду.Наименование = Номенклатура.Наименование
            И (ВТ_ПоискПоКоду.СсылкаПоКоду ЕСТЬ NULL)
;
////////////////////////////////////////////////////////////////////////////////
ИНДЕКСИРОВАТЬ ПО
    Код,
    Наименование
;

Шаг 4: Получение финального результата со слиянием ссылок

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


ВЫБРАТЬ
    ВТ_ПоискПоКоду.Код КАК Код,
    ВТ_ПоискПоКоду.Наименование КАК Наименование,
    ЕСТЬNULL(ВТ_ПоискПоКоду.СсылкаПоКоду, ВТ_ПоискПоНаименованию.СсылкаПоНаименованию) КАК Ссылка
ИЗ
    ВТ_ПоискПоКоду КАК ВТ_ПоискПоКоду
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПоискПоНаименованию КАК ВТ_ПоискПоНаименованию
        ПО ВТ_ПоискПоКоду.Код = ВТ_ПоискПоНаименованию.Код
            И ВТ_ПоискПоКоду.Наименование = ВТ_ПоискПоНаименованию.Наименование

Такой подход гарантирует минимальную нагрузку на СУБД. Если из 10 000 строк по коду найдется 9 900 элементов, то тяжелый поиск по строковому полю «Наименование» СУБД выполнит только для оставшихся 100 строк.

4. Тонкости оптимизации производительности

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

Обязательное индексирование временных таблиц: Обратите внимание на операторы ИНДЕКСИРОВАТЬ ПО после каждого пакета создания ВТ. Без создания индексов по полям связи (Код, Наименование, СсылкаПоКоду) СУБД будет вынуждена производить полное сканирование временных таблиц при каждом соединении, что нивелирует всю пользу от разделения запросов.
Избегайте оператора ПОДОБНО (LIKE) с маской в начале строки: Если бизнес-логика позволяет, всегда используйте точное соответствие наименований (ПО Номенклатура.Наименование = ВТ.Наименование). Если вам критически необходим нечеткий поиск, используйте шаблон вида &Наименование + "%" (поиск по началу строки). Использование конструкции ПОДОБНО "%" + &Наименование + "%" (процент в начале шаблона) делает запрос не-SARGable. Это означает, что СУБД принципиально не сможет использовать индекс по наименованию и будет полностью сканировать всю таблицу справочника.
Анализ результатов на уровне встроенного языка: Если вы обрабатываете данные в коде, перед запуском тяжелой части пакета (поиск по наименованию) вы можете выполнить промежуточный быстрый запрос по коду. Если таблица ненайденных элементов оказалась пустой, то выполнять пакеты поиска по наименованию вообще не имеет смысла. Это сбережет ресурсы вашего SQL-сервера.

5. Полнотекстовый поиск как альтернатива

В случаях, когда сопоставление по наименованию требует гибкости (учет опечаток, поиск по части слов в середине строки, синонимы), стандартный язык запросов 1С и оператор ПОДОБНО окажутся крайне медленными и неэффективными.

Для таких сценариев в платформе 1С:Предприятие разработан специализированный механизм — Полнотекстовый поиск (ПТП). Работа с ним строится следующим образом:


// Пример использования полнотекстового поиска для ненайденных строк
СписокПоиска = ПолнотекстовыйПоиск.СоздатьСписок(ИскомаяСтрока, 10);
СписокПоиска.ПолучитьПолнотекстовыйПоиск().СтрокаПоиска = ИскомоеНаименование;
СписокПоиска.ПорогНечеткости = 15; // Позволяет находить слова с опечатками
СписокПоиска.ПерваяЧасть();

Если СписокПоиска.Количество() > 0 Тогда
    НайденнаяСсылка = СписокПоиска[0].Значение;
КонецЕсли;

Этот механизм работает по заранее построенным индексам, находящимся вне транзакционной базы данных СУБД. Использование полнотекстового поиска позволяет находить нужные элементы за миллисекунды, полностью исключая избыточную нагрузку на SQL-сервер.