Что лучше использовать в запросах 1С: ЛЕВОЕ или ВНУТРЕННЕЕ соединение при сопоставлении данных?

← На главную

При разработке алгоритмов загрузки данных (например, прайс-листов, накладных из внешних файлов или результатов обмена) перед разработчиком регулярно возникает задача: сопоставить загруженную таблицу значений со справочником в базе данных. Возникает вопрос: какое соединение выбрать — ЛЕВОЕ СОЕДИНЕНИЕ или ВНУТРЕННЕЕ СОЕДИНЕНИЕ, влияет ли на этот выбор разница в количестве строк между таблицами и как это отражается на скорости выполнения запроса в СУБД? Рассмотрим эту проблему подробно, разберем поведение оптимизатора и приведем рекомендации по проектированию запросов.

Бизнес-логика: целевой результат запроса

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

Когда требуется ЛЕВОЕ соединение: если загружаемый набор данных должен быть сохранен целиком, а отсутствующие в базе позиции необходимо подсветить пользователю для ручного сопоставления, отправить на нечеткий поиск или автоматически создать новые элементы справочника. При левом соединении исходная таблица загрузки сохраняет все свои строки, а ненайденные поля справочника заполняются значением NULL.
Когда требуется ВНУТРЕННЕЕ соединение: если по регламенту бизнес-процесса требуется обработать исключительно те строки, которые гарантированно присутствуют в информационной базе, а все неизвестные позиции должны быть сразу отсечены без вывода в результат.

Попытка использовать ЛЕВОЕ СОЕДИНЕНИЕ с последующей фильтрацией в секции ГДЕ Справочник.Ссылка ЕСТЬ НЕ NULL синтаксически превращает логику отбора во внутреннее соединение. Рассмотрим, как на такие конструкции реагируют СУБД.

Поведение оптимизаторов СУБД: MS SQL и PostgreSQL

Выясним причину, почему разработчики часто наблюдают одинаковое время выполнения при сравнении внутреннего соединения и левого соединения с фильтром по правой таблице. Современные оптимизаторы СУБД (в частности, Microsoft SQL Server) умеют анализировать предикаты. Если для левого соединения указано жесткое условие фильтрации по полям присоединяемой таблицы, оптимизатор выполняет внутреннее упрощение запроса и строит абсолютно одинаковый план выполнения (Execution Plan), сводя конструкцию к обычному INNER JOIN.

Однако полагаться на такое упрощение не следует по ряду причин:

Свобода выбора ведущей таблицы: при ВНУТРЕННЕМ СОЕДИНЕНИИ оптимизатор СУБД полностью свободен в выборе порядка соединения. На основе статистики он может выбрать ведущей (driving) меньшую таблицу и использовать наиболее эффективный физический оператор (Nested Loops, Hash Match или Merge Join). При ЛЕВОМ СОЕДИНЕНИИ порядок таблиц жестко зафиксирован: левая таблица обязана читаться первой, что при неудачном сочетании условий может привести к неоптимальному полному сканированию.
Поведение PostgreSQL: оптимизатор PostgreSQL при наличии сложных условий, конструкций ЕСТЬNULL или выражений во временных таблицах далеко не всегда выполняет трансляцию левого соединения во внутреннее, что приводит к избыточной материализации промежуточных данных и падению скорости.

Таким образом, если необходимо отобрать только совпавшие строки, следует сразу писать ВНУТРЕННЕЕ СОЕДИНЕНИЕ, не перегружая запрос искусственными конструкциями с ЕСТЬ НЕ NULL.

Оптимизация сопоставления Таблицы Значений и базы данных

Посмотрим на пример правильной архитектуры запроса при сопоставлении внешней ТаблицыЗначений со справочником. Основная потеря производительности на больших массивах данных (от десятков тысяч строк) происходит не из-за выбора типа JOIN, а из-за отсутствия индексов и неправильной типизации колонок входной таблицы.

Разберем по шагам ключевые правила подготовки данных:

