Почему тормозит запрос контроля остатков при проведении документа и как его оптимизировать

← На главную

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

Анализ проблем в исходном запросе

Проанализируем исходный вариант запроса, вызывающий серьезные задержки при проведении реализации:


ВЫБРАТЬ
    РеализацияТоваровУслугТовары.Ссылка.Склад,
    РеализацияТоваровУслугТовары.Номенклатура,
    СУММА(РеализацияТоваровУслугТовары.Количество * РеализацияТоваровУслугТовары.Коэффициент) КАК Необходимо,
    СУММА(ТоварыВРезервеНаСкладахОстатки.КоличествоОстаток) КАК ВРезерве,
    СУММА(ТоварыНаСкладахОстатки.КоличествоОстаток) КАК НаСкладе
ИЗ
    Документ.РеализацияТоваровУслуг.Товары КАК РеализацияТоваровУслугТовары
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыВРезервеНаСкладах.Остатки(
        &ДатаДок,
        Склад = &Склад И Организация = &Организация) КАК ТоварыВРезервеНаСкладахОстатки
    ПО РеализацияТоваровУслугТовары.Номенклатура = ТоварыВРезервеНаСкладахОстатки.Номенклатура
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(
        &ДатаДок,
        Склад = &Склад И Организация = &Организация) КАК ТоварыНаСкладахОстатки
    ПО РеализацияТоваровУслугТовары.Номенклатура = ТоварыНаСкладахОстатки.Номенклатура
ГДЕ
    РеализацияТоваровУслугТовары.Ссылка = &Ссылка
СГРУППИРОВАТЬ ПО
    РеализацияТоваровУслугТовары.Номенклатура,
    РеализацияТоваровУслугТовары.Ссылка.Склад,
    РеализацияТоваровУслугТовары.НомерСтроки
ДЛЯ ИЗМЕНЕНИЯ

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

Отсутствие фильтра по номенклатуре в виртуальных таблицах. Виртуальные таблицы остатков ТоварыВРезервеНаСкладах.Остатки и ТоварыНаСкладах.Остатки рассчитывают остатки абсолютно по всей номенклатуре выбранного склада и организации. Только после расчета всей массы данных СУБД начинает соединять их со строками конкретного документа. Если в базе десятки тысяч позиций номенклатуры, СУБД выполняет колоссальный объем лишней работы.
Неявные соединения через точку. Конструкция РеализацияТоваровУслугТовары.Ссылка.Склад заставляет платформу выполнять дополнительное неявное левое соединение табличной части документа с таблицей заголовка документа (шапкой). Значение склада известно заранее, его необходимо передавать через параметр запроса &Склад.
Группировка по номеру строки. Указание РеализацияТоваровУслугТовары.НомерСтроки в секции СГРУППИРОВАТЬ ПО делает агрегацию бесполезной. Если одна и та же номенклатура указана в документе несколькими строками, строки не свернутся, что приведет к дублированию данных и некорректным расчетам при соединениях.
Множественные левые соединения виртуальных таблиц. Соединение нескольких виртуальных таблиц между собой «на лету» без предварительной фильтрации порождает неоптимальные планы выполнения (высокая стоимость операторов Hash Match и Nested Loops).
Конструкция ДЛЯ ИЗМЕНЕНИЯ при управляемых блокировках. Указание директивы ДЛЯ ИЗМЕНЕНИЯ в запросе эффективно только при автоматическом режиме блокировок (накладывает блокировку обновления UPDLOCK). В режиме управляемых блокировок данная конструкция игнорируется платформой, и для предотвращения параллельного списания в минус требуется явная установка блокировок через объект БлокировкаДанных.

Способ 1. Использование временных таблиц с фильтрацией остатков

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

Посмотрим на пример оптимизированного пакетного запроса:


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

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

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

// 4. Финальное соединение подготовленных временных таблиц
ВЫБРАТЬ
    Товары.Номенклатура КАК Номенклатура,
    Товары.Необходимо КАК Необходимо,
    ЕСТЬNULL(ОстаткиСклад.НаСкладе, 0) КАК НаСкладе,
    ЕСТЬNULL(ОстаткиРезерв.ВРезерве, 0) КАК ВРезерве,
    ЕСТЬNULL(ОстаткиСклад.НаСкладе, 0) - ЕСТЬNULL(ОстаткиРезерв.ВРезерве, 0) КАК СвободныйОстаток
ИЗ
    ВТ_ТоварыДокумента КАК Товары
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОстаткиСклад КАК ОстаткиСклад
        ПО Товары.Номенклатура = ОстаткиСклад.Номенклатура
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОстаткиРезерв КАК ОстаткиРезерв
        ПО Товары.Номенклатура = ОстаткиРезерв.Номенклатура

Обратим внимание на ключевые правила, примененные здесь:

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

Способ 2. Паттерн «ОБЪЕДИНИТЬ ВСЕ» для исключения тяжелых соединений

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

Рассмотрим подробнее реализацию этого механизма:


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

ОБЪЕДИНИТЬ ВСЕ

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

ОБЪЕДИНИТЬ ВСЕ

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

// Выбираем только позиции, по которым возник дефицит
ВЫБРАТЬ
    Баланс.Номенклатура КАК Номенклатура,
    -СУММА(Баланс.Количество) КАК Нехватка
ИЗ
    ВТ_Баланс КАК Баланс
СГРУППИРОВАТЬ ПО
    Баланс.Номенклатура
ИМЕЮЩИЕ
    СУММА(Баланс.Количество) < 0

Преимущество такого подхода заключается в том, что СУБД выполняет линейное сканирование и потоковую группировку (Stream Aggregate), полностью избавляясь от накладных расходов на операторы соединения JOIN и работу с NULL-значениями.

Важные нюансы работы с виртуальными таблицами и блокировками

Для достижения максимальной скорости проведения необходимо также проверить сопутствующие настройки платформы и СУБД:

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

Управляемые блокировки. Если конфигурация работает в режиме управляемых блокировок, директиву ДЛЯ ИЗМЕНЕНИЯ в запросе использовать бессмысленно. Перед выполнением проверки остатков необходимо программно установить исключительную блокировку на используемые комбинации измерений регистров через объект БлокировкаДанных:


Блокировка = Новый БлокировкаДанных;
ЭлементБлокировки = Блокировка.Добавить("РегистрНакопления.ТоварыНаСкладах");
ЭлементБлокировки.Режим = РежимБлокировкиДанных.Исключительный;
ЭлементБлокировки.УстановитьЗначение("Склад", Склад);
ЭлементБлокировки.УстановитьЗначение("Организация", Организация);
ЭлементБлокировки.ИсточникДанных = ТабличнаяЧастьТовары;
ЭлементБлокировки.ИспользоватьСообщения(Ложь);
ЭлементБлокировки.ДляКаждойСтрокиПередатьПоле("Номенклатура", "Номенклатура");
Блокировка.Заблокировать();

Это исключит конфликты параллельных транзакций и предотвратит появление отрицательных остатков.