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