Разработчики систем на платформе 1С частенько сталкиваются с ситуацией, когда простой запрос к виртуальной таблице СрезПоследних периодического регистра сведений начинает «намертво» зависать. При этом аналогичный запрос вообще без условий (по всему регистру) отрабатывает медленно, но успешно завершается за предсказуемое время. Ситуация кажется парадоксальной: мы накладываем жесткие фильтры, чтобы ограничить выборку, но СУБД вместо ускорения работы уходит в вечное вычисление.
В этой статье мы подробно разберем, почему возникает такое поведение, заглянем под капот СУБД MS SQL Server, проанализируем архитектурные ошибки проектирования регистров сведений и рассмотрим несколько практических способов решения этой проблемы.
Для того чтобы понять, почему зависает запрос, выясним, во что превращается обращение к виртуальной таблице СрезПоследних на уровне СУБД. Платформа 1С не хранит готовый срез последних в реальном времени (если не включена специальная опция, о которой мы поговорим ниже). Каждый раз при обращении к виртуальной таблице платформа генерирует сложный SQL-запрос с подзапросом.
Этот запрос находит максимальную дату (период) для каждого уникального набора измерений, а затем соединяет эту выборку с основной физической таблицей регистра, чтобы получить значения ресурсов.
Когда мы выполняем запрос без параметров, оптимизатор MS SQL Server строит план выполнения, ориентированный на обработку всей таблицы. Обычно используется метод соединения хэшированием (Hash Match) или сканирование индекса (Index Scan). Это тяжелая, но стабильная и линейная операция.
Когда же мы передаем в параметры запроса списки значений (например, массив вагонов В (&Вагон) или номеров документов В (&НомерДокумента)), поведение СУБД кардинально меняется:
Давайте разберем по шагам, как исправить эту ситуацию, начиная от архитектуры и заканчивая кодом запроса.
Проанализируем структуру регистра. Использование строковых типов данных для измерений (например, НомерДокумента со типом Строка или Операция со типом Строка) — это одна из ключевых причин деградации производительности СУБД. Индексировать строковые поля переменной длины крайне неэффективно по следующим причинам:
Правило проектирования: Все измерения регистра сведений должны быть строго ссылочного типа (СправочникСсылка, ДокументСсылка, ПеречислениеСсылка) или числового/булевого типов.
Для оптимизации выполним рефакторинг структуры:
Вагоны и заменим тип измерения Вагон с Числа/Строки на СправочникСсылка.Вагоны.Станции (или перенесем логику на существующий) и заменим тип измерения ПоследняяСтанция на СправочникСсылка.Станции.ТехнологическиеОперации для поля Операция, отказавшись от хранения текстовых названий непосредственно в регистре.НомерДокумента, если оно действительно необходимо для отборов, также следует вынести в отдельный справочник или сделать ресурсом, а не измерением (если уникальность записей не должна определяться номером документа).Такой переход позволит СУБД строить максимально компактные и эффективные индексы, а статистика распределения ключей будет точной на 100%.
Если изменить структуру регистра «здесь и сейчас» невозможно (система находится в промышленной эксплуатации), откажемся от стандартной виртуальной таблицы СрезПоследних. Вместо нее мы напишем трехэтапный запрос с использованием временных таблиц. Этот паттерн гарантирует предсказуемый и быстрый план запроса в СУБД.
Рассмотрим этот алгоритм подробнее на примере языка запросов 1С:
// ШАГ 1: Помещаем массив отбираемых вагонов во временную таблицу и индексируем ее.
// Это позволит СУБД сразу ограничить выборку на уровне физического индекса.
ВЫБРАТЬ
ВагоныПараметры.Ссылка КАК Вагон
ПОМЕСТИТЬ ВТ_ФильтрВагоны
ИЗ
&МассивВагонов КАК ВагоныПараметры
ИНДЕКСИРОВАТЬ ПО
Вагон
;
////////////////////////////////////////////////////////////////////////////////
// ШАГ 2: Получаем максимальные даты (периоды) только по интересующим нас вагонам.
// Соединение с проиндексированной ВТ_ФильтрВагоны исключит сканирование лишних записей регистра.
ВЫБРАТЬ
Дислокация.Вагон КАК Вагон,
МАКСИМУМ(Дислокация.Период) КАК Период
ПОМЕСТИТЬ ВТ_МаксДаты
ИЗ
РегистрСведений.Дислокация КАК Дислокация
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ФильтрВагоны КАК ВТ_ФильтрВагоны
ПО Дислокация.Вагон = ВТ_ФильтрВагоны.Вагон
СГРУППИРОВАТЬ ПО
Дислокация.Вагон
ИНДЕКСИРОВАТЬ ПО
Вагон,
Период
;
////////////////////////////////////////////////////////////////////////////////
// ШАГ 3: Получаем финальные данные среза, соединяя таблицу максимальных дат
// с физической таблицей регистра для получения ресурсов и накладываем остальные фильтры.
ВЫБРАТЬ
ВТ_МаксДаты.Вагон КАК Вагон,
ВТ_МаксДаты.Период КАК Период,
Дислокация.НомерДокумента КАК НомерДокумента,
Дислокация.ПоследняяСтанция КАК ПоследняяСтанция
ИЗ
ВТ_МаксДаты КАК ВТ_МаксДаты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.Дислокация КАК Дислокация
ПО ВТ_МаксДаты.Вагон = Дислокация.Вагон
И ВТ_МаксДаты.Период = Дислокация.Период
ГДЕ
Дислокация.НомерДокумента В (&МассивНомеров)
И Дислокация.ПоследняяСтанция В (&МассивСтанций)
Проанализируем, почему этот вариант работает в десятки раз быстрее. Мы разбили одну сложную логическую задачу для СУБД на три простых изолированных шага. На каждом шаге оптимизатор MS SQL четко видит объем данных во временных таблицах (благодаря индексации временных таблиц в 1С автоматически обновляется статистика для них) и строит идеальный план выполнения без тяжелых вложенных циклов по всей таблице регистра.
Мало кто знает, но начиная с версии платформы 8.3 в 1С появился мощный встроенный механизм оптимизации среза последних. В свойствах периодического регистра сведений есть флаг Разрешить итоги: Срез последних (или просто «Использовать итоги срез последних»).
Давайте выясним, как работает этот механизм под капотом:
СрезПоследних без указания конкретной даты (то есть на текущий момент), платформа 1С больше не выполняет тяжелый поиск MAX(Период). Она делает прямую выборку из физической таблицы итогов.Важный нюанс: Использование итогов среза последних незначительно замедляет запись в регистр (так как теперь обновляются две таблицы вместо одной), но скорость чтения среза последних увеличивается в сотни раз, полностью решая проблему зависаний.
Если в базу данных каждые 30 минут пишется более 2000 новых записей, а регламентные операции на SQL Server выполняются всего один раз в неделю — это гарантированная проблема. По умолчанию MS SQL Server автоматически обновляет статистику только при изменении 20% записей таблицы плюс 500 строк. При объеме регистра в 500 000 записей автоматическое обновление запустится только после накопления 100 500 изменений.
Это означает, что всю неделю СУБД работает с некорректной статистикой, «думая», что в таблице другие данные, и строит ужасающе неэффективные планы запросов.
Для исправления ситуации выполним следующие шаги в консоли MS SQL Server Management Studio (MSSMS):
Full Scan. Если таблица критически важна, можно обновлять статистику только для конкретной таблицы регистра несколько раз в день с помощью SQL-скрипта:
UPDATE STATISTICS _InfoReg12345 WITH FULLSCAN, NORECOMPUTE;
(где _InfoReg12345 — имя таблицы вашего регистра в СУБД).Подведем итоги. Зависание запросов к СрезПоследних — это всегда комплексная проблема, лежащая на стыке некорректной архитектуры данных (строковые измерения), отсутствия регламентов обслуживания СУБД и особенностей трансляции запросов платформой 1С. Применив рефакторинг типов данных, переписав запрос через временные таблицы или включив итоги среза последних, мы обеспечим стабильную и быструю работу системы даже при миллионных объемах данных.