При написании запросов в 1С разработчики нередко сталкиваются с вопросом производительности: что произойдет, если в запросе сначала указано ЛЕВОЕ СОЕДИНЕНИЕ двух таблиц, а условие отбора по одной из них находится в секции ГДЕ? Существует опасение, что СУБД сначала полностью соединит обе таблицы целиком и лишь затем применит фильтр, что приведет к лишней нагрузке на сервер.
Разберем исходный пример запроса, выясним реальный порядок выполнения операций на стороне СУБД и проанализируем, в каких ситуациях перенос данных во временную таблицу действительно необходим, а когда он лишь снижает быстродействие системы.
Рассмотрим типовую конструкцию запроса, в которой выбираются физические лица со списком отбора и проверяется отсутствие записей в регистре сведений:
ВЫБРАТЬ
ФизическиеЛица.Ссылка КАК Ссылка,
АвансыРаботникамОрганизаций.СуммаАванса КАК СуммаАванса
ИЗ
Справочник.ФизическиеЛица КАК ФизическиеЛица
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АвансыРаботникамОрганизаций КАК АвансыРаботникамОрганизаций
ПО (АвансыРаботникамОрганизаций.Физлицо = ФизическиеЛица.Ссылка)
И (АвансыРаботникамОрганизаций.Организация = &Организация)
ГДЕ
ФизическиеЛица.Ссылка В (&Физлица)
И АвансыРаботникамОрганизаций.СуммаАванса ЕСТЬ NULL
УПОРЯДОЧИТЬ ПО
Ссылка
В данном запросе решается задача поиска физических лиц из переданного списка, по которым отсутствует запись в регистре сведений АвансыРаботникамОрганизаций по указанной организации.
Опасение, что СУБД сначала соединит весь справочник ФизическиеЛица со всей таблицей регистра сведений и только потом отфильтрует строки по условию ФизическиеЛица.Ссылка В (&Физлица), является заблуждением. Современные реляционные СУБД (MS SQL Server, PostgreSQL) используют встроенный механизм оптимизации — проталкивание предикатов (Predicate Pushdown).
Оптимизатор запросов анализирует условия в секции ГДЕ до выполнения физического соединения. В плане выполнения фильтрация по первичному ключу справочника ФизическиеЛица произойдет до соединения с регистром. СУБД выберет только те строки, которые соответствуют условию В (&Физлица), и уже для них выполнит поиск соответствующих записей в регистре (например, через оператор Nested Loops или Hash Match).
Таким образом, для небольших списков выносить выборку во временную таблицу только ради предварительной фильтрации не требуется — СУБД эффективно справляется с этой задачей самостоятельно.
Несмотря на оптимизацию СУБД, существуют сценарии, когда использование временной таблицы обязательно по техническим причинам и стандартам разработки 1С:
В (&Физлица) платформа 1С генерирует список параметров SQL вида IN (@P1, @P2, ...). В MS SQL Server действует жесткое ограничение — не более 2100 параметров на весь запрос. Если передан массив из 3000 элементов, запрос аварийно завершится с ошибкой СУБД.Посмотрим, как выглядит корректная оптимизация запроса с выносом списка физических лиц во временную таблицу. Обратите внимание на обязательное указание директивы ИНДЕКСИРОВАТЬ ПО:
ВЫБРАТЬ
ТЗ_Физлица.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_Физлица
ИЗ
&ТЗ_Физлица КАК ТЗ_Физлица
ИНДЕКСИРОВАТЬ ПО
Ссылка
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Физлица.Ссылка КАК Ссылка
ИЗ
ВТ_Физлица КАК ВТ_Физлица
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АвансыРаботникамОрганизаций КАК АвансыРаботникамОрганизаций
ПО (АвансыРаботникамОрганизаций.Физлицо = ВТ_Физлица.Ссылка)
И (АвансыРаботникамОрганизаций.Организация = &Организация)
ГДЕ
АвансыРаботникамОрганизаций.СуммаАванса ЕСТЬ NULL
УПОРЯДОЧИТЬ ПО
Ссылка
Важное предостережение: создание временных таблиц создает нагрузку на базу данных tempdb (или системную дисковую подсистему в PostgreSQL). Если десятки пользователей одновременно запускают отчеты, где каждая мелочь выгружается во временные таблицы, сервер может испытывать дефицит дискового ввода-вывода и блокировки системных ресурсов.
Проанализируем другие варианты написания данного условия без использования соединения и проверки на NULL.
Вариант 1. Использование подзапроса с оператором НЕ В:
ВЫБРАТЬ
ФизическиеЛица.Ссылка КАК Ссылка
ИЗ
Справочник.ФизическиеЛица КАК ФизическиеЛица
ГДЕ
ФизическиеЛица.Ссылка В (&Физлица)
И ФизическиеЛица.Ссылка НЕ В
(ВЫБРАТЬ
Авансы.Физлицо
ИЗ
РегистрСведений.АвансыРаботникамОрганизаций КАК Авансы
ГДЕ
Авансы.Организация = &Организация)
Этот вариант эффективен, если по полю Физлицо в регистре сведений есть индекс, либо если измерения регистра начинаются с Организация, Физлицо.
Вариант 2. Эмуляция конструкции NOT EXISTS:
ВЫБРАТЬ
ФизическиеЛица.Ссылка КАК Ссылка
ИЗ
Справочник.ФизическиеЛица КАК ФизическиеЛица
ГДЕ
ФизическиеЛица.Ссылка В (&Физлица)
И НЕ 1 В
(ВЫБРАТЬ ПЕРВЫЕ 1
1
ИЗ
РегистрСведений.АвансыРаботникамОрганизаций КАК Авансы
ГДЕ
Авансы.Физлицо = ФизическиеЛица.Ссылка
И Авансы.Организация = &Организация)
Конструкция с проверкой подзапроса на стороне SQL-сервера преобразуется в оператор NOT EXISTS, который останавливает сканирование регистра сразу после нахождения первой совпадающей строки, не выполняя полного соединения таблиц.
Разберем дополнительные моменты, влияющие на производительность данного запроса:
ВЫБРАТЬ присутствует поле АвансыРаботникамОрганизаций.СуммаАванса, а в условии ГДЕ стоит фильтр АвансыРаботникамОрганизаций.СуммаАванса ЕСТЬ NULL. Данное поле всегда будет содержать значение NULL, поэтому его выборка из связанной таблицы избыточна.АВТОУПОРЯДОЧИВАНИЕ заставляет платформу 1С добавлять в SQL-запрос сортировку по всем ключевым полям соединенных таблиц. В запросах программного кода (не для динамических списков форм) АВТОУПОРЯДОЧИВАНИЕ рекомендуется исключать, чтобы избежать лишней операции Sort в плане запроса.