Как оптимально получить последнее движение по массиву номенклатуры в 1С?

← На главную

В практической работе разработчика 1С часто возникает задача: быстро получить последние движения (документ-регистратор и дату) по списку номенклатуры. Особую сложность эта задача представляет при работе с регистром бухгалтерии Хозрасчетный, так как субконто здесь хранятся в отдельных таблицах, а объемы данных могут быть колоссальными.

В этой статье мы подробно разберем, почему простые решения «в лоб» работают медленно, разберем частые ошибки разработчиков и построим максимально быстрый и оптимизированный пакет запросов.

Анализ неоптимального решения

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


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

Данный запрос рабочий, но он имеет ряд критических проблем производительности:

Сканирование физической таблицы субконто. Таблица РегистрБухгалтерии.Хозрасчетный.Субконто является физической таблицей значений субконто. Она огромна, и обращение к ней напрямую без жестких ограничений по индексам приводит к высокой нагрузке на СУБД.
Отсутствие индексов во временных таблицах. Временные таблицы ВТ и МаксПериод соединяются между собой. Поскольку они не проиндексированы, СУБД вынуждена выполнять операцию сканирования таблиц (Table Scan) вместо быстрого поиска по индексу (Index Seek).
Отсутствие отбора по активности. В запросе не учитывается флаг Активность. Если в системе есть помеченные на удаление или распроведенные документы, чьи движения физически остались в базе данных с признаком активности ЛОЖЬ, запрос вернет неверные данные.

Подводный камень: почему не работает МАКСИМУМ(МоментВремени)

У многих разработчиков возникает логичная идея: сократить пакет запросов и получить последнюю запись в один шаг с помощью агрегатной функции МАКСИМУМ по полю МоментВремени. На первый взгляд это кажется идеальным решением:


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

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

Правильный обходной путь: Чтобы гарантированно определить самую последнюю запись (даже если несколько документов были проведены в одну и ту же секунду), нам необходимо использовать двухэтапный поиск максимума: сначала находим максимальную дату МАКСИМУМ(Период), а затем для этой даты получаем максимальную ссылку МАКСИМУМ(Регистратор).

Пошаговое построение оптимального запроса

Для достижения максимальной скорости работы мы применим профессиональную методику построения пакета запросов с использованием виртуальной таблицы и индексации временных таблиц.

Рассмотрим весь процесс по шагам.

Шаг 1. Использование виртуальной таблицы ДвиженияССубконто

Вместо обращения к тяжелой физической таблице Субконто, мы задействуем виртуальную таблицу РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто.

Важнейшее правило оптимизации: все отборы по номенклатуре и видам субконто мы должны передавать не в секцию ГДЕ, а непосредственно в параметры виртуальной таблицы (в четвертый параметр «Условие»). Это заставит СУБД отсечь лишние записи на самом раннем этапе построения запроса.

Шаг 2. Обязательное индексирование временных таблиц

При создании любой временной таблицы, которая в дальнейшем будет участвовать в соединениях, мы обязаны использовать конструкцию ИНДЕКСИРОВАТЬ ПО. В нашем случае ключевыми полями для индексации будут Значение (номенклатура) и Период.

Шаг 3. Фильтрация по активности записей

Чтобы исключить влияние непроведенных и сторнированных документов, мы обязательно накладываем фильтр Активность = ИСТИНА.

Оптимальный шаблон пакета запросов

Объединим все правила оптимизации в единый эффективный шаблон запроса:


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

ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    Период
;

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

ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    ПоследнееДвижение,
    Регистратор
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 3. Получаем итоговый результат
ВЫБРАТЬ
    ВТ_ИтоговыеДанные.Номенклатура КАК Номенклатура,
    ВТ_ИтоговыеДанные.ПоследнееДвижение КАК ПоследнееДвижение,
    ВТ_ИтоговыеДанные.Регистратор КАК Регистратор
ИЗ
    ВТ_ИтоговыеДанные КАК ВТ_ИтоговыеДанные

Как это работает изнутри?

Давайте проанализируем, почему этот пакет запросов будет работать в разы быстрее исходного варианта:

В первой временной таблице ВТ_МаксДаты мы собираем только уникальные идентификаторы номенклатуры и даты их последних движений. Объем этой таблицы равен количеству переданных позиций номенклатуры.
Индексируя поля Номенклатура, Период в первой таблице, мы подготавливаем СУБД к мгновенному поиску во втором запросе пакета.
Во втором запросе соединение происходит по индексированным полям. Платформа 1С транслирует это в SQL-запрос с операцией INNER JOIN, которая благодаря индексам отрабатывает за миллисекунды.
Повторный отбор внутри параметров виртуальной таблицы ДвиженияССубконто на втором шаге гарантирует, что СУБД не будет сканировать лишние строки общего регистра.

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