При работе с крупными информационными базами нередко возникает соблазн сформировать всеобъемлющую «шахматку» — кросс-таблицу, содержащую в строках весь ассортимент номенклатуры, а в столбцах всех контрагентов предприятия. Разберем, к каким последствиям для серверной базы 1С приводит запуск отчета размером в десятки тысяч строк и тысячи колонок, может ли такая операция физически повредить данные и как решить прикладную задачу оптимизированными средствами платформы.
Проанализируем техническую сторону вопроса: способен ли отчет разрушить или испортить базу данных. Сам по себе отчет выполняет исключительно чтение информации из таблиц СУБД. Никаких изменений, перезаписей или удалений записей в таблицах не происходит, поэтому физически повредить структуру таблиц или стереть данные операция выборки не может. Однако запуск неоптимизированного отчета способен парализовать работу всей системы по следующим причинам:
tempdb). Если объем промежуточных вычислений исчерпает свободное место на диске, СУБД аварийно остановит обработку транзакций, из-за чего база может перейти в состояние сбоя (Recovery Pending или Suspect), став недоступной для пользователей.rphost собирает данные запроса в оперативной памяти. При превышении доступного объема ОЗУ (особенно если используется 32-разрядный сервер 1С с лимитом около 2–3 ГБ на процесс) рабочий процесс аварийно завершается операционной системой. Это приводит к падению сеансов всех пользователей, работавших на этом процессе, и потере их несохраненных данных.Выясним причину, почему вывод отчета размером 30 000 строк на 2 500 столбцов заведомо обречен на провал на стороне клиента. Матрица таких габаритов содержит 75 000 000 ячеек. Объект платформы ТабличныйДокумент хранит в оперативной памяти свойства каждой отдельной ячейки: форматирование, цвет, границы, текст и параметры шрифта.
Даже при минимальном расходе памяти около 50 байт на ячейку только для формирования экранной формы потребуется более 3,5–4 ГБ непрерывной оперативной памяти клиентского приложения. Кроме того, исчерпываются системные дескрипторы графического интерфейса Windows (GDI handles). В результате пользовательский сеанс 1С завершится аварийно с системным сообщением «Недостаточно памяти» задолго до окончания отрисовки таблицы.
Подход, при котором строится гигантская пустая матрица только ради того, чтобы выгрузить ее в Excel и посчитать заполненные ячейки формулой СЧЕТЗ, является грубой архитектурной ошибкой. Всю аналитическую обработку должна выполнять СУБД на уровне запроса.
Рассмотрим реальную цель: выяснить показатель дистрибьюции — сколько именно различных контрагентов покупали ту или иную номенклатурную позицию за указанный интервал времени. Нам не требуется выводить имена покупателей в колонки; нам нужно получить конечное число клиентов для каждой строки номенклатуры.
Для решения этой задачи достаточно применить агрегатную функцию языка запросов КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...) с группировкой по номенклатуре. Посмотрим на пример запроса, выполняющего этот расчет на уровне регистра накопления Продажи в конфигурации «Управление торговлей 10.3»:
ВЫБРАТЬ
ПродажиОбороты.Номенклатура КАК Номенклатура,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ПродажиОбороты.ДоговорКонтрагента.Владелец) КАК КоличествоПокупателей
ИЗ
РегистрНакопления.Продажи.Обороты(&ДатаНачала, &ДатаОкончания, , ) КАК ПродажиОбороты
СГРУППИРОВАТЬ ПО
ПродажиОбороты.Номенклатура
Разберем по шагам, как работает этот алгоритм:
РегистрНакопления.Продажи.Обороты, которая уже содержит предварительно рассчитанные итоги, что значительно ускоряет выборку.ДоговорКонтрагента.Владелец указывает непосредственно на справочник Контрагенты, то есть на конкретного покупателя.КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...) выполняет агрегацию на стороне СУБД. Если клиент «Васильев» покупал «Кроссовки красные» двадцать раз по разным накладным, СУБД учтет его строго один раз.СГРУППИРОВАТЬ ПО сворачивает весь массив до двух простых колонок: номенклатура и число уникальных клиентов. Вместо 75 миллионов ячеек на клиент возвращается легкая линейная таблица из 30 000 строк.Этот запрос можно вставить в стандартную обработку Консоль отчетов или Консоль запросов и мгновенно получить готовый результат без зависания системы.
Рассмотрим вариант реализации аналогичного отчета через Систему компоновки данных (СКД), который позволяет гибко накладывать отборы и настраивать оформление:
Номенклатура и ДоговорКонтрагента.Владелец из таблицы оборотов регистра Продажи.Количество(Различные ДоговорКонтрагентаВладелец).Номенклатура. В выбранные поля включим саму номенклатуру и созданный ресурс.В результате платформа сформирует компактную таблицу с минимальным потреблением оперативной памяти и нулевым риском перегрузки кластера серверов 1С.
Проанализируем системные меры, которые администратор 1С должен предпринять для предотвращения падений кластера из-за случайного запуска тяжелых выборок пользователями:
Максимальный объем памяти рабочих процессов и Безопасный расход памяти за один вызов. При их превышении сервер корректно прервет исполнение прожорливого вызова без аварийного падения службы rphost.