Как быстро найти пропущенные коды номенклатуры и оптимизировать их генерацию в 1С 7.7?

← На главную

В процессе эксплуатации крупных баз данных на платформе 1С:Предприятие 7.7 разработчики часто сталкиваются с проблемой производительности при генерации новых кодов. Особенно остро этот вопрос стоит в торговых компаниях, где используется так называемый "короткий код" (или быстрый код) номенклатуры. Такой код обычно состоит из 5–6 цифр, печатается на ценниках и используется продавцами и кассирами для быстрого поиска товаров без использования сканера штрихкодов.

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

Анализ неэффективного алгоритма: почему база зависает?

Рассмотрим исходный алгоритм, который использовался в системе изначально и приводил к зависанию программы более чем на 5 минут при объеме справочника всего в 50 000 элементов:


Пока 1=1 Цикл
    Если СпрНом.НайтиПоРеквизиту("НомКод",НовыйНомер,1)=1 Тогда
        Если ФлагСпецГруппы=1 Тогда
            ПредНомер         = ПредНомер+1;
            НовыйНомер        = ПредНомер + 1;
            ДлнНовыйНомер     = СтрДлина(НовыйНомер);
            НовыйНомер        = Лев("М00000000",9-ДлнНовыйНомер)+НовыйНомер;
        Иначе
            ПредНомер         = Число(ПредНомер) + 1;
            НовыйНомер        = СокрЛП(Строка(Число(ПредНомер) + 1));
            ДлнНовыйНомер     = СтрДлина(НовыйНомер);
            НовыйНомер        = Лев("00000",5-ДлнНовыйНомер)+НовыйНомер;
        КонецЕсли;
    Иначе
        Сообщить("Новый код: "+НовыйНомер);
        Прервать;
    КонецЕсли;
КонецЦикла;

Выясним причину столь низкой скорости работы этого фрагмента кода. Здесь мы видим классический цикл Пока 1=1, внутри которого на каждой итерации вызывается метод НайтиПоРеквизиту.

Каждый такой вызов в цикле заставляет систему выполнять отдельный запрос к базе данных (к таблице справочника номенклатуры). Если свободный код находится, например, в тридцатой тысяче, система выполнит 30 000 последовательных запросов к СУБД. Это порождает колоссальные накладные расходы на сетевой обмен, парсинг SQL-запросов сервером и дисковые операции ввода-вывода. С ростом справочника время ожидания растет экспоненциально, превращая работу пользователей в кошмар.

Решение проблемы с помощью прямого SQL-запроса (1С++)

Для кардинального ускорения процесса нам необходимо отказаться от поштучного перебора элементов в цикле 1С и перенести всю вычислительную нагрузку на сторону СУБД SQL Server. Сделать это в 1С 7.7 проще всего с помощью внешней компоненты 1С++, которая позволяет выполнять прямые SQL-запросы к таблицам базы данных.

Идея эффективного SQL-запроса заключается в поиске такого существующего кода, для которого код, увеличенный на единицу (Код + 1), отсутствует в базе данных. Это и будет первая свободная "дыра" в нашей нумерации.

Рассмотрим и проанализируем готовый SQL-запрос, который решает эту задачу всего за 1 секунду:


Select  
    MAX(OneQuery.NomCode)   as max_number,
    MIN(OneQuery.NomCode-1) as result
FROM
    (Select                    
        CAST(Спр.SP7835 as int)    as NomCode
    FROM
        $Справочник.Номенклатура as Спр      
    WHERE    
        LEFT(Спр.SP7835,1)!='М' and Спр.IsFolder = 2 
    ) as OneQuery

LEFT JOIN

    (Select                    
        CAST(Спр.SP7835 as int) as NomCode    
    FROM
        $Справочник.Номенклатура as Спр      
    WHERE    
        LEFT(Спр.SP7835,1)!='М' and Спр.IsFolder = 2
    ) as OneQuery2  

    ON OneQuery.NomCode = OneQuery2.NomCode+1
WHERE        
    OneQuery2.NomCode Is Null and OneQuery.NomCode!=0

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

Мы создаем два идентичных подзапроса (OneQuery и OneQuery2), которые выбирают все коды номенклатуры, исключая группы (IsFolder = 2) и элементы специальной группы, код которых начинается на букву 'М'.
Поскольку поле кода в 1С 7.7 является строковым, мы преобразуем его к целочисленному типу с помощью функции CAST(Спр.SP7835 as int). Это необходимо для выполнения арифметических операций. Обратите внимание, что вместо системного имени поля в реальном коде 1С++ лучше использовать конструкцию метаданных $Справочник.Номенклатура.НомКод.
Мы связываем эти две виртуальные таблицы левым соединением (LEFT JOIN) по условию равенства: текущий код равен предыдущему коду плюс один (OneQuery.NomCode = OneQuery2.NomCode+1).
В секции WHERE мы фильтруем результат, оставляя только те записи, для которых левое соединение не нашло соответствия (OneQuery2.NomCode Is Null). Это означает, что число, предшествующее текущему коду, свободно.
С помощью функции MIN(OneQuery.NomCode-1) мы получаем самый первый (минимальный) свободный номер из всех найденных пропусков.

