Чем заменить длинную строку в измерении регистра накопления?

← На главную

В процессе доработки конфигураций 1С разработчики нередко сталкиваются с ситуацией, когда бизнес требует детализировать остатки в отчетах по произвольному текстовому описанию — например, по полю Содержание из табличной части документа. Простейшее на первый взгляд решение — добавить в регистр накопления измерение типа Строка(400) — со временем приводит к резкому падению производительности и техническим сбоям. Разберем, почему длинные строки в измерениях опасны для СУБД, и рассмотрим архитектурно правильные способы решения этой задачи.

В чем опасность длинных строк в измерениях регистра

Регистры накопления в платформе 1С построены на механизме агрегации данных. Для каждого регистра остатков платформа формирует в СУБД две основные таблицы: таблицу движений и таблицу итогов. Проанализируем ограничения, с которыми мы сталкиваемся при добавлении длинных строковых полей в измерения:

Превышение предельного размера индекса в СУБД. В таблице итогов платформа создает кластерный индекс по полям периода, разделителей и всех измерений регистра. В Microsoft SQL Server максимальный размер ключа индекса составляет 900 байт. Поле типа Строка(400) в кодировке Unicode (тип nvarchar в СУБД) занимает 800 байт. Добавление к нему измерений номенклатуры, договора или организации гарантированно превышает 900 байт, что приводит к аппаратным ошибкам СУБД при проведении документов и падению реструктуризации базы данных.
Коллапс таблицы итогов. Регистр накопления эффективен только тогда, когда записи сворачиваются, а количество строк в таблице итогов в разы меньше числа движений. Произвольные формулировки услуг менеджерами делают каждое движение уникальным. Таблица итогов разрастается до размера таблицы движений, теряет смысл агрегации и катастрофически замедляет запись любых документов по этому регистру.
Медленные операции сравнения и группировки. СУБД выполняет операции GROUP BY, JOIN и поиск по строкам переменной длины значительно медленнее, чем по фиксированным ссылочным типам (16 байт) или целочисленным идентификаторам.

Способ 1. Партионный учет через документ и номер строки (типовой подход)

Рассмотрим наиболее надежный и принятый в типовых конфигурациях 1С подход. Если строка содержания формируется индивидуально в каждом документе реализации и требуется при списании остатков в отчете комитенту, нам не нужно сохранять сам текст в регистре накопления.

Вместо строкового поля добавим в регистр накопления два компактных измерения:

ДокументРеализации с типом ДокументСсылка.РеализацияТоваровУслуг (фиксированный размер UUID — 16 байт).
НомерСтрокиДокумента с типом Число(5, 0) (занимает от 3 до 4 байт).

При таком подходе регистр накопления хранит остатки в разрезе конкретных строк документов поступления/реализации. Когда формируется Отчет комитенту о продажах, остатки берутся по этим двум измерениям, а само исходное текстовое поле Содержание извлекается соединением с табличной частью документа:


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |	Остатки.ДокументРеализации КАК ДокументРеализации,
    |	Остатки.НомерСтрокиДокумента КАК НомерСтрокиДокумента,
    |	Остатки.КоличествоОстаток КАК Количество,
    |	ТЧ.Содержание КАК Содержание
    |ИЗ
    |	РегистрНакопления.РеализованныеТоварыКомитентов.Остатки(&Период, ) КАК Остатки
    |	ЛЕВОЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг.Услуги КАК ТЧ
    |		ПО Остатки.ДокументРеализации = ТЧ.Ссылка
    |		И Остатки.НомерСтрокиДокумента = ТЧ.НомерСтроки";

Этот метод полностью устраняет нагрузку на таблицу итогов, исключает дублирование строковых данных и не требует создания дополнительных справочников.

Способ 2. Вынесение формулировок в справочник с поиском по хешу

Если специфика учета требует схлопывания одинаковых услуг между разными документами, партионная привязка к строкам может не подойти. В этом случае правильным решением будет создание вспомогательного справочника (например, СодержанияУслуг), ссылка на который выступает измерением регистра накопления.

