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