Почему тормозит запрос к виртуальной таблице СрезПоследних и как его ускорить?

← На главную

При переходе с файловой базы на клиент-серверный вариант (MS SQL Server) разработчики часто сталкиваются с ситуацией, когда типовые и доработанные документы начинают проводиться крайне медленно — проведение может занимать от нескольких минут до десятков минут. Замер производительности в таких случаях показывает, что львиная доля времени уходит на выполнение простого запроса к виртуальной таблице СрезПоследних периодического регистра сведений (например, регистра цен или курсов валют).

Разберем природу возникновения проблемы, выясним, почему оптимизатор СУБД строит неоптимальные планы выполнения, и рассмотрим пошаговые методы исправления ситуации.

Причины падения производительности виртуальных таблиц в СУБД

В файловом режиме движок 1С выполняет выборки по собственным алгоритмам, однако в клиент-серверном варианте обращение к виртуальной таблице СрезПоследних срез транслируется в сложный T-SQL запрос. Платформа разворачивает виртуальную таблицу в подзапрос с группировкой по измерениям, поиском максимальной даты и последующим соединением с основной таблицей регистра.

Проанализируем основные факторы, вызывающие зависание:

Неудачный порядок измерений в регистре сведений. Порядок полей в основном кластерном индексе таблицы СУБД формируется строго в том порядке, в котором измерения расположены в дереве конфигурации. Если первым идет измерение с низкой селективностью (например, Склад, где всего 3–5 значений), а вторым — высокоселективное измерение (Номенклатура), то при отборе по списку номенклатуры SQL Server не может эффективно применить поиск по индексу (Index Seek) и переходит к полному сканированию индекса (Index Scan).
Эффект Parameter Sniffing (кэширование неоптимального плана). При первом вызове запроса с малым количеством параметров оптимизатор компилирует план, оптимизированный под оператор Nested Loops. При передаче более объемного списка позиций СУБД повторно использует старый план, что приводит к многократному квадратичному росту накладных расходов.
Ограничения механизма итогов среза последних. Начиная с платформы 8.3, для непериодических и периодических регистров существует свойство ИтогиПоСрезуПоследних. Однако встроенные таблицы итогов ускоряют получение данных только на текущую дату (без передачи параметра даты или на дату заведомо больше последней записи). При получении исторических данных на дату документа расчет всегда выполняется «на лету» по всей таблице регистра.

Способ 1. Изменение порядка измерений в метаданных регистра

Самое быстрое и эффективное решение проблемы, не требующее переписывания алгоритмов в модулях, — перестроение кластерного индекса таблицы регистра.

Разберем по шагам, как это сделать:

Откроем конфигуратор и перейдем к проблемному регистру сведений (например, ЦеныАТТ).
Посмотрим на список измерений: если измерение Номенклатура находится ниже других измерений (например, после измерения Склад), переместим Номенклатура на самую первую позицию с помощью стрелок на панели.
Сохраним и обновим конфигурацию базы данных. При реструктуризации платформа перестроит кластерный индекс таблицы, поместив ключевое поле в начало.
Выполним тестирование и исправление или реиндексацию таблиц на стороне СУБД для обновления статистики.

После перемещения наиболее селективного поля на первое место оптимизатор SQL Server сможет выполнять быстрый поиск по индексу, что сокращает время выполнения среза с нескольких минут до долей секунды.

Способ 2. Оптимизация условий с использованием временных таблиц

Конструкция В (&СписокНоменклатуры) с передачей динамического массива ссылок транслируется в T-SQL условие IN (...). При большом количестве элементов оптимизатору трудно построить единый стабильный план. Чтобы стабилизировать план выполнения, заменим прямую передачу массива на временную таблицу с явным индексированием.

Рассмотрим пример правильной структуры запроса:


ВЫБРАТЬ
    тзНоменклатура.Ссылка КАК Номенклатура
ПОМЕСТИТЬ ВТ_Номенклатура
ИЗ
    &МассивНоменклатуры КАК тзНоменклатура
ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ЦеныПродажные.Склад КАК Склад,
    ЦеныПродажные.Номенклатура КАК Номенклатура,
    ЦеныПродажные.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    ЦеныПродажные.Цена КАК Цена
ИЗ
    РегистрСведений.ЦеныАТТ.СрезПоследних(&Дата, Склад В (&СписокСкладов)) КАК ЦеныПродажные
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Номенклатура КАК ВТ_Номенклатура
        ПО ЦеныПродажные.Номенклатура = ВТ_Номенклатура.Номенклатура

При таком подходе фильтрация по номенклатуре выносится в соединение с предварительно проиндексированной таблицей, что позволяет СУБД выбрать эффективный оператор соединения (Hash Match или Merge Join).

Способ 3. Перенос отбора из параметров виртуальной таблицы в секцию ГДЕ

В типовых рекомендациях 1С всегда предписывается помещать отборы внутрь параметров виртуальной таблицы. Однако в специфических случаях, когда оптимизатор СУБД сбоит внутри подзапроса генерации среза, перенос одного из некластерных отборов в секцию ГДЕ позволяет разгрузить подзапрос группировки:


ВЫБРАТЬ
    ЦеныПродажные.Склад КАК Склад,
    ЦеныПродажные.Номенклатура КАК Номенклатура,
    ЦеныПродажные.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    ЦеныПродажные.Цена КАК Цена
ИЗ
    РегистрСведений.ЦеныАТТ.СрезПоследних(&Дата, Склад В (&СписокСкладов)) КАК ЦеныПродажные
ГДЕ
    ЦеныПродажные.Номенклатура В (&СписокНоменклатуры)

Важно: Данный прием следует использовать с осторожностью и только после замеров производительности, так как при неверном соотношении объема данных он может увеличить объем считываемых строк.

Способ 4. Реализация ручного среза через поиск максимального периода

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

Разберем структуру ручного среза:


ВЫБРАТЬ
    Цены.Номенклатура КАК Номенклатура,
    Цены.Склад КАК Склад,
    Цены.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    МАКСИМУМ(Цены.Период) КАК Период
ПОМЕСТИТЬ ВТ_МаксДаты
ИЗ
    РегистрСведений.ЦеныАТТ КАК Цены
ГДЕ
    Цены.Период <= &Дата
    И Цены.Номенклатура В (&СписокНоменклатуры)
    И Цены.Склад В (&СписокСкладов)
СГРУППИРОВАТЬ ПО
    Цены.Номенклатура,
    Цены.Склад,
    Цены.ХарактеристикаНоменклатуры
ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    Склад,
    ХарактеристикаНоменклатуры,
    Период
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Цены.Склад КАК Склад,
    Цены.Номенклатура КАК Номенклатура,
    Цены.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    Цены.Цена КАК Цена
ИЗ
    ВТ_МаксДаты КАК МаксДаты
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныАТТ КАК Цены
        ПО МаксДаты.Номенклатура = Цены.Номенклатура
            И МаксДаты.Склад = Цены.Склад
            И МаксДаты.ХарактеристикаНоменклатуры = Цены.ХарактеристикаНоменклатуры
            И МаксДаты.Период = Цены.Период

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