При разработке механизмов загрузки, синхронизации номенклатуры и пакетного обновления данных часто возникает типовая задача: сравнить входящий массив данных с существующими записями базы данных и отобрать только те строки, которые нужно записать. При этом требуется отобрать как абсолютно новые позиции (которых еще нет в базе), так и позиции, у которых изменилось значение реквизита.
Нередко разработчики пытаются решить эту задачу интуитивно через внутреннее соединение с условиями вида Свойство <> Свойство или помещением сложных логических связок в условие соединения ПО, что приводит либо к получению пустой выборки, либо к декартову произведению строк. Разберем по шагам, почему возникают такие ошибки и как правильно построить эффективный запрос.
Представим, что у нас есть таблица входящих данных Т1 (номенклатура, свойство, значение) и табличная часть базы данных Т2 (дополнительные реквизиты). Нам необходимо выбрать строки из Т1, если:
Типичная ошибка заключается в попытке наложить условие неравенства непосредственно в блоке ПО при внутреннем или левом соединении:
// Ошибочный подход
ПО Т1.Номенклатура = Т2.Ссылка
И Т1.Свойство <> Т2.Свойство
И Т1.Значение <> Т2.Значение
Проанализируем ситуацию: если у товара в базе есть несколько свойств (например, «Цвет» и «Размер»), то при проверке входящей строки с «Цветом» условие Т1.Свойство <> Т2.Свойство сработает для строки базы с «Размером». В результате строка свяжется с чужим свойством, произойдет неконтролируемое размножение строк, а логика проверки будет полностью нарушена.
Второе распространенное заблуждение — считать, что в секции ГДЕ при ЛЕВОМ СОЕДИНЕНИИ нельзя обращаться к полям правой таблицы. Секция ГДЕ фильтрует уже сформированный результат соединения. Однако важно помнить: если запись в правой таблице отсутствует, все ее поля содержат NULL. Любое прямое сравнение с NULL (например, Т1.Значение <> Т2.Значение) в языке SQL возвращает Ложь, из-за чего новая строка отбрасывается фильтром.
Самый чистый, надежный и быстродействующий способ — связать таблицы по всем ключевым полям совпадения, включая значение, а затем отсечь найденные строки в секции ГДЕ с помощью проверки на NULL.
Посмотрим на пример такого запроса:
ВЫБРАТЬ
Т1.Номенклатура КАК Номенклатура,
Т1.Свойство КАК Свойство,
Т1.Значение КАК Значение
ИЗ
ВременнаяТаблицаНоменклатуры КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура.ДополнительныеРеквизиты КАК Т2
ПО Т1.Номенклатура = Т2.Ссылка
И Т1.Свойство = Т2.Свойство
И Т1.Значение = Т2.Значение
ГДЕ
Т2.Ссылка ЕСТЬ NULL
Разберем логику работы этого запроса:
= для всех трех полей: объект, свойство и его значение.Т2.Ссылка появляется ссылка на элемент.Т2 заполнятся значением NULL.ГДЕ Т2.Ссылка ЕСТЬ NULL оставляет в итоговой выборке только те строки, у которых не нашлось полного совпадения в базе данных. Это исключает необходимость подбирать фиктивные константы для замены NULL.Если необходимо явно разделить логику «свойства нет» и «значение изменилось», соединение накладывается только на пару ключевых полей (Номенклатура, Свойство), а проверка различий значений переносится в фильтр с использованием функции ЕСТЬNULL.
Рассмотрим реализацию этого варианта:
ВЫБРАТЬ
Т1.Номенклатура КАК Номенклатура,
Т1.Свойство КАК Свойство,
Т1.Значение КАК Значение
ИЗ
ВременнаяТаблицаНоменклатуры КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура.ДополнительныеРеквизиты КАК Т2
ПО Т1.Номенклатура = Т2.Ссылка
И Т1.Свойство = Т2.Свойство
ГДЕ
Т1.Значение <> ЕСТЬNULL(Т2.Значение, &ЗаведомоНесуществующееЗначение)
Выясним причину работы этого условия: если в базе свойства нет, функция ЕСТЬNULL подставляет фиктивное значение параметра, которое гарантированно не совпадает с реальным входящим значением, и условие неравенства возвращает Истина. Если же свойство найдено, сравниваются реальные значения полей. Главный минус подхода — необходимость передачи специального значения параметра, тип которого должен совпадать с типом поля.
Одной из скрытых причин, по которой запросы сравнения возвращают неверные данные (например, выбирают вообще все строки или не находят ни одной), является несовпадение типов данных.
В типовых конфигурациях 1С поле Значение табличной части ДополнительныеРеквизиты имеет составной тип (характеристика), включающий ссылки на различные справочники (ЗначенияСвойствОбъектов) и примитивные типы (строка, число, булево). В СУБД такое поле хранится в виде нескольких колонок (тип, вид, строковое представление, бинарный идентификатор ссылки).
Если во временной таблице входящее значение хранится как обычная строка, а в базе данных — как ссылка или строка другой длины, прямое сравнение через = или <> всегда будет возвращать неверный результат из-за несовпадения типов.
Для корректного сопоставления применяем приведение типов с помощью функции ВЫРАЗИТЬ:
ВЫБРАТЬ
Т1.Номенклатура КАК Номенклатура,
Т1.Свойство КАК Свойство,
Т1.Значение КАК Значение
ИЗ
ВременнаяТаблицаНоменклатуры КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура.ДополнительныеРеквизиты КАК Т2
ПО Т1.Номенклатура = Т2.Ссылка
И Т1.Свойство = Т2.Свойство
И Т1.Значение = ВЫРАЗИТЬ(Т2.Значение КАК Строка(150))
ГДЕ
Т2.Ссылка ЕСТЬ NULL
При формировании временной таблицы из таблицы значений обязательно задаем жесткую типизацию колонок с помощью квалификаторов типов. Использование строк неограниченной длины в соединениях запрещено платформой и исключает использование индексов СУБД.
Когда обработка оперирует десятками и сотнями тысяч строк, соблюдаем правила оптимизации запросов и записи объектов:
ИНДЕКСИРОВАТЬ ПО Номенклатура, Свойство. Без индексов СУБД выполняет полное сканирование таблиц, что многократно замедляет обработку пакета.ОбменДанными.Загрузка = Истина. Это отключает подписки на события и второстепенные валидации, ускоряя сохранение в несколько раз:
НоменклатураОбъект = СтрокаВыборки.Номенклатура.ПолучитьОбъект();
НоменклатураОбъект.ОбменДанными.Загрузка = Истина;
// Заполнение табличной части...
НоменклатураОбъект.Записать();
НачатьТранзакцию() и ЗафиксироватьТранзакцию().