В процессе разработки и сопровождения конфигураций на платформе 1С:Предприятие мы часто сталкиваемся с наследием прошлых разработчиков или поспешными архитектурными решениями. Типичный пример такой ситуации — когда для сущности, предполагающей множественный выбор, в справочнике создается несколько фиксированных реквизитов (например, Спец, Спец1, Спец2 и так далее) вместо полноценной табличной части или регистра сведений. В этой статье мы подробно разберем, как решить возникшую проблему на уровне запроса в текущих реалиях, проанализируем узкие места с точки зрения производительности СУБД и рассмотрим, как правильно выполнить рефакторинг структуры метаданных для достижения оптимального результата.
Представим задачу: у нас есть справочник Контрагенты, у которого заведены реквизиты специальностей (строитель, слесарь, механик и т.д.). Контрагент может обладать одной специальностью, несколькими или вообще не иметь их. Нам необходимо выбрать всех контрагентов, у которых есть определенная специальность (например, «Строитель»), включая тех, у кого помимо этой специальности указаны другие.
При стандартном подходе мы пытаемся написать простой запрос к табличной части контактной информации контрагента, накладывая условие на реквизит связи:
ВЫБРАТЬ
КонтактнаяИнформация.Ссылка КАК Ссылка,
КонтактнаяИнформация.НомерТелефона КАК НомерТелефона
ИЗ
Справочник.Контрагенты.КонтактнаяИнформация КАК КонтактнаяИнформация
ГДЕ
НЕ КонтактнаяИнформация.Ссылка.ПометкаУдаления
И КонтактнаяИнформация.Ссылка.Спец = &Спец
Однако при таком подходе мы сталкиваемся с тем, что контрагенты, имеющие только одну специальность «Строитель», попадают в выборку, а контрагенты со специальностями «Строитель» и «Механик» — игнорируются. Выясним причину этой проблемы. Как оказалось при детальном анализе метаданных, разработчик конфигурации создал в справочнике Контрагенты несколько независимых реквизитов: Спец, Спец1, Спец2, Спец3, Спец4, Спец5. Пользователь на форме нажимает кнопку «Добавить», и система программно или визуально заполняет следующий по порядку реквизит. Это классическая архитектурная ошибка, нарушающая первую нормальную форму проектирования баз данных.
Давайте рассмотрим несколько вариантов решения этой задачи: от быстрого исправления «в лоб» на уровне запроса до фундаментального рефакторинга структуры данных.
Если у нас нет возможности прямо сейчас вносить изменения в структуру метаданных (база находится в продакшене, запрещено менять схему данных, сжатые сроки), мы можем решить задачу на уровне языка запросов 1С. Для этого нам потребуется перебрать все возможные реквизиты специальностей в секции отбора ГДЕ с помощью логического оператора ИЛИ.
Проанализируем следующий текст запроса:
ВЫБРАТЬ
КонтактнаяИнформация.Ссылка КАК Ссылка,
КонтактнаяИнформация.НомерТелефона КАК НомерТелефона
ИЗ
Справочник.Контрагенты.КонтактнаяИнформация КАК КонтактнаяИнформация
ГДЕ
НЕ КонтактнаяИнформация.Ссылка.ПометкаУдаления
И (КонтактнаяИнформация.Ссылка.Спец = &Спец
ИЛИ КонтактнаяИнформация.Ссылка.Спец1 = &Спец
ИЛИ КонтактнаяИнформация.Ссылка.Спец2 = &Спец
ИЛИ КонтактнаяИнформация.Ссылка.Спец3 = &Спец
ИЛИ КонтактнаяИнформация.Ссылка.Спец4 = &Спец
ИЛИ КонтактнаяИнформация.Ссылка.Спец5 = &Спец)
Этот запрос полностью решает задачу функционально: если нужная специальность окажется в любом из пяти дополнительных реквизитов, контрагент попадет в итоговую выборку. Однако у этого подхода есть критические недостатки:
ИЛИ в условиях соединения или отбора для разных полей практически всегда лишает СУБД возможности использовать индексы. СУБД будет вынуждена выполнять сканирование всей таблицы (Table Scan или Index Scan), что приведет к сильным зависаниям на больших объемах данных.Спец6 в конфигураторе и вручную переписывать все подобные запросы по всей конфигурации.Чтобы избежать проблем с производительностью СУБД при использовании оператора ИЛИ, мы можем прибегнуть к нормализации данных «на лету» внутри самого запроса. Для этого мы построим временную таблицу, в которую соберем все ссылки контрагентов, удовлетворяющие условиям по каждому реквизиту отдельно, а затем объединим их. Каждая отдельная ветка операции ОБЪЕДИНИТЬ будет эффективно использовать индексы по соответствующим полям.
Разберем этот запрос по шагам:
ВЫБРАТЬ Ссылка ПОМЕСТИТЬ ВТ_ОтфильтрованныеКонтрагенты ИЗ Справочник.Контрагенты ГДЕ Спец = &Спец И НЕ ПометкаУдаления
ОБЪЕДИНИТЬ
ВЫБРАТЬ Ссылка ИЗ Справочник.Контрагенты ГДЕ Спец1 = &Спец И НЕ ПометкаУдаления
ОБЪЕДИНИТЬ
ВЫБРАТЬ Ссылка ИЗ Справочник.Контрагенты ГДЕ Спец2 = &Спец И НЕ ПометкаУдаления
ОБЪЕДИНИТЬ
ВЫБРАТЬ Ссылка ИЗ Справочник.Контрагенты ГДЕ Спец3 = &Спец И НЕ ПометкаУдаления
ОБЪЕДИНИТЬ
ВЫБРАТЬ Ссылка ИЗ Справочник.Контрагенты ГДЕ Спец4 = &Спец И НЕ ПометкаУдаления
ОБЪЕДИНИТЬ
ВЫБРАТЬ Ссылка ИЗ Справочник.Контрагенты ГДЕ Спец5 = &Спец И НЕ ПометкаУдаления
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
КонтактнаяИнформация.Ссылка КАК Ссылка,
КонтактнаяИнформация.НомерТелефона КАК НомерТелефона
ИЗ
ВТ_ОтфильтрованныеКонтрагенты КАК ВТ_ОтфильтрованныеКонтрагенты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Контрагенты.КонтактнаяИнформация КАК КонтактнаяИнформация
ПО ВТ_ОтфильтрованныеКонтрагенты.Ссылка = КонтактнаяИнформация.Ссылка
Обратите внимание на важный нюанс: мы используем оператор ОБЪЕДИНИТЬ вместо ОБЪЕДИНИТЬ ВСЕ. Это сделано намеренно. Оператор ОБЪЕДИНИТЬ автоматически устраняет дублирующиеся строки в результирующей таблице. Если у одного контрагента одна и та же специальность по ошибке была записана и в поле Спец, и в поле Спец2, в таблице ВТ_ОтфильтрованныеКонтрагенты останется ровно одна уникальная ссылка на этого контрагента. Это предотвратит дублирование номеров телефонов в конечном отчете.
Хотя предыдущие варианты позволяют решить задачу программно, они являются лишь костылями для компенсации неверного проектирования метаданных. Единственный правильный и стратегически верный путь — провести рефакторинг конфигурации.
Для хранения множества однотипных значений (в данном случае специальностей контрагента) идеально подходит подчиненный объект — Табличная часть. Проанализируем, как это реализовать:
Контрагенты.Специальности.Специальность с типом ссылки на справочник специальностей.Спец, Спец1... в новую табличную часть с помощью одноразовой обработки заполнения, после чего удалим старые реквизиты из конфигурации.После проведения такого рефакторинга наш запрос станет изящным, простым и максимально быстродействующим:
ВЫБРАТЬ РАЗЛИЧНЫЕ
КонтактнаяИнформация.Ссылка КАК Ссылка,
КонтактнаяИнформация.НомерТелефона КАК НомерТелефона
ИЗ
Справочник.Контрагенты.Специальности КАК КонтрагентыСпециальности
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Контрагенты.КонтактнаяИнформация КАК КонтактнаяИнформация
ПО КонтрагентыСпециальности.Ссылка = КонтактнаяИнформация.Ссылка
ГДЕ
НЕ КонтактнаяИнформация.Ссылка.ПометкаУдаления
И КонтрагентыСпециальности.Специальность = &Спец
В данном запросе мы использовали ключевое слово РАЗЛИЧНЫЕ, так как у контрагента может быть привязано несколько телефонов в табличной части контактной информации или указано несколько схожих специальностей. Этот запрос легко масштабируется: у контрагента может быть хоть 10, хоть 100 специальностей, и база данных отработает этот поиск мгновенно благодаря встроенному индексированию реквизита Ссылка табличной части.
Если мы проектируем крупную информационную систему с миллионами контрагентов, то хранение специальностей в табличной части может замедлить объектную работу с самим справочником (так как при чтении контрагента платформа считывает все его табличные части). В этом случае целесообразно вынести эту информацию во внешний Регистр сведений.
Создадим независимый регистр сведений СпециальностиКонтрагентов со следующей структурой:
Контрагент (тип: СправочникСсылка.Контрагенты). Для этого измерения обязательно установим свойство «Индексировать».Специальность (тип: СправочникСсылка.Специальности).Запрос в этом случае будет обращаться к физической таблице регистра сведений, что обеспечит наивысшую скорость работы в СУБД:
ВЫБРАТЬ РАЗЛИЧНЫЕ
КонтактнаяИнформация.Ссылка КАК Ссылка,
КонтактнаяИнформация.НомерТелефона КАК НомерТелефона
ИЗ
РегистрСведений.СпециальностиКонтрагентов КАК СпециальностиКонтрагентов
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Контрагенты.КонтактнаяИнформация КАК КонтактнаяИнформация
ПО СпециальностиКонтрагентов.Контрагент = КонтактнаяИнформация.Ссылка
ГДЕ
НЕ КонтактнаяИнформация.Ссылка.ПометкаУдаления
И СпециальностиКонтрагентов.Специальность = &Спец
Использование регистра сведений также позволяет нам легко добавить периодичность (например, чтобы хранить историю изменения квалификации или специальностей сотрудника/контрагента во времени), что делает это решение наиболее гибким для сложных корпоративных систем.
Подведем итоги нашего разбора. Если вы столкнулись с архитектурным наследием в виде множества полей Спец1, Спец2, помните:
ОБЪЕДИНИТЬ (Вариант 2), так как он работает значительно быстрее, чем условия с ИЛИ.