Как оптимизировать запрос для получения остатков номенклатуры на каждый день в 1С?

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

Причины низкой производительности исходного запроса

Проанализируем типовую ситуацию, когда запрос формирует остатки на каждый день с помощью перемножения таблицы дат и виртуальной таблицы регистра РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты.

Выясним главные узкие места, создающие высокую нагрузку:

1. Обращение к реквизитам через точку в секции выборки. Конструкции вида Движения.Номенклатура.ЕдиницаХраненияОстатков.Литраж заставляют СУБД делать неявные левые соединения (LEFT JOIN) с таблицами справочников для каждой выбранной строки. На больших объемах данных это существенно снижает скорость выполнения.
2. Условия по реквизитам ссылок в параметрах виртуальной таблицы. Передача условий типа Склад.ТипСклада <> ЗНАЧЕНИЕ(...) непосредственно в фильтр виртуальной таблицы усложняет работу оптимизатора SQL. СУБД не может эффективно задействовать штатные индексы регистра накопления, что приводит к сканированию всей таблицы.
3. Перемножение дат и движений без явной связи. Декартово произведение таблицы дат с результатами выборки регистра с последующим вычислением условий Период <= ДНИ.Период порождает огромный объем промежуточных данных в памяти или во временных таблицах tempdb.

Оптимизация запроса с помощью временных таблиц

Рассмотрим поэтапный подход к переработке запроса. Чтобы оптимизатор СУБД построил эффективный план выполнения, разделим выборку данных и наложение фильтров на независимые шаги с использованием временных таблиц.

Разберем по шагам предложенный алгоритм:

1. Отбираем списки складов по требуемым условиям в отдельную временную таблицу втФильтрПоСкладам.
2. Обращаемся к виртуальной таблице ОстаткиИОбороты, передавая условие отбора по складам через сформированную временную таблицу.
3. Отбираем уникальную номенклатуру и проверяем дополнительные реквизиты (поставщик, распродажа, статус закрытия) в отдельной временной таблице втФильтрПоНоменклатуре.
4. Соединяем полученные временные таблицы по индексированным полям.

Посмотрим на пример оптимизированного запроса:

ВЫБРАТЬ
	спрСклад.Ссылка КАК Склад
ПОМЕСТИТЬ втФильтрПоСкладам
ИЗ
	Справочник.Склады КАК спрСклад
ГДЕ
	спрСклад.ТипСклада <> ЗНАЧЕНИЕ(Перечисление.ТипыСкладов.Виртуальный)
{ГДЕ
	спрСклад.Ссылка КАК Склад}
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	Движения.Склад КАК Склад,
	Движения.Номенклатура КАК Номенклатура,
	Движения.Период КАК Период,
	Движения.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток,
	Движения.КоличествоОборот КАК КоличествоОборот
ПОМЕСТИТЬ втДанные
ИЗ
	РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(
		&НачалоПериода,
		&КонецПериода,
		ДЕНЬ,
		,
		Склад В (ВЫБРАТЬ вт.Склад ИЗ втФильтрПоСкладам КАК вт)
	) КАК Движения
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	втНоменклатура.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ втФильтрПоНоменклатуре
ИЗ
	(ВЫБРАТЬ РАЗЛИЧНЫЕ
		втДанные.Номенклатура КАК Номенклатура
	ИЗ
		втДанные КАК втДанные) КАК втНоменклатура
		ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК спрНоменклатура
		ПО втНоменклатура.Номенклатура = спрНоменклатура.Ссылка
{ГДЕ
	спрНоменклатура.НоменклатурнаяГруппа.*,
	спрНоменклатура.НоменклатурнаяГруппа.Поставщик КАК Поставщик,
	спрНоменклатура.ОсновнойПоставщик.*,
	спрНоменклатура.Закрыт,
	спрНоменклатура.Распровождение}

ИНДЕКСИРОВАТЬ ПО
	Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	втДанные.Склад КАК Склад,
	втДанные.Номенклатура КАК Номенклатура,
	втДанные.Период КАК Период,
	втДанные.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток
ИЗ
	втДанные КАК втДанные
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ втФильтрПоНоменклатуре КАК втФильтрПоНоменклатуре
		ПО втДанные.Номенклатура = втФильтрПоНоменклатуре.Номенклатура

Альтернативные способы решения в СКД и коде

Если отчет разрабатывается с использованием Системы компоновки данных (СКД), соединять таблицу дат с виртуальной таблицей регистра вручную в тексте запроса не требуется:

1. В самом запросе достаточно выбрать данные из виртуальной таблицы ОстаткиИОбороты с периодичностью ДЕНЬ (будут получены только даты с реальными движениями).
2. В настройках поля периода СКД устанавливается тип дополнения периодов День, а также указываются начальная и конечная даты.
3. Платформа самостоятельно дополнит пропущенные даты и подтянет конечные остатки с предыдущих дней при выводе отчета.

При программном выгрузке результата в коде 1С можно использовать секцию ИТОГИ с периодичностью по дням:

ИТОГИ СУММА(КоличествоКонечныйОстаток) ПО
	Период ПЕРИОДАМИ(ДЕНЬ, &НачалоПериода, &КонецПериода)

Для обхода выборки в коде применяется метод РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам, "Период", "ВСЕ"). Параметр "ВСЕ" заставит платформу добавить все отсутствующие даты в периоде, автоматически сохранив актуальные остатки на каждый день.