При написании запросов к регистрам накопления разработчики 1С часто сталкиваются с выбором: передавать условия отбора непосредственно в параметры виртуальной таблицы или выполнять левое соединение с другими источниками данных. Ситуация усложняется, когда отбор выполняется по измерению, содержащему ключ аналитики (например, АналитикаУчетаНоменклатуры в типовых конфигурациях), и требуется обратиться к свойствам объекта через точку.
Рассмотрим, почему оба этих подхода могут существенно снижать скорость выполнения запроса, и разберем наиболее эффективный способ построения пакета запросов.
Проанализируем два популярных, но неоптимальных варианта написания запроса, представленных в вопросе.
Вариант 1. Использование подзапроса и разыменования через точку в параметрах виртуальной таблицы:
ВЫБРАТЬ
ТоварыНаСкладахОстатки.АналитикаУчетаНоменклатуры.Номенклатура КАК Номенклатура,
СУММА(ТоварыНаСкладахОстатки.КоличествоОстаток) КАК Количество
ПОМЕСТИТЬ ОстаткиПолучателя
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(
,
АналитикаУчетаНоменклатуры.Номенклатура В
(ВЫБРАТЬ РАЗЛИЧНЫЕ
ТребованиеПолучателяПоНоменклатуре.Номенклатура КАК Номенклатура
ИЗ
ТребованиеПолучателяПоНоменклатуре КАК ТребованиеПолучателяПоНоменклатуре)
И Склад = &СкладПолучатель) КАК ТоварыНаСкладахОстатки
СГРУППИРОВАТЬ ПО
ТоварыНаСкладахОстатки.АналитикаУчетаНоменклатуры.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Номенклатура;
Вариант 2. Использование левого соединения с вложенным запросом:
ВЫБРАТЬ
ВложенныйЗапрос.Номенклатура КАК Номенклатура,
СУММА(ТоварыНаСкладахОстатки.КоличествоОстаток) КАК Количество
ПОМЕСТИТЬ ОстаткиПолучателя
ИЗ
(ВЫБРАТЬ РАЗЛИЧНЫЕ
ТребованиеПолучателяПоНоменклатуре.Номенклатура КАК Номенклатура
ИЗ
ТребованиеПолучателяПоНоменклатуре КАК ТребованиеПолучателяПоНоменклатуре) КАК ВложенныйЗапрос
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(, Склад = &СкладПолучатель) КАК ТоварыНаСкладахОстатки
ПО ВложенныйЗапрос.Номенклатура = ТоварыНаСкладахОстатки.АналитикаУчетаНоменклатуры.Номенклатура
СГРУППИРОВАТЬ ПО
ВложенныйЗапрос.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Номенклатура;
Выясним главные причины низкой производительности предложенных запросов на уровне СУБД:
АналитикаУчетаНоменклатуры.Номенклатура): Когда в запросе происходит обращение к реквизиту через точку от ключа аналитики, платформа 1С автоматически генерирует неявное соединение LEFT JOIN к соответствующей таблице справочника. Если такое условие находится внутри параметров виртуальной таблицы или в секции соединения ПО, СУБД вынуждена выполнять соединение таблиц прямо во время вычисления итогов. Это блокирует использование оптимальных индексов регистра.В (ВЫБРАТЬ ...) в параметрах виртуальной таблицы не позволяет СУБД заранее оценить количество выбираемых записей (кардинальность). В результате оптимизатор SQL-сервера часто выбирает неэффективный план выполнения — вместо точечного поиска по индексу (Index Seek) происходит сканирование таблицы (Table Scan).(ВЫБРАТЬ РАЗЛИЧНЫЕ ...) не имеет проиндексированных полей. При левом соединении с виртуальной таблицей СУБД вынуждена строить хэш-таблицы в оперативной памяти на лету.Разберем по шагам, как правильно переписать запрос с использованием временных таблиц и пакета запросов для достижения максимальной скорости выполнения.
Справочник.КлючиАналитикиУчетаНоменклатуры) и помещаем найденные ключи во вторую временную таблицу с индексацией по ссылке на аналитику.Рассмотренный пошаговый подход на практике выглядит следующим образом:
// Шаг 1. Формируем список требуемой номенклатуры
ВЫБРАТЬ РАЗЛИЧНЫЕ
Требование.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_Номенклатура
ИЗ
ТребованиеПолучателяПоНоменклатуре КАК Требование
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2. Получаем ключи аналитики учета номенклатуры
ВЫБРАТЬ РАЗЛИЧНЫЕ
Аналитика.Ссылка КАК АналитикаУчета
ПОМЕСТИТЬ ВТ_Аналитика
ИЗ
Справочник.КлючиАналитикиУчетаНоменклатуры КАК Аналитика
ГДЕ
Аналитика.Номенклатура В (ВЫБРАТЬ ВТ.Номенклатура ИЗ ВТ_Номенклатура КАК ВТ)
ИНДЕКСИРОВАТЬ ПО
АналитикаУчета
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3. Выбираем остатки напрямую по ключам аналитики
ВЫБРАТЬ
ТоварыНаСкладахОстатки.АналитикаУчетаНоменклатуры КАК АналитикаУчета,
ТоварыНаСкладахОстатки.КоличествоОстаток КАК Количество
ПОМЕСТИТЬ ВТ_Остатки
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(
,
АналитикаУчетаНоменклатуры В (ВЫБРАТЬ ВТ.АналитикаУчета ИЗ ВТ_Аналитика КАК ВТ)
И Склад = &СкладПолучатель) КАК ТоварыНаСкладахОстатки
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 4. Финальное связывание номенклатуры и остатков
ВЫБРАТЬ
ВТ_Номенклатура.Номенклатура КАК Номенклатура,
ЕСТЬНУЛЛ(СУММА(ВТ_Остатки.Количество), 0) КАК Количество
ПОМЕСТИТЬ ОстаткиПолучателя
ИЗ
ВТ_Номенклатура КАК ВТ_Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.КлючиАналитикиУчетаНоменклатуры КАК Аналитика
ПО ВТ_Номенклатура.Номенклатура = Аналитика.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Остатки КАК ВТ_Остатки
ПО Аналитика.Ссылка = ВТ_Остатки.АналитикаУчета
СГРУППИРОВАТЬ ПО
ВТ_Номенклатура.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
Использование цепочки из нескольких проиндексированных временных таблиц позволяет СУБД построить корректную статистику распределения данных и гарантирует максимальную скорость получения остатков даже на больших объемах базы данных.