При росте объемов информационной базы «1С:Бухгалтерия» разработчики и администраторы нередко сталкиваются с катастрофическим падением производительности: закрытие месяца начинает занимать несколько дней, а типовой запрос к виртуальной таблице остатков выполняется по 5 секунд и дольше. При анализе структуры СУБД выясняется, что таблица значений субконто (_AccRgED) разрастается до сотен миллионов строк, а таблицы итогов содержат огромное количество записей. Проанализируем причины такого поведения системы, выясним, почему оптимизатор СУБД выбирает неоптимальные планы, и рассмотрим комплекс мер по восстановлению быстродействия.
Разберем, почему физическая таблица субконто регистра бухгалтерии достигает сотен миллионов записей, даже если документов в базе относительно немного:
ДокументРасчетовСКонтрагентом, число уникальных пар и троек субконто растет пропорционально числу документов, порождая лавинообразный рост строк.Строка, Число), это ломает индексацию и приводит к хранению неструктурированных данных, многократно увеличивая размер таблицы.Для понимания того, откуда берутся тормоза при расчете остатков, необходимо исследовать физические таблицы итогов в СУБД. Проверим, закрываются ли итоги и сколько в них содержится записей с нулевым сальдо. Рассмотрим пример прямого SQL-запроса к таблице итогов регистра бухгалтерии по трем субконто (_AccRgAT3*):
SELECT
_Period AS [ДатаИтога],
COUNT(*) AS [ВсегоСтрок],
SUM(CASE WHEN (_FLD8065 = 0 AND _FLD8066 = 0 AND _FLD8067 = 0
AND _FLD8068 = 0 AND _FLD8069 = 0 AND _FLD8070 = 0)
THEN 1 ELSE 0 END) AS [НулевыхЗаписей]
FROM dbo._AccRgAT38095
GROUP BY _Period
ORDER BY [ДатаИтога] DESC;
Выясним причину наличия нулевых записей: платформа 1С фиксирует в ежемесячных срезах итогов строки с нулевыми суммами, если в периоде было движение, которое обнулило остаток. Это штатный механизм платформы, гарантирующий историчность расчета. Штатный пересчет итогов очищает нули только в точке актуальности (дата 5999 года), но оставляет их в помесячных срезах. Если таких «мертвых» комбинаций накапливаются миллионы за несколько лет, расчет виртуальных таблиц остатков начинает перебирать колоссальные объемы бесполезных данных.
Проанализируем исходный запрос получения остатков, который вызывает сложности при проведении документов:
ВЫБРАТЬ
Остатки.Счет КАК СчетУчета,
Остатки.Подразделение КАК Подразделение,
Остатки.Субконто1 КАК Номенклатура,
Остатки.Субконто2 КАК Склад,
Остатки.Организация КАК Организация,
СУММА(ЕСТЬNULL(Остатки.КоличествоОстатокДт, 0)) КАК Количество,
СУММА(ЕСТЬNULL(Остатки.СуммаОстатокДт, 0)) КАК Стоимость
ИЗ
РегистрБухгалтерии.Хозрасчетный.Остатки(
&МоментКон,
Счет В (&СчетаУчетаНС),
&ВидыСубконтоНС,
(Подразделение В (&ФильтрПодразделения) ИЛИ Подразделение ЕСТЬ NULL)
И Субконто1 В (&Номенклатура)
И Субконто2 В (&Склады)
И Организация В (&Организации)) КАК Остатки
СГРУППИРОВАТЬ ПО
Остатки.Счет,
Остатки.Подразделение,
Остатки.Субконто1,
Остатки.Субконто2,
Остатки.Организация
ДЛЯ ИЗМЕНЕНИЯ
ИТОГИ ПО
Номенклатура;
Внесем ключевые исправления для ускорения этого запроса:
ИЛИ: Условие (Подразделение В (&ФильтрПодразделения) ИЛИ Подразделение ЕСТЬ NULL) в параметрах виртуальной таблицы не позволяет СУБД эффективно использовать существующие индексы, заставляя переходить к сканированию всей таблицы (Index Scan или Table Scan). Если подразделение не заполнено, передадим пустое значение в сам список фильтра либо разобьем логику на объединение запросов без оператора ИЛИ.ВЫРАЗИТЬ: Поля Субконто1 и Субконто2 имеют составной тип данных. При обращении к ним платформа строит тяжелые соединения со всеми возможными таблицами типов. Обязательно укажем явное приведение: ВЫРАЗИТЬ(Остатки.Субконто1 КАК Справочник.Номенклатура).ДЛЯ ИЗМЕНЕНИЯ: Конструкция ДЛЯ ИЗМЕНЕНИЯ в запросе при автоматическом режиме блокировок транслируется в строгие блокировки диапазонов на уровне СУБД. На таблице в сотни миллионов строк это приводит к мгновенной эскалации блокировки до уровня всей таблицы и ступору параллельной работы. Переведем конфигурацию в режим управляемых блокировок, установим блокировки через объект БлокировкаДанных в коде до выполнения запроса, а саму инструкцию ДЛЯ ИЗМЕНЕНИЯ из запроса удалим.Изучим сгенерированный SQL-план: если в коде SQL Server видно объединение (UNION ALL) с точкой актуальности 5999-11-01 и вычитание движений из таблицы _AccRg8061 за много лет назад, значит, платформа не использует помесячные итоги, а «отматывает» движения от фиктивной даты 5999 года.
Такое происходит, если период рассчитанных итогов не сдвинут вперед. Платформа должна опираться на ближайший рассчитанный срез. Для исправления ситуации используем программное смещение границы итогов вперед от рабочей даты:
// Сдвигаем период рассчитанных итогов минимум на один месяц вперед
ГраницаИтогов = КонецМесяца(ТекущаяДатаСеанса()) + 1;
РегистрыБухгалтерии.Хозрасчетный.УстановитьПериодРассчитанныхИтогов(ГраницаИтогов);
Проверим также свойство Разделение итогов у регистра. В высоконагруженных системах разделение итогов позволяет избежать блокировок при записи, однако оно многократно увеличивает физический объем записей в таблицах итогов (каждая комбинация дублируется по разделителям). При тяжелых регламентных операциях закрытия месяца это приводит к замедлению выборки остатков.
Особое внимание уделим особенностям работы Microsoft SQL Server 2014 с базами 1С Предприятия:
#tt* с гигантскими таблицами _AccRgED оптимизатор грубо ошибается в числе ожидаемых строк и строит неверный план соединения (например, выбирает Nested Loops с полным сканированием вместо Hash Match). Рекомендуется включить флаг трассировки 9481 (использование старого оптимизатора кардинальности) или понизить Compatibility Level базы данных до уровня SQL Server 2012 (значение 110).DBCC TRACEON(2453, -1). Он заставляет оптимизатор автоматически рекомпилировать планы запросов при существенном изменении данных во временных таблицах, куда 1С помещает списки номенклатуры и счетов.Max Degree of Parallelism выставлен в 1, либо порог Cost Threshold for Parallelism поднят до значений 50–60. Параллельное сканирование таблицы субконто несколькими потоками при проведении документов перегружает дисковую подсистему и вызывает взаимные ожидания CXPACKET.DBCC FREEPROCCACHE.Если в базе накопилась 5–7-летняя история с сотнями миллионов записей аналитики, техническая оптимизация дает временный эффект. Единственным устойчивым решением является свертка информационной базы: