При разработке отчетов и сложных выборок данных в 1С мы часто сталкиваемся с необходимостью реализации необязательных фильтров. Классическая ситуация: если параметр задан, мы фильтруем по нему данные, если не задан — выводим все записи. Многие разработчики пытаются решить эту задачу "в лоб" с помощью логического оператора ИЛИ, конструируя условия вида:
&ИспользованиеФильтра И (ВТ.Поле1 = &Фильтр) ИЛИ НЕ &ИспользованиеФильтра
Иногда, пытаясь избавиться от "табуированного" оператора ИЛИ, разработчики вспоминают законы булевой алгебры (законы де Моргана) и преобразуют конструкцию через И и НЕ:
НЕ (&ИспользованиеФильтра И НЕ ВТ.Поле1 = &Фильтр)
Давайте подробно разберем, имеет ли смысл такая математическая оптимизация, как вообще СУБД реагирует на операторы ИЛИ и НЕ, и выясним, как правильно реализовывать необязательные условия отбора по современным стандартам разработки 1С.
Для начала проанализируем ситуацию с точки зрения сервера баз данных (например, MS SQL Server). В основе быстрого поиска данных в СУБД лежат индексы, организованные в виде B-деревьев (сбалансированных деревьев). Оптимизатор запросов отлично умеет искать данные по прямому совпадению (операторы = или В), так как он может быстро спуститься по веткам индекса прямо к нужным записям. Эта операция называется Index Seek и работает максимально быстро.
Что происходит, когда мы добавляем в запрос оператор ИЛИ для разных полей? СУБД больше не может пройти по одному пути дерева, так как условия противоречат жесткой структуре индекса. Из-за этого сервер вынужден отбросить индексный поиск и начать построчное чтение всей таблицы (Table Scan или Index Scan), что фатально сказывается на производительности при больших объемах данных.
А теперь посмотрим на попытку обмануть СУБД с помощью отрицания: НЕ (&Условие И НЕ Поле = &Фильтр). Это делает ситуацию еще хуже! Операторы отрицания, такие как НЕ и <>, принципиально лишают оптимизатор возможности использовать поиск по индексу. Вместо быстрого поиска конкретного значения системе приходится проверять каждую запись на "несовпадение". Индексы для таких условий бесполезны.
Рассмотрим самое правильное и элегантное решение задачи необязательных параметров. В современной разработке на 1С хардкодить конструкции с &ИспользованиеФильтра ИЛИ НЕ &ИспользованиеФильтра считается плохим тоном и устаревшим "костылем".
Вместо этого мы настоятельно рекомендуем использовать расширение языка запросов Системы Компоновки Данных (СКД) или объект ПостроительЗапроса. Синтаксис выглядит следующим образом:
ВЫБРАТЬ
ВТ.Поле1,
ВТ.Поле2
ИЗ
ВременнаяТаблица КАК ВТ
{ГДЕ
ВТ.Поле1 = &Фильтр}
Как это работает на практике: фигурные скобки { } указывают платформе 1С, что это условие является опциональным. Если параметр &Фильтр не установлен пользователем, платформа 1С физически "вырежет" это условие из итогового SQL-запроса перед отправкой его на сервер СУБД. Таким образом, база данных вообще не узнает о существовании какого-либо фильтра, выполнив чистую и оптимальную выборку без сканирования таблиц из-за лишних ИЛИ.
Если мы пишем обычный запрос (не для СКД) и нам действительно необходимо отобрать данные по двум совершенно разным условиям, стандарты 1С и документация на ИТС рекомендуют заменять ИЛИ на объединение запросов. Разберем по шагам, почему это работает быстрее.
Предположим, у нас есть "плохой" запрос:
ВЫБРАТЬ
Таблица.Ссылка
ИЗ
Справочник.Номенклатура КАК Таблица
ГДЕ
Таблица.Артикул = &Артикул ИЛИ Таблица.Штрихкод = &Штрихкод
Перепишем его на оптимальный вариант:
ВЫБРАТЬ
Таблица.Ссылка
ИЗ
Справочник.Номенклатура КАК Таблица
ГДЕ
Таблица.Артикул = &Артикул
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Таблица.Ссылка
ИЗ
Справочник.Номенклатура КАК Таблица
ГДЕ
Таблица.Штрихкод = &Штрихкод
В чем секрет производительности? При использовании конструкции ОБЪЕДИНИТЬ ВСЕ СУБД рассматривает каждую часть объединения как полностью независимый запрос. В первой части она быстро находит данные по индексу артикула (Index Seek). Во второй части — использует индекс штрихкода (еще один Index Seek). Затем результаты просто "склеиваются". Это работает в десятки раз быстрее, чем полное сканирование таблицы из-за оператора ИЛИ.
Важный нюанс: всегда старайтесь использовать именно ОБЪЕДИНИТЬ ВСЕ, так как просто ОБЪЕДИНИТЬ запускает скрытую и ресурсоемкую операцию поиска и удаления дублей строк.
Не стоит впадать в крайности. Оператор ИЛИ не всегда является абсолютным злом, и в некоторых случаях его использование абсолютно безопасно и оправдано. Выясним эти ситуации:
Поле1 = 1 ИЛИ Поле1 = 2 ИЛИ Поле1 = 3 является абсолютно рабочим. Встроенный планировщик СУБД распознает этот паттерн и автоматически преобразует его во внутренний аналог Поле1 В (1, 2, 3). Индекс по этому полю будет успешно использован.И, которое сильно ограничивает выборку. Например: ГДЕ Организация = &Организация И (Статус = &Открыт ИЛИ Статус = &ВРаботе). СУБД сначала быстро отфильтрует данные по индексу организации (сделает выборку маленькой), а затем легко применит условие ИЛИ к оставшемуся крошечному объему строк уже в оперативной памяти.Мы проанализировали использование ИЛИ в секции ГДЕ, но существует гораздо более разрушительный сценарий — использование этого оператора в условиях соединений. Посмотрим на пример:
...
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Данные КАК ТаблицаБ
ПО (ТаблицаА.Поле1 = ТаблицаБ.Поле1 ИЛИ ТаблицаА.Поле2 = ТаблицаБ.Поле2)
Такая конструкция практически гарантированно "положит" сервер при большом объеме данных. Оптимизатор СУБД не сможет использовать алгоритмы Hash Match или Merge Join для связывания таблиц. Ему придется переключиться на алгоритм Nested Loops (Вложенные циклы) с полным сканированием: для КАЖДОЙ строки из первой таблицы сервер будет просматривать ВСЮ вторую таблицу целиком. Такие соединения необходимо жестко рефакторить, разбивая на несколько последовательных левых соединений (каждое по своему полю) и затем собирая результат с помощью функции ЕСТЬNULL.
Мы подробно разобрали, как логические операторы влияют на производительность базы данных. Запомните следующие правила, чтобы ваши запросы летали:
ИЛИ переворачивая условие через отрицание НЕ. Это сделает только хуже, лишив СУБД возможности использовать индексы.{ГДЕ Поле = &Параметр}.ИЛИ на конструкцию ОБЪЕДИНИТЬ ВСЕ.ИЛИ в условиях конструкций ЛЕВОЕ СОЕДИНЕНИЕ и ВНУТРЕННЕЕ СОЕДИНЕНИЕ.