При работе с базами данных 1С часто возникает задача оптимизации запросов к регистрам накопления остатков. Рассмотрим типичную ситуацию: в конфигураторе создан регистр накопления с четырьмя измерениями в следующем порядке: Склад, Номенклатура, Характеристика, Серия. Если разработчику требуется часто выполнять запросы с условием сразу по двум измерениям — Номенклатура и Серия — без указания склада, возникает проблема низкой производительности.
Выясним, как платформа 1С строит индексы для регистров накопления, почему стандартное свойство Индексировать не всегда решает проблему и какие способы оптимизации существуют.
Для регистра накопления остатков платформа автоматически создает в базе данных СУБД таблицу итогов. Для этой таблицы формируется главный кластерный индекс. Порядок колонок в этом индексе строго соответствует порядку измерений, заданному в дереве конфигуратора:
Период + Измерение1 + Измерение2 + Измерение3 + Измерение4
В рассматриваемом примере кластерный индекс будет иметь следующую структуру:
Период + Склад + Номенклатура + Характеристика + Серия
Если запрос выполняет поиск по условию Номенклатура = &Номенклатура И Серия = &Серия, но пропускает первое ведущее измерение (Склад), СУБД не может применить точечный поиск по индексу (Index Seek). Из-за пропущенного поля в цепочке составного индекса СУБД вынуждена сканировать весь индекс или таблицу (Index Scan / Table Scan), что критически снижает скорость выполнения запроса на больших объемах данных.
В конфигураторе 1С для любого измерения можно установить свойство Индексировать. Однако важно понимать, что платформа при этом не создает составной индекс по нескольким полям. Для каждого такого измерения создается отдельный некластерный индекс по одному полю.
При получении остатков из виртуальной таблицы СУБД часто игнорирует такие одиночные индексы, так как они не содержат данных о ресурсах (остатках) и требуют дополнительных операций обращения к основной таблице (Bookmark Lookup). В результате одиночные индексы не обеспечивают требуемой производительности для сложных условий.
Разберем по шагам проверенные решения этой проблемы средствами платформы 1С и СУБД.
Самый простой и правильный подход — поднять наиболее часто используемые в фильтрах измерения наверх списка. Если переместить Номенклатуру и Серию на первые позиции, порядок измерений станет следующим: Номенклатура, Серия, Склад, Характеристика.
Платформа пересоздаст кластерный индекс в виде Период + Номенклатура + Серия + ..., и поиск по этой паре полей будет вызывать быстрый Index Seek.
Если изменить порядок измерений невозможно из-за специфики других критичных запросов, можно передавать фильтр по ведущему измерению. Условие передается непосредственно в параметры виртуальной таблицы остатков:
ВЫБРАТЬ
Остатки.Номенклатура КАК Номенклатура,
Остатки.Серия КАК Серия,
Остатки.КоличествоОстаток КАК КоличествоОстаток
ИЗ
РегистрНакопления.ОстаткиЧегото.Остатки(
&Период,
Склад В (&МассивВсехСкладов)
И Номенклатура = &Номенклатура
И Серия = &Серия) КАК Остатки
Передача массива всех возможных складов позволяет СУБД применить ведущую часть кластерного индекса.
Если требуется выбрать остатки по списку конкретных пар «Номенклатура + Серия», исходные данные предварительно помещают во временную таблицу, индексируют её по этим полям через ИНДЕКСИРОВАТЬ ПО, после чего связывают с виртуальной таблицей остатков.
На уровне СУБД (MS SQL Server или PostgreSQL) можно вручную создать произвольный составной индекс по нужным колонкам таблицы итогов. Однако этот метод имеет существенный недостаток:
При любой реструктуризации регистра накопления в конфигураторе (например, изменении типа реквизита или добавлении измерения) платформа 1С пересоздает физическую таблицу базы данных. В этот момент все пользовательские индексы, созданные непосредственно в СУБД, автоматически удаляются.
Если применение стороннего индекса неизбежно, его автоматическое пересоздание организуют на стороне СУБД с помощью DDL-триггеров или регламентных скриптов обслуживания.
Индексы регистра накопления должны быть минимальными и сбалансированными. Настройка порядка измерений в конфигураторе — основной и самый надежный инструмент управления кластерным индексом. Для оптимальной работы запросов к остаткам размещайте наиболее часто используемые в отборах измерения на первых местах в списке измерений регистра.