Разработка сложных аналитических отчетов в Системе компоновки данных (СКД), которые объединяют данные по продажам, закупкам, остаткам и ценам с детализацией по периодам (день, неделя, месяц), часто сопряжена с двумя серьезными проблемами. Во-первых, это неверный расчет итогов по остаткам (когда вместо остатка на конец периода СКД просто суммирует показатели за все интервалы). Во-вторых, это критическое падение производительности и зависание отчета при попытке добавить левое соединение к срезу цен или другим регистрам сведений.
В этой статье мы подробно разберем причины такого поведения платформы 1С:Предприятие и рассмотрим проверенные практические методики, которые позволят получить корректные итоги и обеспечить мгновенную работу отчета даже на базах объемом в сотни гигабайт.
Для начала проанализируем, что происходит «под капотом» СКД, когда мы настраиваем роли для полей остатков и периодов. Как только мы указываем для полей роли «Остатки» и связываем их с соответствующими полями-периодами, СКД берет на себя управление расчетом итогов по временной шкале. Компоновщик макета кардинально перестраивает исходный SQL-запрос, превращая его в сложнейшую конструкцию с множеством вложенных подзапросов. Это необходимо для того, чтобы рассчитать остатки на каждую «микродату» — на каждый момент изменения остатка или границу выбранного пользователем периода.
Если в этом же запросе присутствует левое соединение с виртуальной таблицей среза последних (например, РегистрСведений.ЦеныНоменклатуры.СрезПоследних), СУБД оказывается не в состоянии построить оптимальный план выполнения. Транслятор запросов СКД соединяет динамически рассчитываемые остатки с тяжелой виртуальной таблицей среза последних. В результате СУБД выполняет многократное сканирование таблиц и вычисление цен для каждой строки отчета, что приводит к бесконечному выполнению запроса и зависанию системы.
Давайте посмотрим на пример исходного неоптимального запроса, с которым часто сталкиваются разработчики:
ВЫБРАТЬ
ТоварыНаСкладахОстатки.Склад КАК Склад,
ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
ТоварыНаСкладахОстатки.Характеристика КАК Характеристика,
ТоварыНаСкладахОстатки.ПериодДень КАК ПериодДень,
ТоварыНаСкладахОстатки.ПериодМесяц КАК ПериодМесяц,
ТоварыНаСкладахОстатки.ВНаличииКонечныйОстаток КАК КоличествоКонечныйОстаток,
NULL КАК КоличествоПродаж
ПОМЕСТИТЬ ОсновныеДанные
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты({(&ПериодОт)}, {(&ПериодДо)}, Авто, , ) КАК ТоварыНаСкладахОстатки
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ПродажиРозницаОбороты.Склад,
ПродажиРозницаОбороты.Номенклатура,
ПродажиРозницаОбороты.Характеристика,
ПродажиРозницаОбороты.ПериодДень,
ПродажиРозницаОбороты.ПериодМесяц,
NULL,
ПродажиРозницаОбороты.КоличествоОборот
ИЗ
РегистрНакопления.ПродажиРозница.Обороты({(&ПериодОт)}, {(&ПериодДо)}, Авто, ) КАК ПродажиРозницаОбороты
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ОсновныеДанные.Склад,
ОсновныеДанные.Номенклатура,
ОсновныеДанные.Характеристика,
ОсновныеДанные.ПериодДень,
ОсновныеДанные.ПериодМесяц,
ОсновныеДанные.КоличествоКонечныйОстаток,
ОсновныеДанные.КоличествоПродаж,
ЦеныНоменклатурыСрезПоследних.Цена КАК Цена
ИЗ
ОсновныеДанные КАК ОсновныеДанные
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
{(&ПериодДо)},
ВидЦены = &Розничная
И (Номенклатура, Характеристика) В (ВЫБРАТЬ ОсновныеДанные.Номенклатура, ОсновныеДанные.Характеристика ИЗ ОсновныеДанные)
) КАК ЦеныНоменклатурыСрезПоследних
ПО ОсновныеДанные.Номенклатура = ЦеныНоменклатурыСрезПоследних.Номенклатура
И ОсновныеДанные.Характеристика = ЦеныНоменклатурыСрезПоследних.Характеристика
Этот запрос отлично работает до тех пор, пока мы не укажем роли для полей остатков и периодов. Но без ролей СКД не понимает, как правильно рассчитать итоги по группировкам периодов (например, по месяцам или неделям). Система начинает просто складывать остатки разных периодов, выдавая некорректные завышенные суммы. Как только мы настраиваем роли — отчет зависает.
Рассмотрим по шагам два надежных архитектурных решения, которые позволяют полностью обойти эту проблему.
Наиболее эффективный способ избежать зависаний при соединении таблиц остатков и цен в СКД — отказаться от левого соединения в одном тяжелом запросе. Вместо этого мы разделим получение данных на два независимых наборов данных и свяжем их на уровне самой СКД.
Этот подход великолепен тем, что СКД сначала получит плоскую таблицу остатков и оборотов (что происходит мгновенно), а затем точечно запросит цены из базы данных только для тех номенклатурных позиций и только на те даты, которые реально выводятся на экран пользователю.
Шаг 1. Создаем Набор данных №1 (Запрос)
В этом наборе данных мы получаем только данные по остаткам, оборотам складов и продажам. Запрос остается «чистым» и легким:
ВЫБРАТЬ
ТоварыНаСкладахОстатки.Склад КАК Склад,
ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
ТоварыНаСкладахОстатки.Характеристика КАК Характеристика,
ТоварыНаСкладахОстатки.ПериодДень КАК ПериодДень,
ТоварыНаСкладахОстатки.ВНаличииКонечныйОстаток КАК КоличествоКонечныйОстаток,
NULL КАК КоличествоПродаж
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты({(&ПериодОт)}, {(&ПериодДо)}, Авто, , ) КАК ТоварыНаСкладахОстатки
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ПродажиРозницаОбороты.Склад,
ПродажиРозницаОбороты.Номенклатура,
ПродажиРозницаОбороты.Характеристика,
ПродажиРозницаОбороты.ПериодДень,
NULL,
ПродажиРозницаОбороты.КоличествоОборот
ИЗ
РегистрНакопления.ПродажиРозница.Обороты({(&ПериодОт)}, {(&ПериодДо)}, Авто, ) КАК ПродажиРозницаОбороты
В настройках полей этого набора данных мы указываем роли для остатков и периодов так, как это делается в стандартных отчетах. Для поля КоличествоКонечныйОстаток задаем роль «Остатки» (тип: Конечный остаток), а для ПериодДень — роль «Период» с порядковым номером 1.
Шаг 2. Создаем Набор данных №2 (Запрос)
Второй набор данных предназначен исключительно для получения цен. Мы параметризуем виртуальную таблицу среза последних:
ВЫБРАТЬ
ЦеныНоменклатурыСрезПоследних.Номенклатура КАК Номенклатура,
ЦеныНоменклатурыСрезПоследних.Характеристика КАК Характеристика,
ЦеныНоменклатурыСрезПоследних.Цена КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ПериодСреза, ВидЦены = &Розничная) КАК ЦеныНоменклатурыСрезПоследних
Обратите внимание, что мы явно указали параметр &ПериодСреза в параметрах виртуальной таблицы. Это критически важно для следующего шага.
Шаг 3. Настраиваем связи наборов данных
Переходим на вкладку «Связи наборов данных» в СКД и добавляем новую связь:
Номенклатура = Номенклатура И Характеристика = Характеристика.ПериодСреза Набора данных №2 должно передаваться значение поля ПериодДень (или КонецПериода) из Набора данных №1.Благодаря такой архитектуре СКД самостоятельно выполнит соединение пакетов данных в памяти. Скорость формирования отчета вырастет в десятки раз, так как тяжелый срез последних больше не участвует в общем соединении на уровне СУБД.
Если мы объединяем в одном запросе несколько разных таблиц через оператор ОБЪЕДИНИТЬ ВСЕ, СКД часто путает и «схлопывает» остатки. В результате в общих итогах мы видим неверные цифры. Чтобы решить эту проблему раз и навсегда, применим проверенный профессиональный прием — метод Разделов учета.
Суть метода заключается в принудительном разделении строк разных регистров на уровне компоновщика данных, чтобы СКД рассчитывала остатки для каждой ветки объединения независимо.
Рассмотрим по шагам реализацию этого метода:
Шаг 1. Создаем искусственное поле раздела в запросе
В каждую ветку объединения нашего запроса мы добавляем строковую константу, определяющую раздел учета:
ВЫБРАТЬ
"1. Складские остатки" КАК РазделУчета,
ТоварыНаСкладахОстатки.Склад КАК Склад,
ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
ТоварыНаСкладахОстатки.ВНаличииКонечныйОстаток КАК КоличествоКонечныйОстаток,
NULL КАК КоличествоПродаж
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(...)
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"2. Продажи розница" КАК РазделУчета,
ПродажиРозницаОбороты.Склад,
ПродажиРозницаОбороты.Номенклатура,
NULL,
ПродажиРозницаОбороты.КоличествоОборот
ИЗ
РегистрНакопления.ПродажиРозница.Обороты(...)
Шаг 2. Настраиваем роль поля в схеме компоновки данных
После изменения запроса переходим на вкладку «Наборы данных» в СКД:
РазделУчета.Почему это работает? Флаг «Обязательное» указывает компоновщику СКД, что данное поле (раздел) нельзя исключать из SQL-запроса, даже если пользователь не выбрал его для вывода в отчет. Наличие роли «Измерение» заставляет систему производить расчет остатков строго в разрезе этих разделов. Потоки данных остатков складов и оборотов продаж больше не смешиваются, и итоги по периодам рассчитываются абсолютно корректно!
Часто ошибки расчета остатков по периодам возникают из-за некорректной настройки временной шкалы в СКД. Если мы выводим в отчет периодические данные (например, по дням или неделям), мы должны четко следовать правилам настройки периодов СКД.
Проверим и настроим следующие параметры на вкладке «Наборы данных»:
ПериодСекунда (или Регистратор) — Роль: Период, Порядковый номер: 1;ПериодДень — Роль: Период, Порядковый номер: 2;ПериодНеделя — Роль: Период, Порядковый номер: 3;ПериодМесяц — Роль: Период, Порядковый номер: 4.NULL. Это приводит к ошибкам СУБД или непредсказуемым результатам расчета остатков. В окне редактирования роли периода обязательно устанавливаем флаг «Игнорировать NULL».Мы рассмотрели комплексный подход к решению проблем с остатками по периодам в СКД. Применяя эти правила на практике, мы получаем стабильный результат:
РазделУчета с ролью «Измерение» и флагом «Обязательное». Это гарантирует математическую точность итогов.