При интеграции 1С с внешними системами или при обработке файлов загрузки часто возникает классическая задача: найти документы по списку номеров, которые представлены в сокращенном формате. Например, во внешней системе номер документа может храниться как 12345, в то время как внутри информационной базы 1С он записан по всем стандартам автонумерации — Д-000012345.
В этой статье мы подробно разберем, как эффективно решить эту задачу с помощью запросов. Мы выясним, почему популярный метод соединения таблиц по условию ПОДОБНО "%" + ... работает медленно и некорректно, проанализируем неочевидное поведение функции ВЫРАЗИТЬ, а также рассмотрим лучшие практики оптимизации запросов для баз данных любого объема.
Рассмотрим типичную ситуацию, с которой сталкиваются разработчики. У нас есть Таблица Значений (ТЗ), содержащая две колонки: Номер (тип Строка) и Дата (тип Дата). Длина номера в этой таблице может варьироваться, например, от 1 до 5 символов. Нам необходимо сопоставить эти данные с реальными документами в базе данных.
Попытка соединить временную таблицу и реальную таблицу документов «в лоб» через условие соответствия окончания строки часто выглядит следующим образом:
ВЫБРАТЬ
Реализации.Ссылка,
ТЗ.Номер,
ТЗ.Дата
ИЗ
ТЗ КАК ТЗ
ЛЕВОЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг КАК Реализации
ПО (Реализации.Номер ПОДОБНО "%" + ТЗ.Номер)
И ТЗ.Дата = НАЧАЛОПЕРИОДА(Реализации.Дата, ДЕНЬ)
При выполнении такого кода разработчик неизбежно сталкивается с одной из двух проблем:
«Неверные параметры "+"». Это происходит потому, что СУБД не может выполнить конкатенацию строки с неопределенным типом данных.ВЫРАЗИТЬ(ТЗ.Номер КАК СТРОКА(5)). Однако после этого запрос перестает находить документы, у которых длина номера в ТЗ меньше 5 символов.Давайте выясним причину такого странного поведения платформы 1С.
Когда мы используем функцию ВЫРАЗИТЬ(ТЗ.Номер КАК СТРОКА(Х)), СУБД (будь то Microsoft SQL Server, PostgreSQL или IBM DB2) приводит строковое значение к типу фиксированной длины. Это означает, что если реальная длина строки в ячейке меньше, чем указанный параметр Х, СУБД принудительно дополнит строку пробелами справа до достижения заданной длины.
Проанализируем ситуацию на конкретном примере. Предположим, в нашей ТЗ есть номер "123" (длина 3 символа). Мы применяем выражение ВЫРАЗИТЬ(ТЗ.Номер КАК СТРОКА(5)). В результате в СУБД передается строка "123 " (с двумя пробелами на конце).
Когда выполняется условие связи:
ПО Реализации.Номер ПОДОБНО "%" + ТЗ.Номер
Шаблон поиска преобразуется в "%123 ". Естественно, номер документа в базе данных (например, "Д-0000123") не содержит на конце пробелов. Сравнение возвращает ложь, и документ не попадает в выборку. Поиск работает корректно только для тех строк, длина которых изначально была равна ровно 5 символам, так как в этом случае пробелы справа не добавляются.
Чтобы избежать ошибки конкатенации в запросе и при этом не использовать коварную функцию ВЫРАЗИТЬ, необходимо правильно подготавливать Таблицу Значений во встроенном языке еще до передачи ее в качестве параметра запроса.
Для этого колонке с номерами нужно жестко задать тип Строка с ограничением по длине через объект КвалификаторыСтроки. Посмотрим на пример программного создания и типизации таблицы значений:
// Создаем массив типов для описания колонки
МассивТиповСтрока = Новый Массив;
МассивТиповСтрока.Добавить(Тип("Строка"));
// Задаем квалификаторы строки (максимальная длина 20 символов, переменная длина)
// Важно: тип должен быть переменной длины, чтобы СУБД не дополняла его пробелами
КвалификаторыСтр = Новый КвалификаторыСтроки(20, ДопустимаяДлина.Переменная);
ОписаниеТипаСтрока = Новый ОписаниеТипов(МассивТиповСтрока, , КвалификаторыСтр);
// Создаем таблицу значений с типизированными колонками
ТЗ_Номера = Новый ТаблицаЗначений;
ТЗ_Номера.Колонки.Добавить("Номер", ОписаниеТипаСтрока);
ТЗ_Номера.Колонки.Добавить("Дата", Новый ОписаниеТипов("Дата"));
// Заполняем таблицу исходными данными
НоваяСтрока = ТЗ_Номера.Добавить();
НоваяСтрока.Номер = "123";
НоваяСтрока.Дата = '20231024';
Поскольку колонка изначально строго типизирована как строка переменной длины, при помещении таблицы во временную таблицу запроса СУБД определит ее тип корректно. Ошибка сложения строк пропадет, а сами строки не будут дополняться пробелами справа.
Хотя типизация колонок решает проблему работоспособности запроса, использование конструкции ПОДОБНО "%" + ... в условиях соединения таблиц категорически противоречит стандартам разработки 1С. И вот почему:
Полное сканирование (Table Scan / Index Scan): СУБД строит индексы по строковым полям слева направо. Если в шаблоне поиска первый символ является процентом (ведущий дикий символ "%"), СУБД физически не может использовать индекс по полю Номер для быстрого позиционирования. Ей придется прочитать абсолютно все записи таблицы документов за указанный период из базы данных и для каждой строки проводить сложные вычисления по сопоставлению шаблона. На реальных базах с миллионами документов это приведет к зависанию запроса и критическим блокировкам СУБД.
Рассмотрим два гораздо более эффективных и быстрых способа решения этой задачи.
Самый производительный и правильный подход — это отказ от выполнения сложных строковых манипуляций внутри SQL-запроса. Намного быстрее и проще подготовить данные во встроенном языке 1С до вызова запроса.
Если формат номеров в вашей базе данных фиксирован (например, имеет длину 11 символов и вид "Д-000012345", где цифровая часть занимает 9 символов), мы можем программно дополнить входящие номера лидирующими нулями и префиксом.
Рассмотрим, как реализовать этот алгоритм по шагам:
// Предположим, у нас есть исходная ТаблицаЗначений с короткими номерами: ИсходнаяТЗ
// Подготовим финальную ТЗ для передачи в запрос с правильными типами
ТЗ_ДляЗапроса = Новый ТаблицаЗначений;
ТЗ_ДляЗапроса.Колонки.Добавить("Номер", Новый ОписаниеТипов("Строка",,,, Новый КвалификаторыСтроки(11)));
ТЗ_ДляЗапроса.Колонки.Добавить("Дата", Новый ОписаниеТипов("Дата"));
Для Каждого СтрокаТЗ Из ИсходнаяТЗ Цикл
// Очищаем номер от случайных пробелов
СокращенныйНомер = СокрЛП(СтрокаТЗ.Номер);
// Способ 1. Дополнение нулями с помощью Библиотеки Стандартных Подсистем (БСП)
// Дополняем строку слева нулями до нужной длины цифровой части (например, до 9 символов)
ЦифроваяЧасть = СтроковыеФункцииКлиентСервер.ДополнитьСтроку(СокращенныйНомер, 9, "0", "Слева");
// Способ 2. Если БСП не используется, можно применить функцию Формат (если в номере только цифры)
// ЧисловойНомер = Число(СокращенныйНомер);
// ЦифроваяЧасть = Формат(ЧисловойНомер, "ЧГ=0; ЧЦ=9; ЧВ=0");
// Собираем итоговый номер с учетом префикса базы данных
ИтоговыйНомер = "Д-" + ЦифроваяЧасть;
// Добавляем подготовленные данные в новую таблицу
НоваяСтрока = ТЗ_ДляЗапроса.Добавить();
НоваяСтрока.Номер = ИтоговыйНомер;
НоваяСтрока.Дата = НачалоДня(СтрокаТЗ.Дата);
КонецЦикла;
После такой подготовки данных наш запрос становится максимально простым, надежным и, главное, молниеносным. Соединение таблиц будет происходить по точному равенству (оператор =), что позволит СУБД мгновенно находить нужные документы по стандартному индексу поля Номер:
ВЫБРАТЬ
Реализации.Ссылка КАК Ссылка,
ТЗ.Номер КАК Номер,
ТЗ.Дата КАК Дата
ИЗ
&ТЗ_ДляЗапроса КАК ТЗ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг КАК Реализации
ПО Реализации.Номер = ТЗ.Номер
И Реализации.Дата МЕЖДУ ТЗ.Дата И КОНЕЦПЕРИОДА(ТЗ.Дата, ДЕНЬ)
Иногда возникает ситуация, когда префиксы документов в базе данных могут различаться (например, для разных организаций или подразделений используются префиксы "Д-", "ОР-", "МСК-"). Использование оператора ПОДОБНО "%..." здесь также крайне нежелательно.
Проанализируем ситуацию: количество возможных префиксов в базе данных всегда ограничено и заранее известно разработчику. Вместо того чтобы заставлять СУБД перебирать всю базу данных, мы можем программно "размножить" строки в нашей Таблице Значений перед передачей в запрос. Для каждого короткого номера мы сгенерируем все возможные варианты его написания с разными префиксами.
Разберем пример реализации этого метода:
// Определим список возможных префиксов в нашей базе данных
МассивПрефиксов = Новый Массив;
МассивПрефиксов.Добавить("Д-");
МассивПрефиксов.Добавить("ОР-");
МассивПрефиксов.Добавить("МСК-");
// Создаем типизированную таблицу для передачи в запрос
ТЗ_ДляЗапроса = Новый ТаблицаЗначений;
ТЗ_ДляЗапроса.Колонки.Добавить("Номер", Новый ОписаниеТипов("Строка",,,, Новый КвалификаторыСтроки(11)));
ТЗ_ДляЗапроса.Колонки.Добавить("Дата", Новый ОписаниеТипов("Дата"));
Для Каждого СтрокаТЗ Из ИсходнаяТЗ Цикл
СокращенныйНомер = СокрЛП(СтрокаТЗ.Номер);
// Дополняем только цифровую часть номера нулями слева
ЦифроваяЧасть = СтроковыеФункцииКлиентСервер.ДополнитьСтроку(СокращенныйНомер, 9, "0", "Слева");
// Добавляем в таблицу вариантов строки для каждого возможного префикса
Для Каждого ТекущийПрефикс Из МассивПрефиксов Цикл
НоваяСтрока = ТЗ_ДляЗапроса.Добавить();
НоваяСтрока.Номер = ТекущийПрефикс + ЦифроваяЧасть;
НоваяСтрока.Дата = НачалоДня(СтрокаТЗ.Дата);
КонецЦикла;
КонецЦикла;
В самом запросе мы по-прежнему используем быстрое соединение по точному равенству ПО Реализации.Номер = ТЗ.Номер. СУБД отработает этот запрос за доли секунды, задействовав индексы базы данных. Избыточные строки, для которых в базе не нашлось реальных документов, просто не попадут в результат запроса благодаря механизму внутреннего или левого соединения.
Для эффективного поиска документов по части номера в 1С всегда придерживайтесь следующих правил:
ПОДОБНО с ведущим процентом ("%...") в условиях соединения таблиц в запросах. Это приводит к полному сканированию таблиц базы данных и падению производительности системы.ВЫРАЗИТЬ для приведения типов колонок ТЗ внутри запроса, если планируете выполнять поиск подстроки. Данная функция дополняет строки пробелами справа, что искажает результаты сравнения.ОписаниеТипов и КвалификаторыСтроки с переменной длиной символов.