При разработке отчетов и обработок в среде 1С:Предприятие 8 мы часто сталкиваемся с задачами анализа динамики движения товаров. Одна из классических задач — поиск неликвидной номенклатуры, по которой не было движений (оборотов) за определенный период времени. Казалось бы, для получения этих данных оптимально использовать виртуальную таблицу оборотов регистра накопления. Однако на практике разработчики нередко замечают парадокс: запрос напрямую к физической таблице движений с простым ограничением по периоду выполняется быстрее, чем запрос к специализированной виртуальной таблице оборотов. В этой статье мы подробно разберем, почему так происходит, заглянем «под капот» СУБД, проанализируем неоптимальные конструкции в языке запросов 1С и выработаем надежные, быстрые решения.
Рассмотрим ситуацию, с которой столкнулся разработчик. Ему необходимо получить список номенклатуры, по которой не было движений за указанный период времени, при этом отсеяв определенные свойства объектов (например, признак «Неликвид»).
Разработчик протестировал два варианта запроса. В первом варианте использовалась виртуальная таблица оборотов с периодичностью Год:
ВЫБРАТЬ
Номенклатура.Ссылка
ИЗ
Справочник.Номенклатура КАК Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ПО Номенклатура.Ссылка = ЗначенияСвойствОбъектов.Объект
И (ЗначенияСвойствОбъектов.Свойство = &Свойство)
ГДЕ
НЕ Номенклатура.Ссылка В
(ВЫБРАТЬ РАЗЛИЧНЫЕ
ПартииТоваровКомпанииОбороты.Номенклатура
ИЗ
РегистрНакопления.ПартииТоваровКомпании.Обороты(&НачалоПериода, &КонецПериода, Год, ) КАК ПартииТоваровКомпанииОбороты)
И ВЫБОР
КОГДА ЗначенияСвойствОбъектов.Значение ЕСТЬ NULL
ТОГДА ИСТИНА
ИНАЧЕ ЗначенияСвойствОбъектов.Значение = &Неликвид
КОНЕЦ
Во втором варианте виртуальная таблица была заменена на выборку из физической таблицы движений регистра накопления с ограничением по периоду в секции ГДЕ:
ВЫБРАТЬ
Номенклатура.Ссылка
ИЗ
Справочник.Номенклатура КАК Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ПО Номенклатура.Ссылка = ЗначенияСвойствОбъектов.Объект
И (ЗначенияСвойствОбъектов.Свойство = &Свойство)
ГДЕ
НЕ Номенклатура.Ссылка В
(ВЫБРАТЬ РАЗЛИЧНЫЕ
Номенклатура
ИЗ
РегистрНакопления.ПартииТоваровКомпании Где Период Между &НачалоПериода и &КонецПериода)
И ВЫБОР
КОГДА ЗначенияСвойствОбъектов.Значение ЕСТЬ NULL
ТОГДА ИСТИНА
ИНАЧЕ ЗначенияСвойствОбъектов.Значение = &Неликвид
КОНЕЦ
Результаты замеров времени выполнения оказались неожиданными: второй запрос (к физической таблице) стабильно отрабатывал быстрее, чем первый (к виртуальной таблице), хотя логика подсказывает, что предрассчитанные итоги виртуальной таблицы должны ускорять процесс выборки. Давайте разберем причины этой аномалии по шагам.
Для того чтобы понять разницу в скорости работы, проанализируем, как платформа 1С транслирует обращение к виртуальной таблице Обороты в низкоуровневый SQL-запрос. Платформа не хранит обороты в готовом виде под абсолютно любой произвольный период. В базе данных SQL существуют две основные таблицы для каждого регистра накопления:
Когда мы запрашиваем виртуальную таблицу Обороты(&НачалоПериода, &КонецПериода, Периодичность), платформа выполняет сложный алгоритм сборки данных:
UNION ALL.tempdb.Более того, указание периодичности (например, Год или Авто) заставляет СУБД выполнять дополнительную группировку по временным интервалам, даже если нам в итоге нужны просто уникальные ссылки номенклатуры. Вся эта сложная математика генерирует громоздкий SQL-план со множеством вложенных подзапросов, что сильно нагружает оптимизатор СУБД.
Теперь посмотрим, что происходит во втором запросе при прямом обращении к таблице РегистрНакопления.ПартииТоваровКомпании с условием Период МЕЖДУ &НачалоПериода И &КонецПериода:
Период и Номенклатура в таблицах движений регистров накопления всегда входят в состав кластеризованного или ведущего неупорядоченного индекса базы данных.Именно поэтому прямое чтение индекса физической таблицы движений для проверки факта наличия записей за период зачастую работает в разы быстрее, чем обращение к механизму виртуальных таблиц.
Важнейший фактор замедления обоих исходных запросов — использование конструкции НЕ Номенклатура В (...) в секции ГДЕ. С точки зрения СУБД, оператор NOT IN является неблагоприятным для оптимизации (Non-sargable):
NOT IN вернет хотя бы одну строку со значением NULL, то все внешнее выражение вернет пустой результат (Unknown). Из-за этого оптимизатор СУБД строит усложненные планы выполнения, часто отказываясь от использования индексов внешней таблицы в пользу полного сканирования таблицы (Table Scan / Index Scan).NOT IN заставляет СУБД обрабатывать подзапрос для каждой строки внешней таблицы, если оптимизатор не сможет самостоятельно преобразовать это выражение в оператор полусоединения (Anti Semi Join).Также обратим внимание на конструкцию ВЫБОР КОГДА ... ТОГДА ... КОНЕЦ в секции ГДЕ. Любые логические ветвления в условиях фильтрации мешают оптимизатору СУБД построить качественный план выполнения. СУБД не может применить индекс к вычисляемому на лету выражению. Такие условия необходимо упрощать до базовых логических операторов И / ИЛИ.
Выяснив причины медленной работы, мы можем значительно оптимизировать наш запрос. Рассмотрим правильную методику написания подобных выборок с использованием лучших практик разработки на платформе 1С.
Для исключения номенклатуры с оборотами гораздо эффективнее использовать левое соединение с последующей фильтрацией по значению NULL в правой таблице. На уровне СУБД такое соединение транслируется в высокопроизводительный оператор Left Anti Semi Join, который мгновенно отсекает ненужные строки. Перепишем наше условие:
ВЫБРАТЬ
мНоменклатура.Ссылка КАК Ссылка
ИЗ
Справочник.Номенклатура КАК мНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ПартииТоваровКомпании КАК ПартииТоваровКомпании
ПО мНоменклатура.Ссылка = ПартииТоваровКомпании.Номенклатура
И (ПартииТоваровКомпании.Период МЕЖДУ &НачалоПериода И &КонецПериода)
ГДЕ
ПартииТоваровКомпании.Номенклатура ЕСТЬ NULL
Упростим проверку свойств объектов. Вместо громоздкой конструкции ВЫБОР применим стандартную логику с оператором ИЛИ, которую СУБД сможет легко обработать:
// Вместо конструкции ВЫБОР КОГДА...
И (ЗначенияСвойствОбъектов.Значение ЕСТЬ NULL ИЛИ ЗначенияСвойствОбъектов.Значение = &Неликвид)
Если в базе данных миллионы записей номенклатуры, прямое левое соединение справочника с физической таблицей регистра может привести к огромному промежуточному декартову произведению. Самый надежный способ оптимизации — предварительный выбор уникальных ключей во временную таблицу (ВТ). Это изолирует сложные выборки и позволяет СУБД точечно работать с небольшими объемами данных.
Рассмотрим сбалансированный и быстрый вариант запроса, использующий временную таблицу для сбора номенклатуры с движениями:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ПартииТоваров.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_ТоварыСОборотами
ИЗ
РегистрНакопления.ПартииТоваровКомпании КАК ПартииТоваров
ГДЕ
ПартииТоваров.Период МЕЖДУ &НачалоПериода И &КонецПериода
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
СпрНоменклатура.Ссылка КАК Ссылка
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ТоварыСОборотами КАК ТоварыСОборотами
ПО СпрНоменклатура.Ссылка = ТоварыСОборотами.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ПО СпрНоменклатура.Ссылка = ЗначенияСвойствОбъектов.Объект
И (ЗначенияСвойствОбъектов.Свойство = &Свойство)
ГДЕ
ТоварыСОборотами.Номенклатура ЕСТЬ NULL
И (ЗначенияСвойствОбъектов.Значение ЕСТЬ NULL ИЛИ ЗначенияСвойствОбъектов.Значение = &Неликвид)
И НЕ СпрНоменклатура.ЭтоГруппа
В ходе обсуждения на форуме выяснилось важное уточнение бизнес-требований: неликвидом считается не просто товар без движений, а товар, у которого есть конечный остаток на складе, но при этом не было никаких движений за выбранный период. Если у товара нет остатка и не было движений — анализировать его бессмысленно (это может быть просто архивная или новая позиция).
Для решения этой задачи идеально подходит виртуальная таблица ОстаткиИОбороты, но использовать ее нужно аккуратно, предварительно отфильтровав свойства номенклатуры во временную таблицу. Разберем и проанализируем финальный, максимально оптимизированный вариант решения этой задачи:
// Шаг 1: Отбираем только ту номенклатуру, которая уже помечена свойством "Неликвид"
// Помещаем ее во временную таблицу для быстрого последующего исключения
ВЫБРАТЬ
ЗначенияСвойствОбъектов.Объект КАК Номенклатура
ПОМЕСТИТЬ ВТ_ИсключаемыеНеликвиды
ИЗ
РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ГДЕ
ЗначенияСвойствОбъектов.Свойство = &Свойство
И ЗначенияСвойствОбъектов.Значение = &Неликвид
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Получаем остатки и обороты.
// Нам нужны позиции, у которых Расход = 0 и Приход = 0 (не было движений за период),
// но КонечныйОстаток > 0.
ВЫБРАТЬ
ПартииТоваровКонечные.Номенклатура КАК Номенклатура,
ПартииТоваровКонечные.КоличествоКонечныйОстаток КАК Остаток
ИЗ
РегистрНакопления.ПартииТоваровКомпании.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, , , ) КАК ПартииТоваровКонечные
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИсключаемыеНеликвиды КАК ИсключаемыеНеликвиды
ПО ПартииТоваровКонечные.Номенклатура = ИсключаемыеНеликвиды.Номенклатура
ГДЕ
ПартииТоваровКонечные.КоличествоРасход = 0
И ПартииТоваровКонечные.КоличествоПриход = 0
И ПартииТоваровКонечные.КоличествоКонечныйОстаток > 0
И ИсключаемыеНеликвиды.Номенклатура ЕСТЬ NULL
Выясним, почему этот финальный вариант работает стабильно и быстро:
ВТ_ИсключаемыеНеликвиды и проиндексировали ее по полю соединения. Это избавляет основной запрос от тяжелого соединения со всей физической таблицей регистра сведений ЗначенияСвойствОбъектов.ЕСТЬ NULL гарантирует, что СУБД применит оптимальный план выполнения Anti Semi Join.Таким образом, мы детально разобрали особенности работы оптимизатора СУБД при работе с виртуальными таблицами 1С. Понимание физической структуры таблиц баз данных и отказ от использования неоптимальных логических конструкций в условиях ГДЕ позволяют создавать быстрые и надежные алгоритмы, готовые к высоким нагрузкам.