При разработке отчетов, обработок выгрузки в маркетплейсы или интеграций часто возникает задача: получить актуальные цены по нескольким типам цен (например, оптовую и розничную) так, чтобы для каждой номенклатурной позиции формировалась ровно одна строка со всеми ценами в отдельных колонках.
Начинающие разработчики нередко решают эту задачу «в лоб»: создают несколько временных таблиц со срезом последних по каждому типу цен, а затем связывают их со справочником номенклатуры. Проанализируем, почему такой подход создает высокую нагрузку на СУБД, какие грубые ошибки допускаются в коде и как построить действительно быстрый и правильный запрос.
Рассмотрим структуру неоптимального запроса, с которого началось обсуждение. В нем формируются две временные таблицы ВТ_ЦенаОптовая и ВТ_ЦенаРозн через виртуальную таблицу РегистрСведений.ЦеныНоменклатуры.СрезПоследних, после чего они связываются со справочником номенклатуры через внутреннее соединение.
Выясним основные причины неоптимальности и риски такого кода:
ГДЕ вместо параметров виртуальной таблицы. Это самая грубая ошибка платформы 1С. Конструкция ГДЕ ЦеныНоменклатурыСрезПоследних.ТипЦен = &ТипЦен заставляет СУБД сначала рассчитать срез последних по всей таблице регистра (по всем товарам и всем типам цен за всю историю базы) и лишь затем профильтровать результат. Отбор обязательно должен передаваться во второй параметр виртуальной таблицы: РегистрСведений.ЦеныНоменклатуры.СрезПоследних(, ТипЦен = &ТипЦен).ЦеныНоменклатурыСрезПоследних.Валюта.код порождает неявное левое соединение со справочником Валюты на уровне генерируемого SQL-запроса. Если таких срезов несколько, то СУБД будет многократно выполнять лишние соединения к таблице валют.ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Если у товара задана оптовая цена, но еще не установлена розничная (или наоборот), товар будет полностью исключен из итоговой выборки, так как внутреннее соединение требует совпадения строк во всех связываемых таблицах.Если требуется быстро связать два среза последних без использования громоздких цепочек временных таблиц, можно использовать полное соединение двух срезов с корректно переданными параметрами. Разберем этот пример:
ВЫБРАТЬ
ЕСТЬNULL(ЦеныОпт.Номенклатура, ЦеныРозн.Номенклатура) КАК Номенклатура,
ЦеныОпт.Цена КАК ЦенаОпт,
ЦеныОпт.Валюта КАК ВалютаОпт,
ЦеныРозн.Цена КАК ЦенаРозница,
ЦеныРозн.Валюта КАК ВалютаРозница
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(, ТипЦен = &ТипЦенОпт) КАК ЦеныОпт
ПОЛНОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(, ТипЦен = &ТипЦенРозн) КАК ЦеныРозн
ПО ЦеныОпт.Номенклатура = ЦеныРозн.Номенклатура
Посмотрим на особенности этого решения: здесь параметры отбора переданы непосредственно в виртуальную таблицу, что отсекает лишние данные на уровне расчета среза. Однако использование ПОЛНОЕ СОЕДИНЕНИЕ на больших объемах данных может стать узким местом: СУБД вынуждена строить тяжелые планы выполнения (Full Outer Join), что на миллионных регистрах приводит к повышенному потреблению ресурсов базы TempDB.
Наиболее эффективный способ с точки зрения нагрузки на СУБД — прочитать регистр сведений ровно один раз. Мы передаем в параметры среза последних массив необходимых типов цен, а затем «разворачиваем» строки в колонки с помощью конструкции ВЫБОР и группировки.
Разберем реализацию этого метода по шагам:
ВЫБРАТЬ
Цены.Номенклатура КАК Номенклатура,
МАКСИМУМ(ВЫБОР
КОГДА Цены.ТипЦен = &ТипЦенОпт
ТОГДА Цены.Цена
ИНАЧЕ 0
КОНЕЦ) КАК ЦенаОпт,
МАКСИМУМ(ВЫБОР
КОГДА Цены.ТипЦен = &ТипЦенРозн
ТОГДА Цены.Цена
ИНАЧЕ 0
КОНЕЦ) КАК ЦенаРозница
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(, ТипЦен В (&МассивТиповЦен)) КАК Цены
СГРУППИРОВАТЬ ПО
Цены.Номенклатура
Рассмотрим, почему этот способ предпочтительнее:
ПОЛНОЕ СОЕДИНЕНИЕ или ЛЕВОЕ СОЕДИНЕНИЕ между результатами двух тяжелых подзапросов.СГРУППИРОВАТЬ ПО строго гарантирует, что каждая позиция номенклатуры появится в результирующей таблице ровно один раз.В исходной задаче автора требовалось выгрузить не всю базу, а только активные и доступные товары (около 6 000 позиций из сотен тысяч). Рассчитывать срез последних по всей номенклатуре базы данных крайне расточительно. Правильная архитектура запроса должна сначала отобрать список нужных позиций во временную таблицу, а затем передать этот список в параметры среза.
Посмотрим на комплексный эталонный запрос, объединяющий все правила оптимизации:
ВЫБРАТЬ
СпрНоменклатура.Ссылка КАК Номенклатура,
СпрНоменклатура.Артикул КАК Артикул
ПОМЕСТИТЬ ВТ_ОтборТоваров
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
ГДЕ
НЕ СпрНоменклатура.ЭтоГруппа
И СпрНоменклатура.МЮС_АктивныйТовар = ИСТИНА
И СпрНоменклатура.ГДА_Доступность = &ГДА_Доступность
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Цены.Номенклатура КАК Номенклатура,
МАКСИМУМ(ВЫБОР
КОГДА Цены.ТипЦен = &ТипЦенОпт
ТОГДА Цены.Цена
ИНАЧЕ 0
КОНЕЦ) КАК ЦенаОпт,
МАКСИМУМ(ВЫБОР
КОГДА Цены.ТипЦен = &ТипЦенРозн
ТОГДА Цены.Цена
ИНАЧЕ 0
КОНЕЦ) КАК ЦенаРозница
ПОМЕСТИТЬ ВТ_Цены
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
,
Номенклатура В
(ВЫБРАТЬ
ВТ_ОтборТоваров.Номенклатура
ИЗ
ВТ_ОтборТоваров)
И ТипЦен В (&МассивТиповЦен)) КАК Цены
СГРУППИРОВАТЬ ПО
Цены.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Товары.Артикул КАК Артикул,
ЕСТЬNULL(Цены.ЦенаОпт, 0) КАК ЦенаОпт,
ЕСТЬNULL(Цены.ЦенаРозница, 0) КАК ЦенаРозница
ИЗ
ВТ_ОтборТоваров КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Цены КАК Цены
ПО Товары.Номенклатура = Цены.Номенклатура
Проанализируем ключевые преимущества такой структуры:
ИНДЕКСИРОВАТЬ ПО Номенклатура позволяет СУБД задействовать операцию поиска по индексу (Index Seek), а не полное сканирование таблицы в TempDB.Номенклатура В (ВЫБРАТЬ ...) внутри параметров среза отсекает 90% неактуальных записей регистра еще до выполнения каких-либо математических агрегаций и вычислений.ЛЕВОЕ СОЕДИНЕНИЕ гарантирует, что даже если по какому-то товару цены еще не введены, сам товар не пропадет из выгрузки — в колонках цен останутся нулевые значения благодаря ЕСТЬNULL.