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