Строгая типизация колонок ТЗ: перед заполнением таблицы значений обязательно задаем типы через ОписаниеТипов. Если тип колонки не определен, СУБД создаст временную таблицу с неограниченной длиной поля, по которому невозможно построить полноценный индекс.
Индексация временной таблицы: никогда не соединяйте физическую таблицу базы напрямую с переданным параметром &ТЗ. Поместите ее во временную таблицу в первом пакете запроса и укажите инструкцию ИНДЕКСИРОВАТЬ ПО.
Устранение дубликатов: если во входных данных или в базе возможны дубли по ключевому полю (артикулу, штрихкоду), прямое соединение приведет к декартову произведению строк. Входную таблицу необходимо предварительно свернуть или сгруппировать в запросе.

Посмотрим на пример запроса для сценария дополнения данных, когда нам нужно отобразить все строки файла и подтянуть ссылки из базы (используем ЛЕВОЕ СОЕДИНЕНИЕ):


// Входная таблица значений &ТЗ_Загрузка содержит типизированные колонки: Артикул, Количество, Цена
Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |    ДанныеФайла.Артикул КАК Артикул,
    |    ДанныеФайла.Количество КАК Количество,
    |    ДанныеФайла.Цена КАК Цена
    |ПОМЕСТИТЬ ВТ_Загрузка
    |ИЗ
    |    &ТЗ_Загрузка КАК ДанныеФайла
    |ИНДЕКСИРОВАТЬ ПО
    |    Артикул
    |;
    |
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ
    |    ВТ.Артикул КАК Артикул,
    |    ВТ.Количество КАК Количество,
    |    ВТ.Цена КАК Цена,
    |    СпрНоменклатура.Ссылка КАК НоменклатураСсылка,
    |    ВЫБОР
    |        КОГДА СпрНоменклатура.Ссылка ЕСТЬ NULL
    |            ТОГДА ИСТИНА
    |        ИНАЧЕ ЛОЖЬ
    |    КОНЕЦ КАК ЭтоНоваяПозиция
    |ИЗ
    |    ВТ_Загрузка КАК ВТ
    |        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК СпрНоменклатура
    |        ПО ВТ.Артикул = СпрНоменклатура.Артикул
    |            И (НЕ СпрНоменклатура.ЭтоГруппа)";

Запрос.УстановитьПараметр("ТЗ_Загрузка", ТаблицаЗагрузки);
Результат = Запрос.Выполнить().Выгрузить();

Работа с большими объемами данных и получение уникальных значений

Проанализируем ситуацию, когда объем загружаемого файла составляет 100 000 – 200 000 строк. Передача такого массива в качестве параметра приводит к сильной нагрузке на память рабочих процессов сервера 1С и базу tempdb. В таких сценариях рекомендуется применять пакетную обработку (порции по 5 000 – 10 000 строк в цикле).

Если перед выполнением сопоставления необходимо получить список уникальных ключей из колонки таблицы значений без полной выгрузки или метода Свернуть() (чтобы не дублировать память и не изменять исходную таблицу), рассмотрим два эффективных способа:

Через объект Соответствие: алгоритмическая сложность поиска и вставки составляет O(1). Обойдем строки таблицы значений и запишем элементы как ключи.


УникальныеКлючи = Новый Соответствие;
Для Каждого Стр Из ТаблицаЗагрузки Цикл
    УникальныеКлючи.Вставить(Стр.Артикул, Истина);
КонецЦикла;

Через запрос с ключевым словом РАЗЛИЧНЫЕ: если объем данных велик, группировка силами СУБД с помещением во временную таблицу работает значительно быстрее манипуляций в оперативной памяти встроенного языка:


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ РАЗЛИЧНЫЕ
    |    ТЗ.Артикул КАК Артикул
    |ИЗ
    |    &ТЗ КАК ТЗ";
Запрос.УстановитьПараметр("ТЗ", ТаблицаЗагрузки);
МассивУникальных = Запрос.Выполнить().Выгрузить().ВыгрузитьКолонку("Артикул");

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