В чем разница между условием в секции соединения ПО и секции ГДЕ в запросах 1С?

← На главную

При написании запросов в платформе 1С:Предприятие разработчики часто сталкиваются с дилеммой: где именно указывать фильтрацию данных правой таблицы — непосредственно в секции связи таблиц ПО или переносить её в итоговый блок отбора ГДЕ. Разберем детально, как ведет себя платформа и СУБД в подобных случаях, развеем популярные мифы об оптимизации и выясним, как правильно писать такие запросы.

Анализ исходного запроса

Рассмотрим пример запроса, вызвавший обсуждение:


ВЫБРАТЬ
	ЗаказКлиента.Ссылка КАК ЗаказКлиента,
	ЗаказКлиента.Номер КАК НомерЗаказа,
	ЗаказКлиента.Дата КАК ДатаЗаказа,
	ЗаказКлиента.НомерПоДаннымКлиента КАК НомерЗаказаПоДаннымКлиента
ИЗ
	Документ.ЗаказКлиента КАК ЗаказКлиента
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ЧекККМ.Товары КАК ЧекККМТовары
		ПО ЗаказКлиента.Ссылка = ЧекККМТовары.УТР_ЗаказКлиента.Ссылка
			И (ЧекККМТовары.Ссылка.Проведен)
ГДЕ
	ЗаказКлиента.Проведен

СГРУППИРОВАТЬ ПО
	ЗаказКлиента.Ссылка,
	ЗаказКлиента.Номер,
	ЗаказКлиента.Дата,
	ЗаказКлиента.НомерПоДаннымКлиента

В данном запросе условие проверки проведения чека (ЧекККМТовары.Ссылка.Проведен) размещено внутри секции ПО при внутреннем соединении. Возникает вопрос: изменится ли что-либо, если перенести это условие в секцию ГДЕ?

Поведение условий при внутреннем соединении: мифы и реальность СУБД

Среди разработчиков распространено заблуждение, будто условие в секции ПО фильтрует строки непосредственно в момент сцепки таблиц, а условие в ГДЕ применяется только после формирования полной выборки, из-за чего первый вариант якобы работает быстрее. Проанализируем, как обстоят дела на самом деле.

При использовании конструкции ВНУТРЕННЕЕ СОЕДИНЕНИЕ оба варианта — размещение условия в блоке ПО или в блоке ГДЕ — абсолютно равнозначны как по получаемому результату, так и по скорости выполнения.

Современные реляционные системы управления базами данных (такие как MS SQL Server или PostgreSQL) не выполняют соединение «в лоб». Оптимизатор СУБД применяет механизм проталкивания предикатов (predicate pushdown). Это означает, что независимо от синтаксического расположения фильтра оптимизатор сначала отфильтрует данные по индексам, а затем соединит полученные выборки. План выполнения для обоих вариантов будет идентичным.

В чем критическая разница при ЛЕВОМ соединении?

Совершенно иначе запрос ведет себя при использовании конструкции ЛЕВОЕ СОЕДИНЕНИЕ. Здесь место указания условия кардинально меняет логику выборки:

Условие в секции ПО: фильтрация применяется только к присоединяемой таблице. Если для строки левой таблицы не найдется подходящей строки в правой таблице (или она не удовлетворяет условию), строка левой таблицы всё равно останется в результирующей выборке, а поля правой таблицы заполнятся значениями NULL.
Условие в секции ГДЕ: фильтрация накладывается на весь объединенный результат. Так как сравнение любого поля с NULL дает ложь, все несоединившиеся строки левой таблицы будут отсечены. Таким образом, левое соединение фактически превращается во внутреннее.

Скрытые проблемы и узкие места исходного запроса

Хотя сам вопрос касался условий соединения, в представленном коде есть несколько серьезных архитектурных недочетов, ухудшающих производительность системы в реальной базе данных.

1. Неявные соединения через точку (разыменование ссылок)

Выражение ЧекККМТовары.Ссылка.Проведен обращается из табличной части к реквизиту шапки документа. Платформа 1С транслирует такое обращение в дополнительное неявное левое соединение с таблицей Документ.ЧекККМ. Разыменование ссылок в условиях соединений нарушает стандарты разработки, усложняет сгенерированный SQL-запрос и может помешать оптимизатору эффективно задействовать индексы.

Также избыточным является обращение ЧекККМТовары.УТР_ЗаказКлиента.Ссылка. Если реквизит УТР_ЗаказКлиента уже является ссылочным, получать у него поле Ссылка не требуется. Если же реквизит имеет составной тип данных, это породит левые соединения со всеми таблицами типов, входящих в данный составной тип. Поля составного типа следует явно ограничивать через ВЫРАЗИТЬ(... КАК ...).

