Работа с таблицами сверхбольшого объема в 1С всегда накладывает жесткие ограничения на используемые алгоритмы. Когда независимый непериодический регистр сведений разрастается до 100 миллионов записей, стандартные методы работы с данными (такие как чтение всей таблицы в память, простые вложенные запросы или перезапись широкими наборами) гарантированно приводят к зависанию сервера, переполнению базы временных таблиц tempdb или аварийному завершению процесса rphost.
В этой статье мы подробно разберем, как решить проблему свертки гигантского регистра (на примере базы кросс-номеров автозапчастей), избавиться от избыточных дублирующих записей, исправить архитектурную ошибку структуры и настроить последующую быструю загрузку новых порций данных объемом более 1 миллиона записей.
---
Часто разработчики совершают классическую ошибку: добавляют в независимый регистр сведений измерение УникальныйИдентификатор (тип УникальныйИдентификатор или строка-строковый UUID) только для того, чтобы платформа «не ругалась» на дубли при массовой загрузке «грязных» данных из внешних файлов.
В нашем случае реальная уникальность записи в базе кроссов определяется бизнес-ключом — комбинацией полей:
Добавление искусственного UUID привело к тому, что в регистре накопилось 35 миллионов скрытых дубликатов (из 100 млн записей уникальными по бизнес-ключу являются только 65 млн). Платформа 1С считает записи с одинаковыми кроссами, но разными UUID уникальными и физически хранит их в СУБД. Это раздувает размер базы данных (вплоть до сотен гигабайт), замедляет поиск аналогов и делает невозможным построение качественных индексов.
Наша цель: очистить дубликаты, удалить искусственное измерение УникальныйИдентификатор и перевести регистр на контроль уникальности по естественному бизнес-ключу средствами самой платформы 1С.
---
Это самый быстрый и надежный способ для таблиц объемом 100 миллионов строк. Выполнение аналогичной операции на стороне 1С может занять десятки часов, тогда как СУБД справится с задачей за минуты.
Рассмотрим пошаговый алгоритм очистки:
ПолучитьСтруктуруХраненияБазыДанных(). С ее помощью находим имя SQL-таблицы нашего регистра (например, она называется _InfoRg12345), а также имена колонок, соответствующих измерениям (например, _Fld123_RRRef для брендов и _Fld124 для артикулов)..ldf) может разрастись до критических размеров и заблокировать весь диск. На время операции в MS SQL Server Management Studio (SSMS) устанавливаем простую модель восстановления.
WITH CTE_Duplicates AS (
SELECT
_Fld121_Type, _Fld121_RTRef, _Fld121_RRRef, -- Бизнес-ключ (Бренд1)
_Fld122, -- Бизнес-ключ (Артикул1)
_Fld123_Type, _Fld123_RTRef, _Fld123_RRRef, -- Бизнес-ключ (Бренд2)
_Fld124, -- Бизнес-ключ (Артикул2)
_Fld125_UUID, -- Наш искусственный УИД
ROW_NUMBER() OVER (
PARTITION BY
_Fld121_RRRef, _Fld122, _Fld123_RRRef, _Fld124
ORDER BY
_Fld125_UUID -- Оставляем запись с минимальным или максимальным UUID
) AS RowNum
FROM
_InfoRg12345
)
DELETE FROM CTE_Duplicates WHERE RowNum > 1;
После успешного выполнения этого запроса СУБД физически удалит все дублирующиеся строки. База очищена, и теперь мы можем безболезненно зайти в конфигуратор 1С, удалить измерение УникальныйИдентификатор и запустить реструктуризацию. Обновление конфигурации базы данных пройдет мгновенно, так как дубликатов больше нет и платформа сможет без ошибок создать новый уникальный кластерный индекс СУБД по бизнес-полям.
---
Если у вас нет прямого доступа к СУБД или политика безопасности запрещает прямые манипуляции с таблицами базы данных, операцию придется выполнять программно в среде 1С. Рассмотрим, как сделать это правильно, избежав критических ошибок.
Попытка выполнить группировку "в лоб" с получением уникальных записей через вложенный запрос или соединение строк приведет к аварийному завершению работы платформы. Проанализируем типичный ошибочный запрос:
// ОШИБОЧНЫЙ ЗАПРОС! Приведет к зависанию на 100 млн записей
Запрос.Текст =
"ВЫБРАТЬ
| БазаАналогов.Артикул1 + БазаАналогов.Бренд1.Наименование КАК УидНю,
| МИНИМУМ(БазаАналогов.Назва) КАК Назва
|ИЗ
| РегистрСведений.БазаАналогов КАК БазаАналогов
|СГРУППИРОВАТЬ ПО
| БазаАналогов.Артикул1 + БазаАналогов.Бренд1.Наименование";
В этом запросе содержатся две критические проблемы:
nvarchar(max) (или аналогичный), из-за чего группировка начинает выполняться на диске в tempdb, замедляя работу в тысячи раз. Решением является использование конструкции ВЫРАЗИТЬ(... КАК СТРОКА(Длина)).Бренд1), а не по их строковым представлениям.Чтобы гарантировать стабильность работы, мы должны обходить регистр небольшими порциями (например, по 50 000–100 000 записей). Самым производительным решением будет обход по физическим индексам регистра.
Проанализируем стратегию разбиения на порции. Будем использовать сортировку по ключевым измерениям (ссылкам и строкам), которые гарантированно проиндексированы платформой. Нам нужно запоминать значения полей последней записи из текущей выборки и передавать их в качестве параметров для получения следующего пакета данных.
Рассмотрим пример кода 1С, реализующего данный подход:
Процедура ВыполнитьПорционнуюСвертку()
// Шаг 1. Создаем копию структуры нашего регистра (без УИД) для записи чистых данных
// Пусть новый регистр называется "РегистрСведений.БазаАналоговЧистый"
РазмерПорции = 100000;
// Начальные значения для фильтрации порций
ПоследнийАртикул1 = "";
ПоследнийБренд1 = Справочники.Производители.ПустаяСсылка();
ПоследнийАртикул2 = "";
ПоследнийБренд2 = Справочники.Производители.ПустаяСсылка();
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ ПЕРВЫЕ " + Формат(РазмерПорции, "ЧГ=0") + "
| Рег.Артикул1 КАК Артикул1,
| Рег.Бренд1 КАК Бренд1,
| Рег.Артикул2 КАК Артикул2,
| Рег.Бренд2 КАК Бренд2,
| МИНИМУМ(Рег.Название) КАК Название
|ИЗ
| РегистрСведений.БазаАналогов КАК Рег
|ГДЕ
| (Рег.Артикул1 > &ПоследнийАртикул1)
| ИЛИ (Рег.Артикул1 = &ПоследнийАртикул1 И Рег.Бренд1 > &ПоследнийБренд1)
| ИЛИ (Рег.Артикул1 = &ПоследнийАртикул1 И Рег.Бренд1 = &ПоследнийБренд1 И Рег.Артикул2 > &ПоследнийАртикул2)
| ИЛИ (Рег.Артикул1 = &ПоследнийАртикул1 И Reg.Бренд1 = &ПоследнийБренд1 И Рег.Артикул2 = &ПоследнийАртикул2 И Рег.Бренд2 > &ПоследнийБренд2)
|СГРУППИРОВАТЬ ПО
| Рег.Артикул1,
| Рег.Бренд1,
| Рег.Артикул2,
| Рег.Бренд2
|УПОРЯДОЧИТЬ ПО
| Артикул1,
| Бренд1,
| Артикул2,
| Бренд2";
Пока Истина Цикл
Запрос.УстановитьПараметр("ПоследнийАртикул1", ПоследнийАртикул1);
Запрос.УстановитьПараметр("ПоследнийБренд1", ПоследнийБренд1);
Запрос.УстановитьПараметр("ПоследнийАртикул2", ПоследнийАртикул2);
Запрос.УстановитьПараметр("ПоследнийБренд2", ПоследнийБренд2);
Выборка = Запрос.Выполнить().Выбрать();
Если Выборка.Количество() = 0 Тогда
Прервать; // Все данные обработаны
КонецЕсли;
// Записываем порцию в новый чистый регистр
НачатьТранзакцию();
Попытка
Пока Выборка.Следующий() Цикл
// Создаем набор для каждой записи или накапливаем массив для пакетной вставки
Набор = РегистрыСведений.БазаАналоговЧистый.СоздатьНаборЗаписей();
Набор.Отбор.Артикул1.Установить(Выборка.Артикул1);
Набор.Отбор.Бренд1.Установить(Выборка.Бренд1);
Набор.Отбор.Артикул2.Установить(Выборка.Артикул2);
Набор.Отбор.Бренд2.Установить(Выборка.Бренд2);
НоваяЗапись = Набор.Добавить();
ЗаполнитьЗначенияСвойств(НоваяЗапись, Выборка);
// Важно отключить бизнес-логику при переносе данных
Набор.ОбменДанными.Загрузка = Истина;
Набор.Записать();
// Запоминаем значения текущей записи для следующей итерации
ПоследнийАртикул1 = Выборка.Артикул1;
ПоследнийБренд1 = Выборка.Бренд1;
ПоследнийАртикул2 = Выборка.Артикул2;
ПоследнийБренд2 = Выборка.Бренд2;
КонецЦикла;
ЗафиксироватьТранзакцию();
Исключение
ОтменитьТранзакцию();
ЗаписьЖурналаРегистрации("СверткаРегистра", УровеньЖурналаРегистрации.Ошибка,,, ПодробноеПредставлениеОшибки(ИнформацияОбОшибке()));
ВызватьИсключение "Ошибка при записи порции данных!";
КонецПопытки;
КонецЦикла;
КонецПроцедуры
Этот алгоритм работает стабильно, не перегружает оперативную память сервера приложений и гарантирует сохранение уникальности за счет группировки в рамках каждой порции.
---
После удаления дубликатов и очистки метаданных перед разработчиком встает новая задача: как регулярно догружать новые файлы (например, по 1-2 млн кросс-номеров) без падения производительности и без возникновения ошибок уникальности СУБД?
Рассмотрим три важнейших инструмента оптимизации загрузки:
Никогда не выполняйте запись импортируемых файлов напрямую в рабочий регистр сведений. Вместо этого используйте промежуточную буферную таблицу. Это может быть:
После того как вся порция в 1 млн записей прочитана из внешнего файла и записана в буферную таблицу, мы выполняем один единственный пакетный запрос, который сопоставляет данные буфера с основным регистром:
// Пример запроса для отсечения существующих записей
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Буфер.Артикул1 КАК Артикул1,
| Буфер.Бренд1 КАК Бренд1,
| Буфер.Артикул2 КАК Артикул2,
| Буфер.Бренд2 КАК Бренд2,
| Буфер.Название КАК Название
|ПОМЕСТИТЬ ВТ_НовыеДанные
|ИЗ
| РегистрСведений.БуферЗагрузки КАК Буфер
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.БазаАналогов КАК ОсновнойРег
| ПО Буфер.Артикул1 = ОсновнойРег.Артикул1
| И Буфер.Бренд1 = ОсновнойReg.Бренд1
| И Буфер.Артикул2 = ОсновнойРег.Артикул2
| И Буфер.Бренд2 = ОсновнойРег.Бренд2
|ГДЕ
| ОсновнойРег.Артикул1 ЕСТЬ NULL"; // Выбираем только то, чего еще нет в основном регистре
Такой подход позволяет отсечь все дублирующиеся записи еще до момента попытки записи в основной регистр, предотвращая блокировки и ошибки СУБД.
При программной записи наборов записей всегда устанавливайте флаг обмена данными:
НаборЗаписей.ОбменДанными.Загрузка = Истина;
Это отключает выполнение любых платформенных проверок, подписок на события и тяжелого кода в модулях наборов записей «ПриЗаписи» и «ПередЗаписью». Скорость вставки возрастает в несколько раз.
Если специфика загрузки требует не просто добавления новых строк, а регулярного обновления сопутствующей информации (например, реквизита Название) для существующих записей, обратите внимание на версию платформы 1С.
В версиях платформы ниже 8.3.27 метод Записать() объекта МенеджерЗаписи при обновлении ресурса всегда выполнял две физические операции в СУБД: DELETE (удаление старой строки) и INSERT (вставка новой). Начиная с версии 8.3.27, если ключевые измерения не изменились, платформа выполняет оптимизированную команду UPDATE. Это значительно снижает нагрузку на дисковую систему хранения данных СУБД и предотвращает избыточное фрагментирование индексов при многомиллионных загрузках.