При разработке отчетов и обработок на платформе 1С:Предприятие программисты регулярно сталкиваются с задачей фильтрации данных. Одна из самых распространенных и в то же время коварных задач — исключить из одного набора данных строки, которые присутствуют в другом наборе. Например, нам нужно получить список контрагентов, у которых есть договоры, истекающие в определенном периоде, но при этом полностью исключить тех клиентов, у которых есть другие, более новые и действующие договоры.
Начинающие разработчики часто путаются в синтаксисе языка запросов 1С, что приводит либо к неработающим отборам, либо к колоссальному падению производительности базы данных. В этой статье мы подробно разберем, почему стандартные интуитивные подходы могут не работать, проанализируем физический смысл происходящих в СУБД процессов и рассмотрим наиболее эффективные методики решения этой задачи, соответствующие стандартам разработки фирмы «1С».
Рассмотрим ситуацию, с которой столкнулся автор темы на форуме. Была сделана попытка выбрать данные из временной таблицы ВсеДоговоры и исключить из нее данные, содержащиеся во временной таблице ДействующиеДоговора. Код выглядел следующим образом:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ВсеДоговоры.Владелец,
ВсеДоговоры.СрокДействия,
ВсеДоговоры.Договор
ИЗ
ВсеДоговоры КАК ВсеДоговоры,
ДействующиеДоговора КАК ДействующиеДоговора
ГДЕ
НЕ ВсеДоговоры.Владелец В (ДействующиеДоговора.Владелец)
Логика автора казалась простой: «Выбираем все договоры, но только те, у которых владелец (контрагент) не входит в список владельцев из таблицы действующих договоров». Однако этот запрос выдает непредсказуемые результаты, а в итоговую выборку все равно попадают лишние данные. Выясним причину этого поведения.
Когда мы перечисляем таблицы через запятую в секции ИЗ без указания явного типа соединения (ВНУТРЕННЕЕ, ЛЕВОЕ и т. д.) и условий связи (после ключевого слова ПО), СУБД выполняет неявное кросс-соединение (декартово произведение). Это означает, что СУБД берет каждую строку из первой таблицы и сопоставляет её с каждой строкой из второй таблицы.
Проанализируем простейший пример:
ВсеДоговоры у нас есть 3 строки с контрагентами: ООО «Альфа», ООО «Бета», ООО «Гамма».ДействующиеДоговора есть 1 строка с контрагентом: ООО «Бета» (его нам нужно исключить).При кросс-соединении СУБД построит виртуальную таблицу из 3 строк:
Когда СУБД начинает обрабатывать условие ГДЕ НЕ ВсеДоговоры.Владелец В (ДействующиеДоговора.Владелец) в контексте этого кросс-соединения, логика выборки полностью нарушается из-за размножения записей. СУБД не может корректно сопоставить списки, так как они уже перемножены между собой на физическом уровне. Кроме того, если в обеих таблицах будет хотя бы по 10 000 строк, промежуточный результат кросс-соединения составит 100 000 000 строк! Это приведет к перегрузке оперативной памяти сервера и зависанию всей системы.
Для того чтобы исправить логическую ошибку кросс-соединения, нам необходимо убрать вторую таблицу из секции ИЗ и обратиться к ней непосредственно внутри условия фильтрации с помощью подзапроса. Посмотрим на рабочий вариант кода:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ВсеДоговоры.Владелец КАК Владелец,
ВсеДоговоры.СрокДействия КАК СрокДействия,
ВсеДоговоры.Договор КАК Договор
ИЗ
ВсеДоговоры КАК ВсеДоговоры
ГДЕ
НЕ ВсеДоговоры.Владелец В
(ВЫБРАТЬ
ДействующиеДоговора.Владелец
ИЗ
ДействующиеДоговора КАК ДействующиеДоговора)
Рассмотрим подробнее, почему этот вариант работает правильно:
ИЗ осталась только одна таблица — ВсеДоговоры. Это исключает декартово произведение строк.ГДЕ НЕ ... В (...) теперь проверяет значение поля Владелец для каждой строки основной таблицы по списку, который возвращает изолированный подзапрос.Хотя данный запрос решает поставленную задачу и выдает корректный результат, он имеет серьезные недостатки с точки зрения производительности:
НЕ В или NOT IN). Вместо быстрого поиска по индексу СУБД будет вынуждена построчно сканировать всю таблицу, что крайне критично при росте объемов базы данных.NOT IN специфицировано так, что если подзапрос вернет хотя бы одну строку со значением NULL, то все условие вернет результат «Неизвестно» (UNKNOWN), и итоговая выборка окажется абсолютно пустой. В 1С ссылки обычно типизированы, но при сложных запросах с левыми соединениями появление NULL в подзапросах вполне вероятно, что полностью сломает логику программы.В профессиональной среде разработки 1С стандартом де-факто для исключения данных является использование связки ЛЕВОЕ СОЕДИНЕНИЕ + ЕСТЬ NULL. Этот метод работает в разы быстрее, так как оптимизаторы СУБД умеют эффективно строить планы запросов для левых соединений (например, через Hash Match или Merge Join) и задействовать индексы.
Рассмотрим по шагам, как реализовать этот подход на практике:
Владелец).ГДЕ накладываем условие, что ключевое поле из правой таблицы должно быть равно NULL. Это означает, что для строки из левой таблицы не нашлось соответствия в правой (то есть этот контрагент отсутствует в списке исключений).Посмотрим на финальный оптимизированный запрос:
// Шаг 1. Получаем список контрагентов, которых нужно исключить (с действующими договорами)
ВЫБРАТЬ РАЗЛИЧНЫЕ
ДоговорыКонтрагентов.Владелец КАК Владелец
ПОМЕСТИТЬ ДействующиеДоговора
ИЗ
Справочник.ДоговорыКонтрагентов КАК ДоговорыКонтрагентов
ГДЕ
ДоговорыКонтрагентов.СрокДействия > &СрокДействияКонец
И ДоговорыКонтрагентов.Организация В(&Организация)
ИНДЕКСИРОВАТЬ
Владелец // Индексируем поле связи для ускорения последующего соединения
;
// Шаг 2. Получаем основной список договоров за целевой период
ВЫБРАТЬ РАЗЛИЧНЫЕ
ДоговорыКонтрагентов.Владелец КАК Владелец,
ДоговорыКонтрагентов.Ссылка КАК Договор,
ДоговорыКонтрагентов.СрокДействия КАК СрокДействия
ПОМЕСТИТЬ ВсеДоговоры
ИЗ
Справочник.ДоговорыКонтрагентов КАК ДоговорыКонтрагентов
ГДЕ
ДоговорыКонтрагентов.Организация В(&Организация)
И ДоговорыКонтрагентов.СрокДействия МЕЖДУ &СрокДействияНачало И &СрокДействияКонец
ИНДЕКСИРОВАТЬ
Владелец // Индексируем поле связи
;
// Шаг 3. Соединяем таблицы и исключаем ненужные данные
ВЫБРАТЬ
ВсеДоговоры.Владелец КАК Владелец,
ВсеДоговоры.СрокДействия КАК СрокДействия,
ВсеДоговоры.Договор КАК Договор
ИЗ
ВсеДоговоры КАК ВсеДоговоры
// Присоединяем таблицу исключений
ЛЕВОЕ СОЕДИНЕНИЕ ДействующиеДоговора КАК ДействующиеДоговора
ПО ВсеДоговоры.Владелец = ДействующиеДоговора.Владелец
ГДЕ
// Фильтр оставляет только те строки, которых НЕ БЫЛО в ДействующиеДоговора
ДействующиеДоговора.Владелец ЕСТЬ NULL
Проанализируем ключевые моменты, которые делают наш финальный запрос быстрым и надежным:
ИНДЕКСИРОВАТЬ, добавленную к временным таблицам. Если ваши таблицы содержат более нескольких десятков (или сотен) строк, создание индекса по полю связи (в данном случае по полю Владелец) жизненно необходимо. Без индексов СУБД будет выполнять вложенные циклы поиска, что приведет к сильным задержкам на больших базах.РАЗЛИЧНЫЕ при формировании таблицы ДействующиеДоговора гарантирует, что каждый контрагент встретится там ровно один раз. Это снижает нагрузку на СУБД при сопоставлении строк во время левого соединения.Используя методику ЛЕВОГО СОЕДИНЕНИЯ с последующим отбором по значению ЕСТЬ NULL, вы гарантируете стабильную и быструю работу вашей конфигурации 1С даже в условиях работы со справочниками и таблицами документов огромного объема.