При работе с регистром бухгалтерии в системе 1С программисты часто сталкиваются с ситуацией, когда стандартный запрос к виртуальной таблице Обороты начинает работать крайне медленно или вовсе приводит к «зависанию» сервера. Особенно остро эта проблема проявляется при использовании периодичности Запись. Рассмотрим подробно, почему это происходит, как устроена работа этой таблицы «под капотом» и какие шаги необходимо предпринять для оптимизации производительности.
Главная особенность виртуальной таблицы Обороты заключается в том, что ее поведение кардинально меняется в зависимости от выбранной периодичности. Проанализируем этот механизм. Когда мы выбираем периодичность Месяц, Квартал или Год, платформа 1С обращается к предварительно рассчитанным таблицам итогов. Это позволяет мгновенно получать агрегированные данные.
Однако, как только в параметрах виртуальной таблицы указывается периодичность Запись, платформа полностью игнорирует таблицы итогов. В этом случае запрос транслируется в SQL как прямое обращение к основной физической таблице движений регистра (обычно это _AccRg), которая соединяется с таблицей значений субконто (_AccRgED). Если записей в регистре много, СУБД приходится выполнять полное сканирование индексов или даже таблиц, что и вызывает «раздумья» сервера.
Выясним причину, почему проблема может проявляться избирательно, например, только в одном месяце (как в случае с январем 2019 года). Это часто связано с механизмом Parameter Sniffing в SQL Server. План запроса, построенный для декабря, может оказаться неэффективным для данных января из-за разного распределения данных или устаревшей статистики по индексам. Кроме того, периодичность Запись заставляет систему выполнять дополнительные группировки для сопоставления оборотов каждой конкретной строке документа, что создает избыточную нагрузку.
Одной из самых тяжелых операций в запросах к виртуальным таблицам является использование условия В ИЕРАРХИИ непосредственно в параметрах таблицы. На уровне СУБД это условие разворачивается в огромный список всех подчиненных счетов через оператор IN. Посмотрим на исходный код, который вызывает проблемы:
ВЫБРАТЬ
ХозрасчетныйОбороты.Субконто1,
ХозрасчетныйОбороты.Регистратор
ИЗ
РегистрБухгалтерии.Хозрасчетный.Обороты(&НачПериода, &КонПериода, Запись, Счет В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.Материалы)), , , , ) КАК ХозрасчетныйОбороты
Чтобы оптимизировать этот участок, нам нужно заранее подготовить массив всех необходимых счетов и передать его в запрос. Разберем по шагам, как это сделать:
В ИЕРАРХИИ на обычное условие В (&МассивСчетов).Это позволит оптимизатору SQL построить более простой и эффективный план запроса, так как ему не придется каждый раз динамически вычислять иерархию счетов.
Если вам необходим Регистратор и детальная аналитика по каждой записи, часто эффективнее использовать виртуальную таблицу ДвиженияССубконто вместо Обороты. Проанализируем ситуацию: таблица Обороты предназначена для получения сумм, а ДвиженияССубконто — для получения самих проводок. При периодичности Запись эти таблицы возвращают похожий набор данных, но ДвиженияССубконто оптимизирована именно для построчного вывода.
Пример кода с использованием альтернативной таблицы:
ВЫБРАТЬ
ХозрасчетныйДвижения.СубконтоДт1 КАК Субконто1,
ХозрасчетныйДвижения.СубконтоДт2 КАК Субконто2,
ХозрасчетныйДвижения.Регистратор КАК Регистратор,
ХозрасчетныйДвижения.НомерСтроки КАК НомерСтроки
ИЗ
РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто(&НачПериода, &КонПериода, Счет = &НужныйСчет, , ) КАК ХозрасчетныйДвижения
Важный нюанс: хотя многие считают ДвиженияССубконто медленной таблицей, в условиях, когда итоги все равно не используются (периодичность Запись), она может отработать значительно быстрее за счет отсутствия лишних агрегаций.
Если запрос «зависает» на конкретном периоде времени, это явный признак проблем со статистикой в SQL-базе. Рассмотрим подробнее, что нужно сделать системному администратору или программисту с правами доступа к SQL Server:
UPDATE STATISTICS для таблиц регистра бухгалтерии (_AccRg и _AccRgED). Это заставит SQL Server пересчитать распределение данных и построить актуальный план запроса.DBCC FREEPROCCACHE. Она удаляет старые, неэффективные планы запросов, которые сервер пытается применить к новым данным.Еще один способ ускорить работу виртуальной таблицы — явно указать массив видов субконто в параметрах. Когда мы оставляем эти параметры пустыми, система пытается соединить таблицу движений со всеми возможными значениями субконто, что порождает сложные соединения (JOIN) в SQL-запросе.
Посмотрим на пример правильного заполнения параметров:
// Подготовим виды субконто
МассивСубконто = Новый Массив;
МассивСубконто.Добавить(ПланыВидовХарактеристик.ВидыСубконтоХозрасчетные.Номенклатура);
МассивСубконто.Добавить(ПланыВидовХарактеристик.ВидыСубконтоХозрасчетные.Склады);
// Укажем их в 5-м параметре виртуальной таблицы
// Обороты(&НачПериода, &КонПериода, Запись, &УсловиеСчет, &ВидыСубконто, ...)
Явное указание видов аналитики значительно сокращает количество обрабатываемых данных на уровне СУБД, так как отсекаются лишние записи в таблице _AccRgED.
Если ни один из методов не помогает мгновенно, рекомендуем выполнить следующие шаги для локализации ошибки:
DBMSSQL. Вы сможете увидеть реальный текст SQL-запроса, который генерирует платформа. Часто там обнаруживаются неожиданные соединения или условия, которые можно оптимизировать.ХозрасчетныйОбороты.Субконто1.Код). При периодичности Запись это создает лавинообразный рост количества соединений в SQL, что моментально «убивает» производительность.Подводя итог, можно сказать, что работа с периодичностью Запись требует от разработчика особого внимания к деталям. Отказ от использования итогов перекладывает всю нагрузку на физические таблицы и оптимизатор СУБД. Использование прямых списков значений вместо иерархии, уточнение аналитики и контроль за состоянием индексов SQL — это три «кита», на которых строится быстрая работа регистра бухгалтерии в крупных базах.