Разработчики часто сталкиваются с ситуацией, когда сам запрос к базе данных выполняется практически мгновенно, но вывод результатов или расчет итогов по группировкам занимает длительное время (10 секунд и более). Особенно остро эта проблема проявляется при работе с большими объемами данных и при использовании временных таблиц.
В этой статье мы подробно разберем, почему медленно работает секция ИТОГИ, как устроена архитектура платформы «1С:Предприятие 8» в части вычисления агрегатов, и рассмотрим практические методы оптимизации таких запросов.
Для того чтобы эффективно оптимизировать запрос, нам необходимо понять, где именно происходят вычисления. Секция ИТОГИ в платформе 1С имеет важную архитектурную особенность: расчет агрегатных функций (таких как СУММА, КОЛИЧЕСТВО, МАКСИМУМ и др.) по группировкам выполняется на стороне сервера приложений 1С (в процессе rphost), а не силами СУБД (MS SQL, PostgreSQL и т.д.).
Рассмотрим этот процесс по шагам:
ИТОГИ ПО....rphost последовательно (в один поток) обходит полученные строки и строит в оперативной памяти иерархическое дерево итогов.Если плоская таблица содержит сотни тысяч строк, то даже при моментальном ответе СУБД сервер приложений будет тратить колоссальное количество процессорного времени на последовательный обход и выделение памяти под дерево итогов. Наша задача — максимально облегчить эту работу для сервера приложений или полностью перенести её на СУБД.
Одной из наиболее частых причин низкой производительности при расчете итогов является использование полей составного типа (содержащих несколько возможных ссылок) в качестве группировок. Выясним причину, почему это происходит.
В СУБД составное ссылочное поле 1С хранится не в одной колонке, а в нескольких. Для каждого составного поля СУБД создает отдельную колонку под тип (числовой идентификатор метаданных) и отдельную колонку под уникальный идентификатор (GUID). Когда СУБД пытается подготовить данные для итогов, она вынуждена сортировать и группировать результат сразу по нескольким колонкам, из-за чего стандартные индексы СУБД перестают работать эффективно.
Решение: В запросе создания временной таблицы (ВТ) всегда явно ограничивайте типы полей с помощью конструкции ВЫРАЗИТЬ. Это приведет структуру таблицы на уровне СУБД к одной физической колонке.
Проанализируем пример «плохого» запроса:
// Неоптимальный вариант: поле ДокументОснование имеет составной тип
ВЫБРАТЬ
Продажи.ДокументОснование КАК Документ,
Продажи.СуммаОборот КАК Сумма
ПОМЕСТИТЬ ВТ_Данные
ИЗ
РегистрНакопления.Продажи.Обороты КАК Продажи;
А теперь оптимизируем его с явным приведением типа:
// Оптимальный вариант с приведением к конкретному типу
ВЫБРАТЬ
ВЫРАЗИТЬ(Продажи.ДокументОснование КАК Документ.РеализацияТоваровУслуг) КАК Документ,
Продажи.СуммаОборот КАК Сумма
ПОМЕСТИТЬ ВТ_Данные
ИЗ
РегистрНакопления.Продажи.Обороты КАК Продажи;
Если выборка данных для итогов происходит из временной таблицы, крайне важно, чтобы СУБД могла быстро отсортировать данные перед передачей их на сервер приложений. Для этого поля, по которым будут рассчитываться итоги, необходимо проиндексировать на этапе создания временной таблицы.
Рассмотрим пример создания индекса:
ВЫБРАТЬ
ВЫРАЗИТЬ(Продажи.ДокументОснование КАК Документ.РеализацияТоваровУслуг) КАК Документ,
Продажи.Номенклатура КАК Номенклатура,
Продажи.СуммаОборот КАК Сумма
ПОМЕСТИТЬ ВТ_Данные
ИЗ
РегистрНакопления.Продажи.Обороты КАК Продажи
ИНДЕКСИРОВАТЬ ПО
Документ,
Номенклатура;
Индексирование полей группировки позволяет СУБД использовать эффективные алгоритмы сортировки (например, Index Scan вместо дорогостоящего Sort в плане запроса), что значительно ускоряет передачу подготовленных данных в rphost.
Еще одна критическая точка неоптимальности — расчет агрегатных функций по ссылочным полям в самой секции ИТОГИ. Например, когда разработчик пытается посчитать количество уникальных клиентов или документов с помощью конструкции КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Контрагент).
В чем сложность для системы:
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Ссылка) сервер приложений вынужден для каждого уровня группировки строить в памяти уникальную хэш-таблицу значений, чтобы отсеять дубликаты. Это требует огромных затрат оперативной памяти и процессорного времени.МАКСИМУМ(Ссылка) или МИНИМУМ(Ссылка) заставляет СУБД и сервер приложений сравнивать значения GUID в двоичном формате, что также является крайне ресурсоемкой операцией и редко несет практическую пользу для бизнес-логики.Решение: Сделайте предварительную свертку (группировку) на уровне временной таблицы, избавившись от необходимости вычислять количество различных элементов на лету. Сформируйте числовое поле-маркер (например, число 1) для уникальных комбинаций, а в итогах используйте простую и быструю функцию СУММА.
Если стандартный механизм ИТОГИ продолжает работать медленно из-за колоссального объема данных, мы можем полностью отказаться от этой секции в запросе и заставить СУБД самостоятельно рассчитать все уровни группировок. СУБД делает это в разы быстрее за счет многопоточности и оптимизированных планов выполнения.
Разберем по шагам, как переписать такой запрос с помощью конструкции СГРУППИРОВАТЬ ПО и последующего объединения результатов:
// 1. Детальные записи (Уровень 0)
ВЫБРАТЬ
0 КАК УровеньИтогов,
Данные.Организация КАК Организация,
Данные.Контрагент КАК Контрагент,
Данные.Сумма КАК Сумма
ИЗ
ВТ_Данные КАК Данные
ОБЪЕДИНИТЬ ВСЕ
// 2. Итоги по Контрагентам внутри Организаций (Уровень 1)
ВЫБРАТЬ
1 КАК УровеньИтогов,
Данные.Организация КАК Организация,
Данные.Контрагент КАК Контрагент,
СУММА(Данные.Сумма) КАК Сумма
ИЗ
ВТ_Данные КАК Данные
СГРУППИРОВАТЬ ПО
Данные.Организация,
Данные.Контрагент
ОБЪЕДИНИТЬ ВСЕ
// 3. Общие итоги по Организациям (Уровень 2)
ВЫБРАТЬ
2 КАК УровеньИтогов,
Данные.Организация КАК Организация,
НЕОПРЕДЕЛЕНО КАК Контрагент,
СУММА(Данные.Сумма) КАК Сумма
ИЗ
ВТ_Данные КАК Данные
СГРУППИРОВАТЬ ПО
Данные.Организация
ОБЪЕДИНИТЬ ВСЕ
// 4. Общий итог по всей таблице (Уровень 3)
ВЫБРАТЬ
3 КАК УровеньИтогов,
НЕОПРЕДЕЛЕНО КАК Организация,
НЕОПРЕДЕЛЕНО КАК Контрагент,
СУММА(Данные.Сумма) КАК Сумма
ИЗ
ВТ_Данные КАК Данные;
При таком подходе сервер приложений 1С мгновенно получает готовую плоскую таблицу, в которой уже рассчитаны все необходимые итоги. Нам остается лишь правильно обойти этот результат в коде или вывести его пользователю.
Если ваш запрос используется в качестве источника данных для отчета на базе СКД, то использование секции ИТОГИ непосредственно в тексте запроса является грубой ошибкой проектирования.
СКД имеет собственный высокоэффективный процессор вывода, который умеет рассчитывать итоги в многопоточном режиме, используя оптимизированные внутренние структуры данных. Наличие слова ИТОГИ в запросе СКД заставляет платформу выполнять двойную работу: сначала рассчитывать итоги силами сервера приложений для самого запроса, а затем повторно агрегировать эти данные в процессоре компоновки.
Решение:
ИТОГИ из текста запроса в схеме компоновки данных.Помните золотое правило оптимизации запросов 1С: чем меньше строк передается между временными таблицами и на финальный этап расчета, тем быстрее работает система.
Перед тем как рассчитывать итоги, убедитесь, что вы не передаете избыточно детальные записи. Если вам нужны итоги по дням, не нужно выбирать данные с точностью до секунды. Сгруппируйте данные (используя СГРУППИРОВАТЬ ПО) на этапе их помещения во временную таблицу. Это сократит объем обрабатываемых данных в разы, и расчет итогов выполнится мгновенно.