Почему запрос с вложенным запросом и дополнительными условиями работает медленнее, и как это исправить?

← На главную

Казалось бы, добавление жесткого ограничивающего условия во вложенный запрос должно уменьшать объем обрабатываемых данных и ускорять выполнение программы. Однако на практике разработчики 1С часто сталкиваются с противоположным эффектом: стоит добавить фильтр по дате во вложенный запрос, как скорость его выполнения падает в разы, а иногда и в десятки раз. В рамках этой статьи мы подробно разберем, почему так происходит, какие скрытые механизмы запускаются на уровне СУБД и как правильно оптимизировать подобные запросы согласно стандартам разработки 1С.

Исходная проблема и анализ исходного запроса

Рассмотрим типичный пример запроса, с которым столкнулся автор темы. В нем происходит левое соединение документов ОтчетОРозничныхПродажах с вложенным запросом по документам ПриходныйКассовыйОрдер (ПКО), в котором рассчитывается сумма документов с группировкой по ссылке:


ВЫБРАТЬ
	ОтчетОРозничныхПродажах.Ссылка КАК ОРП,
	ПриходныйКассовыйОрдер.Ссылка КАК ПКО
ИЗ
	Документ.ОтчетОРозничныхПродажах КАК ОтчетОРозничныхПродажах
		ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
			Ссылка,
			СУММА(СуммаДокумента) КАК Сумма
		ИЗ
			Документ.ПриходныйКассовыйОрдер КАК ПриходныйКассовыйОрдер
		
		//ГДЕ Дата МЕЖДУ &НачалоПериода И &КонецПериода
		
		СГРУППИРОВАТЬ ПО
			Ссылка) КАК ПриходныйКассовыйОрдер
		ПО КОНЕЦПЕРИОДА(ОтчетОРозничныхПродажах.Дата, ДЕНЬ) = КОНЕЦПЕРИОДА(ПриходныйКассовыйОрдер.Дата, ДЕНЬ)
			И ОтчетОРозничныхПродажах.Организация = ПриходныйКассовыйОрдер.Организация
			И ОтчетОРозничныхПродажах.Контрагент = ПриходныйКассовыйОрдер.Контрагент
ГДЕ
	ОтчетОРозничныхПродажах.Дата МЕЖДУ &НачалоПериода И &КонецПериода

Автор заметил интересную особенность: если раскомментировать условие по дате внутри вложенного запроса, время выполнения существенно увеличивается. Давайте разберем по шагам, какие архитектурные и системные факторы влияют на это поведение.

Причина №1. Неявные соединения СУБД из-за обращения «через точку»

Проанализируем секцию ПО в условии соединения нашего запроса. Мы видим сравнение полей:

КОНЕЦПЕРИОДА(ОтчетОРозничныхПродажах.Дата, ДЕНЬ) = КОНЕЦПЕРИОДА(ПриходныйКассовыйОрдер.Дата, ДЕНЬ)
ОтчетОРозничныхПродажах.Организация = ПриходныйКассовыйОрдер.Организация
ОтчетОРозничныхПродажах.Контрагент = ПриходныйКассовыйОрдер.Контрагент

Обратите внимание на правую часть сравнений. Мы обращаемся к полям Дата, Организация и Контрагент виртуальной таблицы ПриходныйКассовыйОрдер. Но во вложенном запросе мы выбираем только два поля: Ссылка и Сумма! Физически во вложенной выборке нет ни даты, ни организации, ни контрагента.

Платформа 1С «прощает» разработчику такую небрежность и автоматически разыменовывает эти поля. Однако на уровне СУБД транслятор запросов 1С вынужден сгенерировать дополнительное неявное левое соединение результирующего набора подзапроса с физической таблицей документов ПКО по полю Ссылка (первичному ключу), чтобы извлечь недостающие данные для связи. В итоге вместо одного простого соединения СУБД строит сложную цепочку связей из трех таблиц, что сильно усложняет построение оптимального плана выполнения.

Причина №2. Как ручное условие ломает «Проталкивание предикатов»

Главная причина замедления при добавлении условия кроется в алгоритмах оптимизатора СУБД (MS SQL Server, PostgreSQL и др.) и понятии «проталкивание предикатов» (Predicate Pushdown). Выясним, как меняется логика СУБД в двух сценариях:

Когда явного условия внутри вложенного запроса НЕТ:
Оптимизатор СУБД анализирует запрос целиком. Он видит, что внешняя таблица ОтчетОРозничныхПродажах ограничена по датам (МЕЖДУ &НачалоПериода И &КонецПериода), а соединение выполняется по равенству дат. Используя правила логической транзитивности, оптимизатор сам «проталкивает» этот фильтр внутрь вложенного запроса.
СУБД строит план на основе механизма Nested Loops (вложенные циклы). Она сначала выбирает отфильтрованные строки ОРП, а затем для каждой строки делает точечный и очень быстрый поиск (Index Seek) во вложенный запрос по индексу первичного ключа. Вложенный запрос при этом фактически не обрабатывает всю таблицу ПКО, а вычисляется порционно для конкретных записей.
Когда условие ГДЕ явно добавлено во вложенный запрос:
Как только мы принудительно пишем условие по дате внутри подзапроса, содержащего группировку (СГРУППИРОВАТЬ ПО), оптимизатор СУБД воспринимает этот подзапрос как изолированный логический блок, который необходимо вычислить предварительно.
Вместо быстрых точечных поисков СУБД принимает решение полностью материализовать вложенный запрос. Она начинает сканировать таблицу ПКО за указанный период, выполняет группировку всей этой массы данных в оперативной памяти (или в базе tempdb при нехватке ресурсов) и только после этого соединяет полученный массив с основной таблицей через тяжелые алгоритмы Hash Match (хеш-соединение) или Merge Join. Полное сканирование и группировка данных «в лоб» практически всегда работают медленнее, чем каскадный поиск по индексам.