Оптимизация SQL-запроса для высоконагруженных систем

Несмотря на то, что предложенный выше запрос работает быстро (около 1 секунды на 50 000 элементов), у него есть скрытый недостаток с точки зрения масштабируемости. Использование функции приведения типов CAST(... as int) на соединяемых полях лишает SQL Server возможности использовать индексы по полю кода. СУБД вынуждена выполнять полное сканирование таблицы (Table Scan) при каждом запуске.

Если база данных вырастет до сотен тысяч позиций, запрос начнет замедляться. Чтобы избежать этого, рассмотрим альтернативный вариант с использованием конструкции NOT EXISTS, который зачастую работает значительно быстрее на больших объемах данных:


SELECT MIN(CAST(Спр.SP7835 as int)) + 1 AS FreeCode
FROM $Справочник.Номенклатура as Спр
WHERE IsFolder = 2 AND LEFT(Спр.SP7835, 1) != 'М'
  AND NOT EXISTS (
      SELECT 1 
      FROM $Справочник.Номенклатура as Спр2 
      WHERE CAST(Спр2.SP7835 as int) = CAST(Спр.SP7835 as int) + 1
  )

Также, если вы используете современную версию MS SQL Server, можно применить оконную функцию LEAD. Она позволяет найти первую «дыру» всего за один проход по таблице без тяжелого соединения таблицы самой с собой:


SELECT MIN(NomCode) + 1 AS FreeCode
FROM (
    SELECT CAST(SP7835 as int) as NomCode,
           LEAD(CAST(SP7835 as int)) OVER (ORDER BY CAST(SP7835 as int)) as NextCode
    FROM $Справочник.Номенклатура
    WHERE IsFolder = 2 AND LEFT(SP7835, 1) != 'М'
) t
WHERE NextCode - NomCode > 1

Архитектурные риски: распределенные базы (УРБД) и блокировки

Переход на быстрые SQL-запросы решает проблему производительности "здесь и сейчас", однако при автоматическом заполнении пропусков в нумерации на реальном предприятии возникают серьезные архитектурные риски. Проанализируем их подробнее.

1. Проблема коллизий в распределенных базах данных (УРБД).
Если ваша сеть состоит из нескольких магазинов с распределенными базами, заполнение дыр становится крайне опасным. Представим ситуацию: Магазин 1 и Магазин 2 работают автономно. В обеих базах есть пропущенный код 00023. Операторы в обоих магазинах одновременно создают новый товар. Локальные SQL-запросы находят один и тот же свободный код 00023 и присваивают его разным товарам. При первой же синхронизации баз произойдет конфликт уникальности: либо обмен заблокируется с ошибкой, либо один товар перезапишет другой.

Как решить проблему коллизий в УРБД:

Разделение диапазонов кодов по узлам. Закрепите за каждым магазином свой уникальный диапазон номеров. Например, центральный офис создает коды от 10000 до 19999, первый филиал — от 20000 до 29999, второй — от 30000 до 39999. В таком случае SQL-запрос на каждом узле должен искать свободные дыры строго в пределах своего выделенного диапазона.
Централизованная генерация. Запретите создавать новые карточки номенклатуры на периферийных узлах. Новые позиции должны заводиться только в центральной базе и реплицироваться на филиалы.

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

Как минимизировать проблему конкурентного доступа:

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

Создание отдельной таблицы "очереди свободных кодов"

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

Идея заключается в следующем:

Создается отдельная плоская таблица (или простой справочник) ОчередьСвободныхКодов.
В ночное время, когда нагрузка на базу минимальна, регламентное задание запускает SQL-скрипт, который находит все пропущенные коды в справочнике номенклатуры и записывает их в эту таблицу.
При создании новой номенклатуры система делает мгновенный запрос:
```
SELECT TOP 1 СвободныйКод FROM ОчередьСвободныхКодов ORDER BY СвободныйКод
```
Полученный код присваивается номенклатуре, после чего запись немедленно удаляется из очереди. Эта операция выполняется за сотые доли миллисекунды и полностью исключает задержки для пользователей. Если очередь пуста, система просто берет значение МаксимальныйКод + 1.

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

← На главную