Как оптимизировать запрос получения остатков по штрихкоду в 1С?

← На главную

Часто при разработке рабочего места кассира, обработчиков событий сканера штрихкодов или терминалов сбора данных мы сталкиваемся с задачей: необходимо быстро получить данные о товаре (номенклатуре, SKU) и его текущих остатках на складе по отсканированному штрихкоду. Разберем типичную ситуацию, когда первоначальный запрос работает медленно, проанализируем ошибки и шаг за шагом проведем оптимизацию.

Анализ исходного неоптимального запроса

Посмотрим на пример запроса, который собирает данные по штрихкоду и остаткам:


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

СГРУППИРОВАТЬ ПО
    ТоварыНаСкладахОстатки.Номенклатура,
    КодыТоваровSKU.SKU

На первый взгляд запрос кажется логичным, но он содержит целый ряд грубых архитектурных ошибок, которые заставляют СУБД (будь то MS SQL или PostgreSQL) строить неэффективный план выполнения. Проанализируем каждую проблему подробно.

Проблема 1: Вложенный запрос в параметрах виртуальной таблицы

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

Проблема 2: Избыточная группировка

В запросе используется секция СГРУППИРОВАТЬ ПО. Зададимся вопросом: зачем она здесь нужна? Виртуальная таблица РегистрНакопления.ТоварыНаСкладах.Остатки уже по своей природе возвращает свернутые (сгруппированные) данные в разрезе запрошенных измерений (в нашем случае — номенклатуры). Если в базе нет дублей SKU для одной номенклатуры, результат и так будет уникальным. Дополнительная группировка заставляет сервер баз данных выполнять ресурсоемкие операции (Hash Match или Sort), которые тратят процессорное время впустую.

Проблема 3: Недетерминированная выборка ПЕРВЫЕ 1

В тексте мы видим ВЫБРАТЬ ПЕРВЫЕ 1 без указания сортировки УПОРЯДОЧИТЬ ПО. С точки зрения стандартов SQL, такая выборка является недетерминированной. Если в регистре по какой-то причине окажется несколько владельцев для одного штрихкода (дубли), система при разных вызовах может возвращать разную номенклатуру, просто потому что данные физически легли на разные страницы памяти. Всегда нужно использовать либо сортировку, либо агрегатную функцию (например, максимум).

Проблема 4: Передача даты для получения текущих остатков

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

Шаг к решению: вариант с временной таблицей

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


ВЫБРАТЬ ПЕРВЫЕ 1
    Штрихкоды.Владелец КАК Номенклатура,
    &Штрихкод КАК Штрихкод,
    КодыТоваровSKU.SKU КАК SKU
ПОМЕСТИТЬ ВТ_ШтрихКодВладелец
ИЗ
    РегистрСведений.Штрихкоды КАК Штрихкоды
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.КодыТоваровSKU КАК КодыТоваровSKU
        ПО Штрихкоды.Владелец = КодыТоваровSKU.Номенклатура
ГДЕ
    Штрихкоды.Штрихкод = &Штрихкод
УПОРЯДОЧИТЬ ПО 
    Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_ШтрихКодВладелец.Номенклатура КАК Номенклатура,
    ВТ_ШтрихКодВладелец.Штрихкод КАК Штрихкод,
    ВТ_ШтрихКодВладелец.SKU КАК SKU,
    ЕСТЬNULL(ТоварыНаСкладахОстатки.КоличествоОстаток, 0) КАК КоличествоОстаток
ИЗ
    ВТ_ШтрихКодВладелец КАК ВТ_ШтрихКодВладелец
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(, Склад В (&Склады)) КАК ТоварыНаСкладахОстатки
        ПО ВТ_ШтрихКодВладелец.Номенклатура = ТоварыНаСкладахОстатки.Номенклатура

Проблема 5: Отсутствие отбора внутри параметров виртуальной таблицы

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


ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(, Склад В (&Склады)) КАК ТоварыНаСкладахОстатки
ПО ВТ_ШтрихКодВладелец.Номенклатура = ТоварыНаСкладахОстатки.Номенклатура

По стандартам трансляции запросов 1С в SQL, если мы не передаем во вложенный запрос виртуальной таблицы явный отбор по номенклатуре, сервер СУБД выполнит следующее: он рассчитает остатки абсолютно по всей номенклатуре на указанных складах, выгрузит этот огромный массив в оперативную память сервера, и только потом присоединит из него одну нужную строку к нашей временной таблице. Это крайне неэффективно!

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

Финальный оптимизированный вариант запроса

Соберем воедино все правила хорошего тона оптимизации 1С и напишем идеальный запрос:


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

Подведем итоги проделанной работы

Мы избавились от вложенных подзапросов в физических выборках, применив конструкцию ПОМЕСТИТЬ.
Мы удалили лишнюю команду СГРУППИРОВАТЬ ПО, снизив нагрузку на сервер баз данных.
Мы очистили параметр &ТекДата, позволив платформе брать данные из таблицы актуальных итогов мгновенно.
Мы добавили УПОРЯДОЧИТЬ ПО к оператору выбора первой записи для обеспечения детерминированности.
Самое важное: мы пробросили отбор по номенклатуре из временной таблицы прямо в параметры виртуальной таблицы остатков, заставив СУБД считать остаток только по одному конкретному товару.

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