При интеграции 1С с внешними системами (такими как сайты, CRM, файлы Excel или сторонние базы данных) разработчики регулярно сталкиваются с задачей сопоставления объектов. Классический сценарий: из внешнего источника поступает плоский список контрагентов, где присутствует только текстовое наименование. При этом данные во внешнем источнике зачастую не нормализованы — названия могут содержать лишние пробелы, кавычки, опечатки, а также быть задвоенными или затроенными.
Перед разработчиком встает важный архитектурный вопрос: как организовать хранение связей между текстовыми наименованиями внешних контрагентов и ссылками на справочник СправочникСсылка.Контрагенты в нашей информационной базе? Первое интуитивное решение, которое часто приходит в голову начинающим специалистам, — создать регистр сведений, где измерением будет строка с наименованием внешнего контрагента, а ресурсом — ссылка на элемент нашей базы. Однако опытные разработчики знают, что использование примитивных строковых типов в измерениях регистров сведений скрывает в себе серьезные подводные камни.
В этой статье мы подробно проанализируем, почему строковые измерения в регистрах сведений могут стать источником проблем, рассмотрим ограничения СУБД, а также разберем наиболее эффективные и «кошерные» архитектурные паттерны для решения этой задачи в платформе 1С:Предприятие.
Давайте выясним причину, по которой опытные разработчики протестуют против использования длинных строк в измерениях регистров сведений. Для этого нам потребуется заглянуть на уровень работы СУБД и стандартов разработки фирмы 1С.
Для каждого регистра сведений платформа 1С автоматически создает уникальный индекс по всем его измерениям (а для периодических регистров — еще и по периоду). Этот индекс необходим СУБД для обеспечения уникальности записей и быстрого поиска. В СУБД (например, в MS SQL Server) существуют жесткие лимиты на максимальный размер ключа индекса. Исторически этот лимит составлял 900 байт, в современных версиях он может быть увеличен, но принцип остается прежним.
Поскольку платформа 1С хранит строковые данные в кодировке Unicode (UTF-16), каждый символ занимает ровно 2 байта. Если мы определим измерение как Строка(150), это зарезервирует до 300 байт в ключе индекса. Если в регистре будет несколько таких измерений или строка будет длиннее (например, 250 символов), суммарная длина ключа индекса легко превысит допустимый СУБД лимит. При попытке записать запись с длинной строкой система вернет критическую ошибку СУБД, и транзакция будет прервана.
Сравнение и поиск данных по ссылочным типам происходят мгновенно, так как ссылки в 1С хранятся в виде уникальных 16-байтных бинарных идентификаторов (UUID). СУБД выполняет сравнение таких идентификаторов на аппаратном уровне процессора за минимальное количество тактов.
Работа со строковыми индексами строится совершенно иначе. Поиск, сортировка и соединение таблиц по строковому полю требуют от СУБД посимвольного сравнения строк с учетом установленной кодировки и правил сопоставления. Это создает колоссальную нагрузку на дисковую подсистему и процессор сервера. Стандарты разработки фирмы 1С строго не рекомендуют индексировать строковые поля длиной более 300 символов, а использование их в качестве измерений регистра существенно снижает производительность системы при росте объема данных.
Сравнение строк в базе данных напрямую зависит от настроек сопоставления (Collation) СУБД. Как правило, базы данных 1С настраиваются как регистронезависимые (Case Insensitive). Это означает, что для СУБД строки «ООО РОМАШКА», «ООО Ромашка» и «ооо ромашка» абсолютно идентичны.
Если мы решим записать этих трех контрагентов как три разные записи в регистр со строковым измерением, платформа выдаст ошибку уникальности ключа записи. Мы просто не сможем хранить историю сопоставлений для вариантов написания, отличающихся только регистром, хотя внешняя система может считать их разными объектами. Если же база данных настроена как регистрозависимая, записи добавятся, но при поиске и сопоставлении в запросах нам придется постоянно приводить строки к одному регистру с помощью функций ВРЕГ() или НРЕГ(), что полностью исключает использование индексов и замедляет работу запроса в разы.
Если мы связываем данные через регистр сведений, где измерение — строка, а ресурс — ссылка на нашего контрагента, мы лишаемся стандартного механизма контроля ссылочной целостности 1С. При удалении элемента из справочника Контрагенты (или его замене) запись в регистре сведений останется нетронутой. В поле ресурса появится битая ссылка вида <Объект не найден>, а строковое измерение продолжит висеть в базе данных как «мусор». Автоматическая очистка таких записей платформой невозможна, так как измерение не является «ведущим» ссылочным типом.
В процессе обсуждения часто приводят в пример типовой регистр сведений СоответствияОбъектовИнформационныхБаз из Библиотеки Стандартных Подсистем (БСП), в измерениях которого присутствует строковый тип. Давайте проанализируем этот пример более детально.
Строка в измерениях этого регистра используется исключительно для хранения строкового представления уникального идентификатора (GUID) объекта внешней информационной базы. Длина этой строки жестко ограничена (например, 36 символов для стандартного UUID или чуть больше для составных кодов интеграции). Этот идентификатор не содержит произвольного пользовательского ввода, знаков препинания, кавычек или различных вариантов регистра букв. Именно поэтому в БСП такое решение допустимо и работает стабильно. Использовать же этот подход для хранения неструктурированных наименований контрагентов произвольной длины — грубая архитектурная ошибка.
Рассмотрим по шагам оптимальные варианты построения архитектуры базы данных, которые позволят решить задачу сопоставления чисто, быстро и в соответствии со стандартами 1С.
Вместо того чтобы изобретать регистр со строковыми измерениями, мы можем создать подчиненный или независимый справочник (назовем его, например, ВнешниеНаименованияКонтрагентов).
Структура справочника:
СправочникСсылка.Контрагенты. Это ссылка на сопоставленный элемент в нашей базе данных.Почему это решение оптимально?
В данном сценарии регистр сведений нам вообще не требуется. Связь один-к-одному (1:1) или многие-к-одному (N:1, когда нескольким внешним наименованиям соответствует один наш контрагент) элегантно реализуется на уровне реквизитов справочника.
Посмотрим на пример кода, как будет выглядеть поиск сопоставленного контрагента при загрузке данных:
Функция НайтиСопоставленногоКонтрагента(ИмяВнешнегоКонтрагента) Экспорт
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ВнешниеНаименования.Контрагент КАК Контрагент
|ИЗ
| Справочник.ВнешниеНаименованияКонтрагентов КАК ВнешниеНаименования
|ГДЕ
| ВнешниеНаименования.Наименование = &Наименование";
Запрос.УстановитьПараметр("Наименование", СокрЛП(ИмяВнешнегоКонтрагента));
РезультатЗапроса = Запрос.Выполнить();
Если Не РезультатЗапроса.Пустой() Тогда
Выборка = РезультатЗапроса.Выбрать();
Выборка.Следующий();
Возврат Выборка.Контрагент;
КонецЕсли;
Возврат Справочники.Контрагенты.ПустаяСсылка();
КонецФункции
СУБД быстро найдет элемент справочника по индексу стандартного поля Наименование, а затем вернет ссылку на нашего контрагента. Такое решение работает надежно, не создает избыточных блокировок и легко масштабируется.
Если сопоставление требуется только в рамках одного основного справочника, и вам не хочется плодить новые объекты метаданных в конфигурации, рассмотрим еще один изящный вариант. Мы можем добавить к нашему стандартному справочнику Контрагенты табличную часть, например, ВнешниеНаименования.
Структура табличной части:
При поиске сопоставления мы будем делать запрос к этой табличной части. Посмотрим на пример такого запроса:
Функция НайтиКонтрагентаПоТабличнойЧасти(ИмяВнешнегоКонтрагента) Экспорт
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ ПЕРВЫЕ 1
| КонтрагентыВнешниеНаименования.Ссылка КАК Контрагент
|ИЗ
| Справочник.Контрагенты.ВнешниеНаименования КАК КонтрагентыВнешниеНаименования
|ГДЕ
| КонтрагентыВнешниеНаименования.НаименованиеИсточника = &Наименование";
Запрос.УстановитьПараметр("Наименование", СокрЛП(ИмяВнешнегоКонтрагента));
РезультатЗапроса = Запрос.Выполнить();
Если Не РезультатЗапроса.Пустой() Тогда
Выборка = РезультатЗапроса.Выбрать();
Выборка.Следующий();
Возврат Выборка.Контрагент;
КонецЕсли;
Возврат Справочники.Контрагенты.ПустаяСсылка();
КонецФункции
Этот вариант невероятно удобен, если логика сопоставления жестко привязана к самому контрагенту, и вам не требуется вести сложную историю сопоставлений в разрезе разных внешних баз данных с пересекающимися наименованиями.
Если по каким-то причинам бизнес-логика требует использования именно регистра сведений (например, сопоставление периодическое, или нужно фиксировать автора сопоставления, дату изменения связи и т.д.), то структуру регистра нужно перевернуть по сравнению с первоначальной идеей.
Вместо того чтобы делать строку измерением, а ссылку — ресурсом, мы поступим наоборот:
Контрагент (тип СправочникСсылка.Контрагенты). Это обеспечит уникальность и быстрый поиск по ссылке в нашей базе.НомерСоответствия (тип Число), если одному нашему контрагенту может соответствовать несколько внешних наименований (связь 1:N).НаименованиеВнешнегоКонтрагента (тип Строка).В таком случае уникальный индекс регистра будет построен по ссылочному типу и числу, что гарантирует максимальную производительность СУБД. Проблема дублирования строк на уровне СУБД исчезнет, а хранение внешних наименований в текстовых ресурсах не вызовет переполнения индекса базы данных.
Однако стоит учитывать, что при таком подходе искать контрагента по внешней строке в запросе будет несколько сложнее, так как поиск будет выполняться по ресурсу (неиндексируемому полю). Чтобы это обойти, придется программно индексировать данный ресурс, что опять же возвращает нас к проблемам производительности строковых индексов.
Давайте сопоставим все три архитектурных варианта, чтобы вы могли выбрать наиболее подходящий под ваши задачи:
Проанализировав все возможные варианты, мы можем сделать однозначный вывод: делать измерение регистра сведений неограниченной или длинной строкой под неструктурированное наименование контрагента — не рекомендуется.
Для простых задач сопоставления («один наш — много чужих») идеальным выбором будет создание вспомогательного справочника. Он убережет вашу базу данных от ошибок переполнения индекса СУБД, обеспечит высокую скорость работы запросов и сохранит ссылочную целостность системы. Используйте правильные паттерны проектирования, и ваша база данных всегда будет работать быстро и стабильно!