Разработчики в среде 1С:Предприятие часто сталкиваются с задачей объединения разнородных данных. Типичный пример: у нас есть две таблицы складов (Таб1 и Таб2), и нам необходимо получить результирующую таблицу Таб3. При этом должны выполняться следующие жесткие требования к структуре и порядку данных:
Таб1).Таб2).На первый взгляд задача кажется тривиальной, однако при реализации стандартного объединения через ОБЪЕДИНИТЬ или при использовании ВЫБРАТЬ РАЗЛИЧНЫЕ разработчики натыкаются на системные ограничения СУБД и платформы 1С. В этой статье мы подробно разберем, почему возникают эти сложности, рассмотрим несколько вариантов решения и выделим наиболее оптимальный с точки зрения производительности.
Попытка решить задачу «в лоб» часто приводит к ошибке СУБД. Разработчик пытается использовать конструкцию ВЫБРАТЬ РАЗЛИЧНЫЕ, чтобы избавиться от дублей, и одновременно применить УПОРЯДОЧИТЬ ПО для поля «Индекс» (или приоритета), которое помогает поднять записи первой таблицы наверх.
Выясним причину этой проблемы. СУБД обрабатывает запрос в строго определенной последовательности. Сначала происходит выборка данных и отсечение дубликатов (операция DISTINCT). На этом этапе СУБД группирует строки по полям, указанным в секции ВЫБРАТЬ. Если вы пытаетесь отсортировать результат по полю, которое отсутствует в выборке (например, по техническому индексу сортировки), СУБД выдает ошибку. Система «не понимает», по какому именно значению индекса нужно сортировать свернутую строку, если теоретически для одного склада в исходных таблицах могло быть несколько разных индексов. Включение же индекса в саму выборку приводит к тому, что ВЫБРАТЬ РАЗЛИЧНЫЕ перестает работать, так как строки с одинаковыми складами, но разными индексами СУБД считает уникальными.
Рассмотрим, как обойти это ограничение и построить производительный запрос.
Этот вариант часто приходит на ум начинающим разработчикам. Склады из первой таблицы выбираются напрямую с приоритетом «0», а склады из второй таблицы выбираются с приоритетом «1», но фильтруются с помощью подзапроса через конструкцию НЕ В.
Посмотрим на пример такого запроса:
ВЫБРАТЬ
0 КАК Приоритет,
т1.Склад КАК Склад
ИЗ
ВременнаяТаблица1 КАК т1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
1,
т2.Склад
ИЗ
ВременнаяТаблица2 КАК т2
ГДЕ
т2.Склад НЕ В (ВЫБРАТЬ ВтСклады.Склад ИЗ ВременнаяТаблица1 КАК ВтСклады)
УПОРЯДОЧИТЬ ПО Приоритет
Почему этот подход не рекомендуется использовать на реальных базах данных?
Проанализируем ситуацию с точки зрения производительности СУБД (MS SQL Server, PostgreSQL). Использование оператора НЕ В (или NOT IN в терминах SQL) имеет два критических недостатка:
ВЫБРАТЬ ВтСклады.Склад... окажется хотя бы одно значение NULL, весь оператор НЕ В вернет пустой результат. СУБД просто не выдаст ни одной строки из второй таблицы. Это связано с особенностями трехзначной логики SQL (True, False, Unknown).НЕ В. Вместо быстрого поиска (Index Seek) СУБД вынуждена выполнять полное сканирование таблиц (Table Scan / Index Scan), что приводит к колоссальному падению производительности при росте объемов данных.Еще один вариант, который часто предлагают на форумах благодаря его компактности — использование ПОЛНОГО СОЕДИНЕНИЯ по полю Склад с последующим выводом значений через функцию ЕСТЬNULL.
Рассмотрим пример реализации:
ВЫБРАТЬ
ЕСТЬNULL(Таб1.Склад, Таб2.Склад) КАК Склад
ИЗ
ВременнаяТаблица1 КАК Таб1
ПОЛНОЕ СОЕДИНЕНИЕ ВременнаяТаблица2 КАК Таб2
ПО Таб1.Склад = Таб2.Склад
УПОРЯДОЧИТЬ ПО
ЕСТЬNULL(Таб1.Индекс, Таб2.Индекс)
Несмотря на внешнее изящество, в реальных высоконагруженных системах 1С такое решение является серьезным антипаттерном производительности. Операция ПОЛНОЕ СОЕДИНЕНИЕ (Full Outer Join) требует от СУБД выполнения тяжелых алгоритмов сопоставления данных (обычно Hash Match или Merge Join по всей выборке). Это создает предельную нагрузку на дисковую подсистему и процессор сервера. Кроме того, при высокой параллельной работе пользователей такие запросы часто приводят к эскалации блокировок таблиц.
Для того чтобы обойти ограничение СУБД на сортировку уникальных записей и обеспечить максимальное быстродействие, мы должны разделить логику получения уникальных складов и логику их сортировки. Сначала мы соберем уникальный список во вложенном запросе, а затем во внешнем запросе присвоим им индексы приоритета и отсортируем.
Для исключения дублей мы откажемся от медленного НЕ В в пользу высокопроизводительной связки ЛЕВОЕ СОЕДИНЕНИЕ ... ГДЕ ... ЕСТЬ NULL. Оптимизатор СУБД транслирует такую связку в логический оператор Left Anti Semi Join, который выполняется мгновенно даже на миллионах строк.
Разберем по шагам готовый шаблон оптимального запроса:
ВЫБРАТЬ
Т.Склад КАК Склад
ИЗ
(
// Выбираем уникальные склады из первой таблицы
ВЫБРАТЬ РАЗЛИЧНЫЕ
Таб1.Склад КАК Склад,
1 КАК Приоритет
ИЗ
ВременнаяТаблица1 КАК Таб1
ОБЪЕДИНИТЬ ВСЕ
// Выбираем уникальные склады из второй таблицы, которых НЕТ в первой
ВЫБРАТЬ РАЗЛИЧНЫЕ
Таб2.Склад КАК Склад,
2 КАК Приоритет
ИЗ
ВременнаяТаблица2 КАК Таб2
ЛЕВОЕ СОЕДИНЕНИЕ ВременнаяТаблица1 КАК Таб1
ПО Таб2.Склад = Таб1.Склад
ГДЕ
Таб1.Склад ЕСТЬ NULL
) КАК Т
УПОРЯДОЧИТЬ ПО
Т.Приоритет
Преимущества данного подхода:
Приоритет создается искусственно внутри вложенного запроса. Первая таблица гарантированно получает приоритет «1», вторая — «2».Приоритет присутствует в выборке вложенного запроса, поэтому сортировка во внешнем запросе отрабатывает без ошибок.ЛЕВОЕ СОЕДИНЕНИЕ ... ЕСТЬ NULL эффективно использует индексы СУБД и корректно обрабатывает возможные значения NULL.Если исходные таблицы ВременнаяТаблица1 и ВременнаяТаблица2 создаются вами динамически в рамках одного пакета запросов, критически важно их проиндексировать. Без создания индексов СУБД будет делать вложенные циклы (Nested Loops) для сопоставления данных, что нивелирует всю оптимизацию.
Обязательно добавляйте инструкцию индексации при создании временных таблиц:
// Пример создания первой временной таблицы с индексированием
ВЫБРАТЬ
Склады.Ссылка КАК Склад
ПОМЕСТИТЬ ВременнаяТаблица1
ИЗ
Справочник.Склады КАК Склады
ИНДЕКСИРОВАТЬ ПО
Склад
;
Наличие индекса по полю Склад позволит СУБД мгновенно сопоставить две таблицы в левом соединении через алгоритм Index Seek.
Иногда задача объединения таблиц возникает не в запросе к базе данных, а в оперативной памяти (например, при обработке внешних файлов или результатов различных API-запросов). В этом случае использовать менеджер запросов для объединения таблиц значений не всегда целесообразно.
Рассмотрим, как решить эту задачу на встроенном языке 1С с максимальной скоростью, избегая медленного построчного обхода в двойном цикле.
Для быстрой проверки уникальности мы используем объект Соответствие, так как поиск по ключу в соответствии работает со скоростью O(1), в отличие от метода Найти() таблицы значений, который выполняет линейный перебор строк.
// Создаем соответствие для быстрой проверки уникальности складов
СкладыСоответствие = Новый Соответствие;
// Таб1 и Таб2 - наши исходные таблицы значений с колонкой "Склад"
// Таб3 - результирующая таблица
Таб3 = Таб1.СкопироватьКолонки();
// Шаг 1. Переносим все склады из Таб1 в результирующую таблицу
Для Каждого СтрокаТ1 Из Таб1 Цикл
НоваяСтрока = Таб3.Добавить();
ЗаполнитьЗначенияСвойств(НоваяСтрока, СтрокаТ1);
// Запоминаем склад, чтобы исключить дубли
СкладыСоответствие.Вставить(СтрокаТ1.Склад, Истина);
КонецЦикла;
// Шаг 2. Переносим склады из Таб2, которых еще нет в Таб3
Для Каждого СтрокаТ2 Из Таб2 Цикл
Если СкладыСоответствие[СтрокаТ2.Склад] = Неопределено Тогда
НоваяСтрока = Таб3.Добавить();
ЗаполнитьЗначенияСвойств(НоваяСтрока, СтрокаТ2);
// Добавляем в соответствие, чтобы избежать дублей внутри самой Таб2
СкладыСоответствие.Вставить(СтрокаТ2.Склад, Истина);
КонецЕсли;
КонецЦикла;
Использование объекта Соответствие позволяет обработать десятки тысяч строк в оперативной памяти за доли секунды, сохраняя при этом строгое позиционирование элементов первой таблицы вверху списка.
Подведем итог: при работе с базой данных наилучшим решением является использование вложенного запроса с конструкцией ЛЕВОЕ СОЕДИНЕНИЕ ... ЕСТЬ NULL и явным индексированием временных таблиц. При работе в оперативной памяти оптимально использовать коллекции ТаблицаЗначений в связке с быстрым поиском через Соответствие.