Причина №3. Избыточность группировки по первичному ключу

Во вложенном запросе используется группировка по полю Ссылка: СГРУППИРОВАТЬ ПО Ссылка. Поле Ссылка является уникальным идентификатором (первичным ключом таблицы СУБД). Каждая строка в таблице ПКО имеет уникальную ссылку.
Группировка по уникальному полю для подсчета агрегатной суммы реквизита шапки (который и так один для каждого документа) является избыточной операцией. Для СУБД наличие конструкции GROUP BY служит жестким барьером, мешающим оптимизатору упростить («схлопнуть») структуру запроса во внешний уровень. Без этой группировки СУБД могла бы применить оптимизацию упрощения соединений (View Merging), что полностью нивелировало бы падение производительности.

Решаем проблему правильно: использование Временных Таблиц

Соединения с вложенными запросами — это известное нарушение стандартов разработки 1С. Они делают запросы непредсказуемыми: при небольшом объеме данных запрос может работать быстро, но по мере роста базы данных производительность лавинообразно падает. Единственным надежным и стабильным решением является использование **Временных Таблиц (ВТ)**.

Рассмотрим по шагам, как переписать наш запрос с использованием временных таблиц:

Шаг 1. Выбираем данные во временную таблицу

В первом запросе пакета мы выбираем только нужные документы ПКО за указанный период. Сразу получаем из них все необходимые для связи реквизиты (дату, организацию, контрагента и сумму), избавляясь от последующих неявных соединений «через точку».


ВЫБРАТЬ
	ПриходныйКассовыйОрдер.Ссылка КАК Ссылка,
	КОНЕЦПЕРИОДА(ПриходныйКассовыйОрдер.Date, ДЕНЬ) КАК ПериодДень,
	ПриходныйКассовыйОрдер.Организация КАК Организация,
	ПриходныйКассовыйОрдер.Контрагент КАК Контрагент,
	СУММА(ПриходныйКассовыйОрдер.СуммаДокумента) КАК Сумма
ПОМЕСТИТЬ ВТ_ПКО
ИЗ
	Документ.ПриходныйКассовыйОрдер КАК ПриходныйКассовыйОрдер
ГДЕ
	ПриходныйКассовыйОрдер.Дата МЕЖДУ &НачалоПериода И &КонецПериода
СГРУППИРОВАТЬ ПО
	ПриходныйКассовыйОрдер.Ссылка,
	КОНЕЦПЕРИОДА(ПриходныйКассовыйОрдер.Date, ДЕНЬ),
	ПриходныйКассовыйОрдер.Организация,
	ПриходныйКассовыйОрдер.Контрагент

Шаг 2. Индексируем временную таблицу

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


ИНДЕКСИРОВАТЬ ПО
	ПериодДень,
	Организация,
	Контрагент
;

Шаг 3. Выполняем итоговое соединение

В следующем запросе пакета мы производим левое соединение таблицы ОРП с нашей подготовленной и проиндексированной временной таблицей ВТ_ПКО:


ВЫБРАТЬ
	ОтчетОРозничныхПродажах.Ссылка КАК ОРП,
	ВТ_ПКО.Ссылка КАК ПКО
ИЗ
	Документ.ОтчетОРозничныхПродажах КАК ОтчетОРозничныхПродажах
		ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПКО КАК ВТ_ПКО
		ПО КОНЕЦПЕРИОДА(ОтчетОРозничныхПродажах.Дата, ДЕНЬ) = ВТ_ПКО.ПериодДень
			И ОтчетОРозничныхПродажах.Организация = ВТ_ПКО.Организация
			И ОтчетОРозничныхПродажах.Контрагент = ВТ_ПКО.Контрагент
ГДЕ
	ОтчетОРозничныхПродажах.Дата МЕЖДУ &НачалоПериода И &КонецПериода

Почему использование Временных Таблиц работает быстрее?

Проанализируем преимущества перевода запроса на временные таблицы:

Изоляция операций: СУБД сначала выполняет простую выборку с фильтром по индексу даты ПКО, группирует небольшой объем данных и записывает его во временную таблицу. Сложные логические условия не смешиваются в одном плане.
Индексация: Создание индекса на временной таблице позволяет оптимизатору СУБД гарантированно использовать быстрый поиск (Index Seek) при соединении с основной таблицей.
Точная статистика: При использовании временных таблиц СУБД точно «знает», сколько строк находится во временной таблице, и может построить идеальный план соединения. В случае с вложенным запросом СУБД строит план «на ощупь», опираясь на приблизительные эвристические данные.

Таким образом, отказ от вложенных запросов в пользу временных таблиц не только решает текущую проблему с падением скорости, но и гарантирует стабильную и быструю работу вашей конфигурации при любом масштабе базы данных.