При работе с механизмами запросов в платформе 1С:Предприятие разработчики часто сталкиваются с необходимостью объединения нескольких выборок. Одной из типичных задач является сохранение строгой последовательности вывода: сначала должны идти записи из первого запроса, а затем — из второго.
По умолчанию СУБД возвращает объединенные данные без гарантированного порядка, либо сортирует их по внутреннему представлению (например, по ссылке или первичному ключу). Попытки решить эту проблему интуитивными методами часто приводят к неэффективной работе базы данных. В этой статье мы подробно разберем, как правильно управлять порядком строк в объединенных запросах, а также рассмотрим критические аспекты оптимизации производительности таких выборок.
Прежде чем перейти к практическому решению, разберем важный теоретический аспект. В реляционных базах данных любой промежуточный или финальный результат запроса рассматривается как математическое множество. По определению, элементы множества не имеют порядка.
Даже если вы используете конструкцию ОБЪЕДИНИТЬ ВСЕ, и при тестировании на файловой базе данных строки отображаются ровно в том порядке, в котором они были описаны в конфигураторе, полагаться на это в реальных задачах нельзя. Поведение СУБД (будь то MS SQL Server, PostgreSQL или IBM DB2) на клиент-серверном варианте работы может измениться в любой момент. На порядок вывода без явной инструкции сортировки влияют:
Единственным надежным способом заставить СУБД вывести строки в строго определенной последовательности является явное использование секции УПОРЯДОЧИТЬ ПО в самом конце запроса.
Для того чтобы гарантировать вывод результатов сначала первого запроса, а затем второго, мы должны добавить в каждую схему выборки статичное (вычисляемое) поле, которое будет служить маркером приоритета. Назовем его, например, Порядок.
В первом запросе мы жестко пропишем числовое значение 1 КАК Порядок, а во втором запросе — 2 КАК Порядок (или просто литерал 2, так как имена полей объединенного запроса определяются по первому запросу). В конце всей конструкции мы применим сортировку по этому новому полю.
Давайте посмотрим, как выглядит такое решение на практике:
ВЫБРАТЬ
Номенклатура.Ссылка КАК Ссылка,
Номенклатура.Наименование КАК Наименование,
1 КАК Порядок
ИЗ
Справочник.Номенклатура КАК Номенклатура
ГДЕ
Номенклатура.Наименование ПОДОБНО &Наименование + "%"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Номенклатура.Ссылка,
Номенклатура.Наименование,
2
ИЗ
Справочник.Номенклатура КАК Номенклатура
ГДЕ
НЕ Номенклатура.Ссылка В
(ВЫБРАТЬ
Номенклатура.Ссылка КАК Ссылка
ИЗ
Справочник.Номенклатура КАК Номенклатура
ГДЕ
Номенклатура.Наименование ПОДОБНО &Наименование + "%")
И Номенклатура.Наименование ПОДОБНО "%" + &Наименование + "%"
УПОРЯДОЧИТЬ ПО
Порядок,
Наименование
Разберем, что изменилось в этом коде по сравнению с исходным вариантом автора темы:
1, для второй — 2.УПОРЯДОЧИТЬ ПО первым критерием указано поле Порядок. Это гарантирует, что все строки из первой выборки (у которых значение равно 1) гарантированно окажутся выше строк из второй выборки (у которых значение равно 2).Наименование. Это позволяет отсортировать элементы по алфавиту внутри каждой из двух групп.Обратите внимание на замену ключевого слова ОБЪЕДИНИТЬ на ОБЪЕДИНИТЬ ВСЕ. Это важнейшая рекомендация по повышению производительности систем на платформе 1С:Предприятие.
Когда вы пишете просто ОБЪЕДИНИТЬ, СУБД пытается найти и удалить абсолютно одинаковые строки в результирующем наборе данных (выполняется неявная операция DISTINCT). Для этого сервер базы данных вынужден производить ресурсоемкие операции сортировки и сравнения во временной памяти (операции типа Hash Match или Merge Join). Это драматически снижает скорость выполнения запроса, особенно на больших таблицах.
Когда вы используете ОБЪЕДИНИТЬ ВСЕ, СУБД просто последовательно склеивает результаты двух выборок без анализа на дубликаты. Это происходит практически мгновенно. Поскольку в нашем случае первая и вторая части логически исключают друг друга (во второй части стоит явное отрицание попадания элементов в первую часть), дубликатов быть не может физически. Следовательно, использование обычной конструкции без приставки "ВСЕ" здесь не только вредно для скорости, но и бессмысленно.
Хотя предложенное решение с полем Порядок полностью закрывает вопрос сортировки, исходный текст запроса содержит крайне опасные с точки зрения производительности конструкции. На реальной базе данных объемом в несколько сотен тысяч элементов этот запрос вызовет серьезные зависания системы. Давайте проанализируем эти проблемы и устраним их.
Использование оператора НЕ ... В (ВЫБРАТЬ ...) — это классический антипаттерн разработки в 1С. Оптимизаторы СУБД очень часто не могут эффективно построить план для таких условий. Вместо быстрого сопоставления они переводят этот подзапрос в коррелированный (выполняющийся заново для каждой строки основной таблицы) или прибегают к полному сканированию таблицы.
Решение: Заменить отрицание в подзапросе на ЛЕВОЕ СОЕДИНЕНИЕ с проверкой на NULL.
Конструкция ПОДОБНО "%" + &Наименование + "%" выполняет поиск подстроки в середине или конце поля. СУБД физически не может использовать индекс по наименованию, если шаблон поиска начинается с символа % (так называемая ведущая маска). СУБД будет вынуждена выполнить операцию Table Scan или Index Scan, то есть прочитать абсолютно все записи справочника с диска в оперативную память и посимвольно сравнить каждую строку. На больших базах это приведет к полному коллапсу производительности.
Решение: Для полноценного и быстрого поиска по подстроке следует использовать встроенный в 1С Полнотекстовый поиск (ПСП). Если же бизнес-логика требует реализации именно через язык запросов, необходимо минимизировать нагрузку за счет использования Временных Таблиц.
Давайте перепишем исходный алгоритм в соответствии со стандартами разработки 1С. Мы разобьем сложную задачу на последовательные простые шаги, используя менеджер временных таблиц. Это позволит СУБД построить идеальный план выполнения для каждого шага.
Разберем этот оптимальный подход по шагам:
// Шаг 1: Выбираем элементы с прямым совпадением (начало строки) во временную таблицу.
// Этот запрос отработает мгновенно, так как СУБД использует индекс по Наименованию.
ВЫБРАТЬ
Номенклатура.Ссылка КАК Ссылка,
Номенклатура.Наименование КАК Наименование,
1 КАК Порядок
ПОМЕСТИТЬ ВТ_ПрямоеСовпадение
ИЗ
Справочник.Номенклатура КАК Номенклатура
ГДЕ
Номенклатура.Наименование ПОДОБНО &Наименование + "%"
ИНДЕКСИРОВАТЬ ПО
Ссылка
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Выбираем элементы с вхождением в середину строки,
// исключая те, которые мы уже нашли на первом шаге, с помощью ЛЕВОГО СОЕДИНЕНИЯ.
ВЫБРАТЬ
Номенклатура.Ссылка КАК Ссылка,
Номенклатура.Наименование КАК Наименование,
2 КАК Порядок
ПОМЕСТИТЬ ВТ_НепрямоеСовпадение
ИЗ
Справочник.Номенклатура КАК Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПрямоеСовпадение КАК ВТ_ПрямоеСовпадение
ПО Номенклатура.Ссылка = ВТ_ПрямоеСовпадение.Ссылка
ГДЕ
ВТ_ПрямоеСовпадение.Ссылка ЕСТЬ NULL
И Номенклатура.Наименование ПОДОБНО "%" + &Наименование + "%"
ИНДЕКСИРОВАТЬ ПО
Ссылка
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Объединяем результаты через высокопроизводительный ОБЪЕДИНИТЬ ВСЕ
// и накладываем финальное упорядочивание.
ВЫБРАТЬ
ВТ_ПрямоеСовпадение.Ссылка КАК Ссылка,
ВТ_ПрямоеСовпадение.Наименование КАК Наименование,
ВТ_ПрямоеСовпадение.Порядок КАК Порядок
ИЗ
ВТ_ПрямоеСовпадение КАК ВТ_ПрямоеСовпадение
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ВТ_НепрямоеСовпадение.Ссылка,
ВТ_НепрямоеСовпадение.Наименование,
ВТ_НепрямоеСовпадение.Порядок
ИЗ
ВТ_НепрямоеСовпадение КАК ВТ_НепрямоеСовпадение
УПОРЯДОЧИТЬ ПО
Порядок,
Наименование
Порядок гарантирует разделение результатов на две группы (сначала точные совпадения, затем частичные вхождения). Внутри каждой группы записи аккуратно упорядочены по алфавиту.ОБЪЕДИНИТЬ ВСЕ избавило СУБД от необходимости выполнять уникализацию строк.НЕ В (ВЫБРАТЬ...) заменена на эффективное ЛЕВОЕ СОЕДИНЕНИЕ по проиндексированному полю временной таблицы. СУБД сопоставит данные за доли миллисекунды.ВТ_ПрямоеСовпадение и ВТ_НепрямоеСовпадение в консоли запросов.Применяйте данный подход всегда, когда вам требуется сформировать сложную, логически разделенную выдачу данных с гарантированным приоритетом определенных групп записей.