При работе с крупными базами данных под управлением 1С:Управление производственным предприятием 1.3 (УПП), объем которых превышает 1 ТБ, стандартные механизмы проведения документов и восстановления последовательности партионного учета часто становятся главным «узким местом» системы. Время восстановления последовательности перед расчетом себестоимости начинает измеряться сутками, парализуя работу пользователей.
В этой статье мы подробно разберем, как решить проблему медленного проведения по партиям. Мы проанализируем физическую структуру хранения данных в СУБД, рассмотрим методику выборочного сверхчастого обновления статистик MS SQL Server, разберем оптимизацию неоптимальных типовых запросов и обсудим альтернативные архитектурные решения.
Основная причина падения производительности кроется в механике работы оптимизатора MS SQL Server и особенностях типового кода 1С. При восстановлении последовательности система перепроводит огромное количество документов. При этом таблицы регистров партий непрерывно изменяются.
По умолчанию MS SQL Server автоматически обновляет статистику распределения данных только после изменения определенного процента строк (в классическом алгоритме это около 20% + 500 строк). Для таблицы объемом в 50-100 миллионов записей этот порог составляет более 10 миллионов измененных строк. В процессе проведения по партиям СУБД успевает записать сотни тысяч новых документов, распределение данных кардинально меняется, но автообновление статистики не срабатывает. В результате оптимизатор строит неверный план запроса (например, выбирает тяжелый Index Scan вместо быстрого Index Seek), и проведение зависает.
---
Чтобы СУБД всегда владела актуальной информацией о распределении данных в таблицах партионного учета, мы можем настроить регламентное задание на стороне MS SQL Server для точечного обновления статистик. Обновлять всю базу размером более 1 ТБ слишком долго и ресурсоемко, поэтому мы выделим только самые критичные таблицы.
Какие таблицы СУБД необходимо обновлять часто:
ПартииТоваровНаСкладах (управленческий, бухгалтерский и налоговый учет). В СУБД они соответствуют физическим таблицам с префиксами _AccumRegXXXX и _AccumRegTotalsXXXX.НДСПартииТоваров._InfoRegXXXX (соответствует регистру СписанныеТовары). В этот регистр временно записываются данные о товарах, подлежащих списанию в текущей транзакции. Эта таблица постоянно очищается и заполняется заново, из-за чего статистика по ней мгновенно устаревает.Выясним физические имена таблиц в вашей СУБД с помощью функции платформы 1С ПолучитьСтруктуруХраненияБазыДанных() или специализированных обработок. После этого настроим SQL-скрипт, который будет запускаться во время проведения партий каждые 15–30 минут.
Ниже представлен пример SQL-скрипта для выборочного обновления статистики и точечной очистки процедурного кэша для конкретных таблиц:
-- Обновление статистики по конкретным "горячим" таблицам регистра партий и списанных товаров
UPDATE STATISTICS _AccumReg12345 WITH FULLSCAN, NORECOMPUTE;
UPDATE STATISTICS _AccumRegTotals12345 WITH FULLSCAN, NORECOMPUTE;
UPDATE STATISTICS _InfoReg67890 WITH FULLSCAN, NORECOMPUTE;
-- Очистка кэша планов запросов, связанных с этими таблицами,
-- чтобы СУБД гарантированно построила новые оптимальные планы
DECLARE @table_id1 INT = OBJECT_ID('_AccumReg12345');
DECLARE @table_id2 INT = OBJECT_ID('_InfoReg67890');
DECLARE @plan_handle VARBINARY(64);
DECLARE plan_cursor CURSOR FOR
SELECT plan_handle
FROM sys.dm_exec_query_plan(NULL) AS qp
CROSS APPLY sys.dm_exec_sql_text(qp.plan_handle) AS st
WHERE st.objectid IN (@table_id1, @table_id2);
OPEN plan_cursor;
FETCH NEXT FROM plan_cursor INTO @plan_handle;
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC FREEPROCCACHE (@plan_handle);
FETCH NEXT FROM plan_cursor INTO @plan_handle;
END;
CLOSE plan_cursor;
DEALLOCATE plan_cursor;
Важно: Использование параметра FULLSCAN гарантирует точную оценку, но на терабайтных базах может создавать нагрузку на дисковую подсистему. Если обновление длится слишком долго, замените FULLSCAN на SAMPLE 20 PERCENT.
---
Даже актуальная статистика не спасет, если сам поисковый запрос написан неоптимально для больших объемов данных. В общем модуле УправлениеЗапасамиПартионныйУчет (в процедурах построения запроса партий) присутствует типовая проблема.
В чем заключается проблема:
В параметрах виртуальной таблицы остатков регистра партий разработчики 1С применили вложенный запрос-фильтр по номенклатуре:
ГДЕ Номенклатура В (
ВЫБРАТЬ СписанныеТовары.Номенклатура
ИЗ СписанныеТовары
ГДЕ СписанныеТовары.Регистратор = &Ссылка
)
Оптимизатор MS SQL Server не может корректно оценить кардинальность (количество возвращаемых строк) такого вложенного запроса в параметрах виртуальной таблицы 1С. СУБД строит неверный план выполнения, полагая, что строк будет очень мало, и начинает сканировать весь индекс физической таблицы остатков партий.
Как правильно переписать этот запрос:
ВТ_СписанныеТовары).Номенклатура, Склад, Характеристика.ВНУТРЕННЕЕ СОЕДИНЕНИЕ проиндексированной временной таблицы с виртуальной таблицей остатков регистра партий.ДокументОприходования, имеющее составной тип данных, обязательно используем функцию ВЫРАЗИТЬ. Без этого платформа генерирует SQL-запрос с десятками LEFT JOIN ко всем таблицам документов в базе, что критически замедляет выполнение на СУБД.Рассмотрим пример оптимизированной структуры запроса:
// Шаг 1: Помещаем списанные товары во временную таблицу и индексируем её
Запрос.Текст =
"ВЫБРАТЬ
| СписанныеТовары.Номенклатура КАК Номенклатура,
| СписанныеТовары.Склад КАК Склад,
| СписанныеТовары.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры
|ПОМЕСТИТЬ ВТ_СписанныеТовары
|ИЗ
| РегистрСведений.СписанныеТовары КАК СписанныеТовары
|ГДЕ
| СписанныеТовары.Регистратор = &Ссылка
|
|ИНДЕКСИРОВАТЬ ПО
| Номенклатура,
| Склад,
| ХарактеристикаНоменклатуры
|;
|
|// Шаг 2: Получаем остатки партий через соединение с временной таблицей
|ВЫБРАТЬ
| ОстаткиПартий.Номенклатура КАК Номенклатура,
| ОстаткиПартий.Склад КАК Склад,
| ОстаткиПартий.ДокументОприходования КАК ДокументОприходования,
| ОстаткиПартий.КоличествоОстаток КАК КоличествоОстаток,
| ОстаткиПартий.СтоимостьОстаток КАК СтоимостьОстаток
|ИЗ
| РегистрНакопления.ПартииТоваровНаСкладах.Остатки(&МоментВремени, ) КАК ОстаткиПартий
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_СписанныеТовары КАК СписанныеТовары
| ПО ОстаткиПартий.Номенклатура = СписанныеТовары.Номенклатура
| И ОстаткиПартий.Склад = СписанныеТовары.Sklad
| И ОстаткиПартий.ХарактеристикаНоменклатуры = СписанныеТовары.ХарактеристикаНоменклатуры";
Эта доработка снижает нагрузку на СУБД в разы, так как оптимизатор MS SQL четко видит размер индексированной временной таблицы и мгновенно находит нужные записи в физических таблицах остатков по индексу.
---
Если оптимизация запросов и обновление статистик не дают желаемой скорости на терабайтной базе, рекомендуем обратить внимание на системные настройки и архитектурные изменения.
1. Многопоточное восстановление последовательности
Стандартное восстановление последовательности в УПП 1.3 выполняется строго в один поток, загружая только одно ядро процессора. На мощных серверах это приводит к простою оборудования. Применение сторонних обработок для многопоточного проведения документов (разделяющих очередь по организациям или независимым группам номенклатуры) позволяет сократить время восстановления последовательности в несколько раз.
2. Оптимизация настроек учетной политики
Проанализируем учетные настройки системы. Часто в базах включены функции, которые не используются бизнесом, но создают колоссальную нагрузку:
3. Переход на РАУЗ (Расширенную аналитику учета затрат)
Для базы объемом более 1 ТБ традиционный партионный учет является технологическим тупиком. Списание «на лету» требует постоянного поддержания актуальности последовательности, что вызывает взаимные блокировки при интенсивной многопользовательской работе.
Переход на РАУЗ полностью решает эту проблему:
Если бизнес-требования позволяют отказаться от партионного учета ФИФО в пользу средней стоимости или ФИФО скользящей оценки по РАУЗ, переход на РАУЗ решит проблему производительности кардинально и навсегда.