Однако поиск существующей длинной строки стандартным методом НайтиПоРеквизиту вызовет полное сканирование таблицы (Table Scan) в СУБД. Разберем, как оптимизировать эту операцию с помощью встроенного объекта ХешированиеДанных:

Добавим в справочник реквизит Хеш с типом Строка(32) или Строка(40) и включим свойство Индексировать.
Реквизит ПолноеСодержание сделаем строкой неограниченной длины.
При записи документа вычисляем хеш от введенного текста и выполняем быстрый поиск по индексу.

Посмотрим на пример функции получения ссылки на элемент справочника:


Функция ПолучитьСсылкуНаСодержание(ТекстСодержания) Экспорт
    Хеширование = Новый ХешированиеДанных(ХешФункция.MD5);
    Хеширование.Добавить(СокрЛП(ТекстСодержания));
    СтрокаХеша = Хеширование.ХешСумма; // Возвращает hex-строку фиксированной длины

    Запрос = Новый Запрос;
    Запрос.Текст = 
        "ВЫБРАТЬ ПЕРВЫЕ 1
        |	Спр.Ссылка КАК Ссылка
        |ИЗ
        |	Справочник.СодержанияУслуг КАК Спр
        |ГДЕ
        |	Спр.Хеш = &Хеш
        |	И Спр.ПолноеСодержание = &ПолноеСодержание";
    Запрос.УстановитьПараметр("Хеш", СтрокаХеша);
    Запрос.УстановитьПараметр("ПолноеСодержание", ТекстСодержания);

    Выборка = Запрос.Выполнить().Выбрать();
    Если Выборка.Следующий() Тогда
        Возврат Выборка.Ссылка;
    Иначе
        НовыйЭлемент = Справочники.СодержанияУслуг.СоздатьЭлемент();
        НовыйЭлемент.Наименование = Лев(ТекстСодержания, 100);
        НовыйЭлемент.Хеш = СтрокаХеша;
        НовыйЭлемент.ПолноеСодержание = ТекстСодержания;
        НовыйЭлемент.Записать();
        Возврат НовыйЭлемент.Ссылка;
    КонецЕсли;
КонецФункции

Такой алгоритм работает мгновенно даже при миллионах записей: СУБД мгновенно находит запись по индексированному полю хеша, а проверка совпадения полного текста предотвращает редкие коллизии алгоритма хеширования.

Способ 3. Прямое хранение хеша в измерении регистра

Если создание отдельного справочника нежелательно, можно хранить сам хеш текста непосредственно в измерении регистра накопления. Для этого в измерение помещается поле ХешСодержания типа Число или Строка(32).

Параллельно в табличную часть документа РеализацияТоваровУслуг добавляется такое же скрытое поле хеша. При проведении документа остатки фиксируются по рассчитанному хешу, а при заполнении документа Отчет комитенту о продажах текст восстанавливается сопоставлением по паре полей: Регистратор + ХешСодержания.

Как безопасно перевести работающую базу на новую схему

Прямое удаление старого измерения из регистра через конфигуратор приведет к длительной блокирующей реструктуризации таблиц и потере истории. Чтобы выполнить переход без сбоев, применим следующую последовательность шагов:

Добавим новые измерения (ссылку на документ с номером строки либо ссылку на справочник) в регистр накопления, не удаляя старую строку.

С помощью внешней обработки перезапишем движения прошлых периодов. Для исключения сдвигов партий и перепроведения бухгалтерских проводок изменяем только наборы записей целевого регистра:


Набор = РегистрыНакопления.РеализованныеТоварыКомитентов.СоздатьНаборЗаписей();
Набор.Отбор.Регистратор.Установить(ДокументСсылка);
Набор.Прочитать();

Для Каждого Запись Из Набор Цикл
    // Заполняем новые реквизиты на основании данных документа
    Запись.ДокументРеализации = ДокументСсылка;
    Запись.НомерСтрокиДокумента = НомерСтроки;
КонецЦикла;

Набор.Записать();

Обновим алгоритмы проведения новых документов и логику построения отчетов, переключив их на новые измерения.
В регламентное окно выполним пересчет итогов регистра накопления.
После успешного перехода удалим устаревшее строковое измерение из структуры метаданных.