При работе с прямыми SQL-запросами в среде 1С:Предприятие 7.7 (с использованием внешней компоненты 1С++) разработчики часто сталкиваются с проблемами производительности и некорректной фильтрации данных при соединении таблиц регистров и справочников. Особенно остро этот вопрос встает, когда требуется получить данные по продажам в разрезе иерархии номенклатуры.
В этой статье мы подробно разберем, почему популярный, но тяжеловесный FULL OUTER JOIN часто оказывается избыточным, почему простой переход на LEFT JOIN может приводить к пустым результатам выборки, и как правильно реализовать аналог отбора «В ИЕРАРХИИ» на уровне базы данных MS SQL Server.
Рассмотрим исходный SQL-запрос, который вызывал затруднения у автора темы:
SELECT Номенклатура.ID [Ссылка $Справочник.Номенклатура]
FROM $Регистр.Продажи AS Продажи With (NOLOCK)
FULL OUTER JOIN $Справочник.Номенклатура AS Номенклатура With (NOLOCK) ON $Продажи.Номенклатура = Номенклатура.ID
WHERE (Номенклатура.PARENTID = :ТекФильтр)
GROUP BY Номенклатура.ID
В данном запросе разработчик пытается решить следующую задачу: получить все товары из определенной группы справочника «Номенклатура», по которым были движения в регистре «Продажи». Однако использование FULL OUTER JOIN здесь не только снижает производительность, но и логически ломает логику SQL-оптимизатора.
Проанализируем поведение СУБД при обработке этого запроса. В секции WHERE прописано строгое условие фильтрации:
Номенклатура.PARENTID = :ТекФильтр
В реляционных базах данных любое строгое условие фильтрации по правой (или левой) таблице во внешнем соединении, если оно явно не обрабатывает значение NULL (например, через конструкцию OR Номенклатура.PARENTID IS NULL), **неявно преобразует внешний JOIN в обычный INNER JOIN**.
SQL-сервер сначала соединяет таблицы по указанному условию связи, а затем отсекает все строки, где Номенклатура.PARENTID равен NULL (то есть те продажи из регистра, для которых не нашлось соответствия в справочнике номенклатуры, либо те элементы справочника, которые не попали в отбор). В результате тяжелая операция полного внешнего объединения выполняется вхолостую, превращаясь по сути во внутреннее соединение.
Когда автор темы попытался заменить FULL OUTER JOIN на LEFT JOIN, оставив таблицу Продажи ведущей (слева), запрос также фактически отработал как INNER JOIN по той же самой причине. И если в результате выполнения вернулась пустая таблица, это означает одно из двух:
:ТекФильтр.В СУБД-версии 1С 7.7 поле PARENTID в таблицах справочников хранит идентификатор только непосредственного родителя (на один уровень выше).
Если ваш справочник имеет сложную многоуровневую структуру (например: Бытовая техника -> Телевизоры -> Smart-TV -> Товар), то при передаче в параметр :ТекФильтр идентификатора группы «Бытовая техника» простой SQL-запрос с условием PARENTID = :ТекФильтр **не найдет** товары, лежащие внутри папки «Smart-TV». Он выберет только те элементы, которые расположены непосредственно внутри корня группы «Бытовая техника».
Рассмотрим три эффективных способа решения этой проблемы в зависимости от ваших задач.
Если вам гарантированно нужно работать только с первым уровнем вложенности справочника, лучшим решением будет поменять таблицы местами (сделать справочник ведущей таблицей) и использовать LEFT JOIN к таблице регистра, либо перенести условие фильтрации непосредственно в блок связи ON.
Посмотрим на пример запроса с изменением направления связи:
SELECT
Номенклатура.ID [Ссылка $Справочник.Номенклатура]
FROM
$Справочник.Номенклатура AS Номенклатура WITH (NOLOCK)
LEFT JOIN
$Регистр.Продажи AS Продажи WITH (NOLOCK) ON $Продажи.Номенклатура = Номенклатура.ID
WHERE
Номенклатура.PARENTID = :ТекФильтр
GROUP BY
Номенклатура.ID
В этом случае мы сначала выбираем все элементы справочника, принадлежащие конкретному родителю, а уже затем сопоставляем их с продажами. Если нам нужно жестко ограничить выборку только теми товарами, по которым продажи реально были, мы просто меняем LEFT JOIN на INNER JOIN.
Для полноценной работы с многоуровневой иерархией в 1С++ предусмотрен крайне эффективный штатный механизм. Метод УложитьСписокОбъектов позволяет рекурсивно собрать всю ветку подчиненных элементов на клиенте и передать их на сервер во временную таблицу базы данных.
Разберем по шагам реализацию этого механизма в программном коде 1С:
// 1. Создаем объект класса "Запрос" из ВК 1С++
Запрос = СоздатьОбъект("ODBCRecordset");
// 2. Укладываем всю иерархию выбранной группы во временную таблицу базы данных
// Третий параметр "Номенклатура" указывает тип объектов метаданных для правильного парсинга ID
Запрос.УложитьСписокОбъектов(ТекФильтр, "#СписокНоменклатуры", "Номенклатура");
После этого мы можем написать лаконичный, быстрый и абсолютно корректный SQL-запрос, который использует подготовленную временную таблицу #СписокНоменклатуры:
SELECT
Номенклатура.ID [Ссылка $Справочник.Номенклатура]
FROM
$Регистр.Продажи AS Продажи WITH (NOLOCK)
INNER JOIN
$Справочник.Номенклатура AS Номенклатура WITH (NOLOCK) ON $Продажи.Номенклатура = Номенклатура.ID
WHERE
Номенклатура.ID IN (SELECT Val FROM #СписокНоменклатуры)
GROUP BY
Номенклатура.ID
Этот подход отрабатывает максимально оперативно, так как СУБД осуществляет поиск по индексированному полю первичного ключа во временной таблице.
Если база данных развернута на сервере MS SQL Server 2005 и выше, вы можете полностью отказаться от подготовки данных на клиенте 1С и переложить построение дерева иерархии на мощь самого SQL-сервера с помощью рекурсивных обобщенных табличных выражений (Common Table Expressions - CTE):
WITH Hierarchy AS (
-- Якорь рекурсии: выбираем корневую группу-фильтр
SELECT ID
FROM $Справочник.Номенклатура WITH (NOLOCK)
WHERE ID = :ТекФильтр
UNION ALL
-- Рекурсивная часть: выбираем всех потомков на всех последующих уровнях
SELECT Child.ID
FROM $Справочник.Номенклатура AS Child WITH (NOLOCK)
INNER JOIN Hierarchy AS Parent ON Child.PARENTID = Parent.ID
)
SELECT
Номенклатура.ID [Ссылка $Справочник.Номенклатура]
FROM
$Регистр.Продажи AS Продажи WITH (NOLOCK)
INNER JOIN
$Справочник.Номенклатура AS Номенклатура WITH (NOLOCK) ON $Продажи.Номенклатура = Номенклатура.ID
WHERE
Номенклатура.ID IN (SELECT ID FROM Hierarchy)
GROUP BY
Номенклатура.ID
Такой запрос полностью автономен и выполнится непосредственно на сервере СУБД за минимальное количество чтений страниц данных.
В исходном запросе используется связка JOIN с последующей группировкой GROUP BY Номенклатура.ID. Если единственная цель запроса — получить уникальный список проданных товаров без агрегатных функций (сумм, количеств), то группировка по всей таблице регистра продаж является крайне неоптимальной операцией.
СУБД вынуждена будет просканировать все подходящие под фильтр записи регистра, соединить их со справочником, а затем выполнить тяжелую операцию сортировки и схлопывания дублей.
Эффективнее применить оператор EXISTS. В этом случае SQL Server прекратит чтение записей в регистре для каждого конкретного товара из отбора, как только найдет хотя бы одну строку его продажи. Это в разы ускоряет работу запроса на больших базах данных:
SELECT
Номенклатура.ID [Ссылка $Справочник.Номенклатура]
FROM
$Справочник.Номенклатура AS Номенклатура WITH (NOLOCK)
WHERE
Номенклатура.ID IN (SELECT Val FROM #СписокНоменклатуры)
AND EXISTS (
SELECT 1
FROM $Регистр.Продажи AS Продажи WITH (NOLOCK)
WHERE $Продажи.Номенклатура = Номенклатура.ID
)
Применив описанные методики, мы полностью избавляемся от ресурсоемких соединений типа FULL OUTER JOIN, корректно обрабатываем иерархическую структуру справочников 1С 7.7 и существенно снижаем нагрузку на дисковую подсистему сервера баз данных.