Разберем распространенную задачу: требуется собрать данные по торговым точкам с детализацией по дням за выбранный период — остатки на начало и конец каждого дня, продажи, внутрискладские перемещения, фактические цены из документов и цены из регистра сведений. Рассмотрим типичные ошибки при написании таких запросов и выясним, как правильно оптимизировать код для повышения производительности СУБД.
Проанализируем исходный вариант запроса, в котором соединяются календарь дней, виртуальная таблица остатков и оборотов, табличная часть документов перемещения и регистр сведений цен:
ВЫБРАТЬ РАЗЛИЧНЫЕ
РегламентированныйПроизводственныйКалендарь.ДатаКалендаря КАК Период
ПОМЕСТИТЬ Дни
ИЗ
РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК РегламентированныйПроизводственныйКалендарь
ГДЕ
РегламентированныйПроизводственныйКалендарь.ДатаКалендаря МЕЖДУ &НачалоПериод И &КонецПериод;
ВЫБРАТЬ
ВложенныйЗапрос.Период,
ВложенныйЗапрос.Склад,
ВложенныйЗапрос.Номенклатура,
ВложенныйЗапрос.КолВоКонОст - ВложенныйЗапрос.КолВоОборот КАК КолВоНачОст,
ВложенныйЗапрос.КолВоПриход,
ВложенныйЗапрос.КолВоОборот,
ПеремещениеТоваровТовары.Количество КАК Перемещения,
ВложенныйЗапрос.КолВоРасход - ЕСТЬNULL(ПеремещениеТоваровТовары.Количество, 0) КАК КолВоРасход,
ВложенныйЗапрос.КолВоКонОст,
ЦеныНоменклатуры.Цена КАК ЦенаРег,
ВложенныйЗапрос.ЦенаДок
ИЗ
(ВЫБРАТЬ
НАЧАЛОПЕРИОДА(ДНИ.Период, ДЕНЬ) КАК Период,
Движения.Номенклатура КАК Номенклатура,
СУММА(ВЫБОР КОГДА Движения.Период = ДНИ.Период ТОГДА Движения.КоличествоПриход ИНАЧЕ 0 КОНЕЦ) КАК КолВоПриход,
СУММА(ВЫБОР КОГДА Движения.Период = ДНИ.Период ТОГДА Движения.КоличествоРасход ИНАЧЕ 0 КОНЕЦ) КАК КолВоРасход,
СУММА(ВЫБОР КОГДА Движения.Период = ДНИ.Период ТОГДА Движения.КоличествоОборот ИНАЧЕ 0 КОНЕЦ) КАК КолВоОборот,
СУММА(ВЫБОР КОГДА Движения.Период = &НачалоПериод ТОГДА Движения.КоличествоКонечныйОстаток ИНАЧЕ ВЫБОР КОГДА Движения.Период <= ДНИ.Период ТОГДА Движения.КоличествоОборот ИНАЧЕ 0 КОНЕЦ КОНЕЦ) КАК КолВоКонОст,
Движения.Склад КАК Склад,
ВЫБОР КОГДА СУММА(Движения.КоличествоРасход) = 0 ТОГДА 0 ИНАЧЕ СУММА(Движения.СуммаПродажнаяРасход) / СУММА(Движения.КоличествоРасход) КОНЕЦ КАК ЦенаДок
ИЗ
РегистрНакопления.ТоварыВРознице.ОстаткиИОбороты(НАЧАЛОПЕРИОДА(&НачалоПериод, ДЕНЬ), КОНЕЦПЕРИОДА(&КонецПериод, ДЕНЬ), День, , ) КАК Движения,
Дни КАК ДНИ
СГРУППИРОВАТЬ ПО
Движения.Номенклатура,
Движения.Склад,
НАЧАЛОПЕРИОДА(ДНИ.Период, ДЕНЬ)) КАК ВложенныйЗапрос
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПеремещениеТоваров.Товары КАК ПеремещениеТоваровТовары
ПО (ВложенныйЗапрос.Период = НАЧАЛОПЕРИОДА(ПеремещениеТоваровТовары.Ссылка.Дата, ДЕНЬ))
И ВложенныйЗапрос.Склад = ПеремещениеТоваровТовары.Ссылка.СкладОтправитель
И ВложенныйЗапрос.Номенклатура = ПеремещениеТоваровТовары.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО ВложенныйЗапрос.Номенклатура = ЦеныНоменклатуры.Номенклатура
И (ЦеныНоменклатуры.ТипЦен.Наименование = "Розничная базовая")
И (ЦеныНоменклатуры.Период В
(ВЫБРАТЬ
МАКСИМУМ(Цены.Период)
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК Цены
ГДЕ
Цены.Период <= ВложенныйЗапрос.Период
И Цены.Номенклатура = ВложенныйЗапрос.Номенклатура
И Цены.ТипЦен.Наименование = "Розничная базовая"))
УПОРЯДОЧИТЬ ПО
ВложенныйЗапрос.Номенклатура,
ВложенныйЗапрос.Период
Выясним основные проблемы производительности и логики в приведенном тексте:
ОстаткиИОбороты и таблицы Дни без условий связи формирует полное перемножение строк. Это приводит к резкому росту объема временных данных в оперативной памяти и tempdb.Документ.ПеремещениеТоваров.Товары без предварительного сворачивания дублирует строки, если в течение суток было несколько перемещений одной позиции. Кроме того, выборка захватывает непроведенные документы и использует неэффективные обращения через точку к реквизитам шапки: Ссылка.Дата и Ссылка.СкладОтправитель.ЦеныНоменклатуры.ТипЦен.Наименование = "Розничная базовая" замедляет поиск и приводит к ошибкам при переименовании типа цен. Необходимо передавать ссылку через параметр.ПО заставляет СУБД многократно сканировать таблицу цен для каждой строки основного набора.Разберем по шагам, как переписать логику с использованием временных таблиц и корректной индексации.
Вместо соединения с табличной частью документа напрямую, заранее отберем только проведенные документы за период, сгруппируем количество по складам, номенклатуре и датам, а также проиндексируем результат:
ВЫБРАТЬ
НАЧАЛОПЕРИОДА(Товары.Ссылка.Дата, ДЕНЬ) КАК Период,
Товары.Ссылка.СкладОтправитель КАК Склад,
Товары.Номенклатура КАК Номенклатура,
СУММА(Товары.Количество) КАК Количество
ПОМЕСТИТЬ ВТ_Перемещения
ИЗ
Документ.ПеремещениеТоваров.Товары КАК Товары
ГДЕ
Товары.Ссылка.Проведен = ИСТИНА
И Товары.Ссылка.Дата МЕЖДУ &НачалоПериод И &КонецПериод
СГРУППИРОВАТЬ ПО
НАЧАЛОПЕРИОДА(Товары.Ссылка.Дата, ДЕНЬ),
Товары.Ссылка.СкладОтправитель,
Товары.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Период,
Склад,
Номенклатура;
Чтобы исключить ресурсоемкие подзапросы внутри соединений, реализуем срез цен в три последовательных шага:
(Номенклатура, Период) из основных движений во временную таблицу ВТ_ДатыНоменклатуры с обязательной индексацией.РегистрСведений.ЦеныНоменклатуры по условию Цены.Период <= Даты.Период и группируем по МАКСИМУМ(Цены.Период) для нахождения точной даты действия цены на каждый день.Номенклатура, ТипЦен и вычисленному максимальному Период для извлечения числового значения цены.Если отчет строится на базе Системы компоновки данных, сложные манипуляции с календарем и срезами цен можно полностью делегировать платформе:
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, ТипЦен = &ТипЦен). В связях наборов дата строки передается в параметр &Период, что позволяет платформе автоматически получать актуальную цену без тяжелых конструкций в тексте запроса.