2. Размножение строк и тяжелая группировка

Поскольку в чеке ККМ может быть множество строк с одним и тем же заказом клиента, внутреннее соединение с табличной частью многократно дублирует данные заказа в промежуточной выборке. Из-за этого разработчику приходится использовать громоздкую секцию СГРУППИРОВАТЬ ПО по ссылочным и строковым полям, что создает существенную нагрузку на оперативную память и процессор сервера СУБД.

3. Отсутствие индексации по реквизиту табличной части

Кластерный индекс табличной части документа всегда строится по полям (Ссылка, НомерСтроки). Реквизиты табличной части по умолчанию не индексируются. Соединение по неиндексированному реквизиту УТР_ЗаказКлиента приводит к полному сканированию огромной таблицы строк чеков. Чтобы избежать этого, для реквизита в метаданных должно быть установлено свойство «Индексировать».

Оптимальные варианты переработки запроса

Посмотрим на практические способы решения задачи, которые устранят избыточные соединения и группировки.

Вариант 1. Использование условия «В (...)» через подзапрос

Если нам необходимо получить только реквизиты заказов, по которым есть проведенные чеки, нет смысла соединять всю таблицу строк чеков с шапкой заказов. Гораздо эффективнее проверить наличие ссылок через подзапрос:


ВЫБРАТЬ
	ЗаказКлиента.Ссылка КАК ЗаказКлиента,
	ЗаказКлиента.Номер КАК НомерЗаказа,
	ЗаказКлиента.Дата КАК ДатаЗаказа,
	ЗаказКлиента.НомерПоДаннымКлиента КАК НомерЗаказаПоДаннымКлиента
ИЗ
	Документ.ЗаказКлиента КАК ЗаказКлиента
ГДЕ
	ЗаказКлиента.Проведен
	И ЗаказКлиента.Ссылка В
		(ВЫБРАТЬ
			ЧекККМТовары.УТР_ЗаказКлиента
		ИЗ
			Документ.ЧекККМ.Товары КАК ЧекККМТовары
				ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ЧекККМ КАК ЧекККМ
				ПО ЧекККМТовары.Ссылка = ЧекККМ.Ссылка
		ГДЕ
			ЧекККМ.Проведен)

Такой подход избавляет от необходимости группировать строки заказов и явно соединяет шапку с табличной частью документа ЧекККМ по первичному ключу Ссылка, исключая скрытые разыменования через точку.

Вариант 2. Использование временной таблицы уникальных заказов

Для высоконагруженных систем и больших объемов данных наилучшую производительность и стабильность планов запроса обеспечивает предварительный отбор уникальных значений во временную таблицу:


ВЫБРАТЬ РАЗЛИЧНЫЕ
	ЧекККМТовары.УТР_ЗаказКлиента КАК ЗаказКлиента
ПОМЕСТИТЬ ВТ_ЗаказыИзЧеков
ИЗ
	Документ.ЧекККМ.Товары КАК ЧекККМТовары
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ЧекККМ КАК ЧекККМ
		ПО ЧекККМТовары.Ссылка = ЧекККМ.Ссылка
ГДЕ
	ЧекККМ.Проведен
ИНДЕКСИРОВАТЬ ПО
	ЗаказКлиента;

ВЫБРАТЬ
	ЗаказКлиента.Ссылка КАК ЗаказКлиента,
	ЗаказКлиента.Номер КАК НомерЗаказа,
	ЗаказКлиента.Дата КАК ДатаЗаказа,
	ЗаказКлиента.НомерПоДаннымКлиента КАК НомерЗаказаПоДаннымКлиента
ИЗ
	Документ.ЗаказКлиента КАК ЗаказКлиента
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ЗаказыИзЧеков КАК ВТ_Заказы
		ПО ЗаказКлиента.Ссылка = ВТ_Заказы.ЗаказКлиента
ГДЕ
	ЗаказКлиента.Проведен;

Разберем преимущества этого решения:

Во временную таблицу попадает компактный список уникальных ссылок на заказы, что минимизирует объем оперативной памяти.
Индексация временной таблицы по полю ЗаказКлиента позволяет СУБД выполнить быстрое соединение с таблицей Документ.ЗаказКлиента по кластерному индексу.
Полностью исключается ресурсоемкая операция группировки тяжелых полей шапки заказа.