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

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

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

Почему запросы в цикле — это грубая ошибка?

Проанализируем механику работы платформы 1С и СУБД. Каждый раз, когда мы вызываем запрос к базе данных из кода, инициализируется обращение сервера 1С к серверу управления базами данных (СУБД). Что при этом происходит:

1. Тратится время на установку соединения и передачу контекста запроса.
2. Сервер СУБД выполняет компиляцию плана запроса (или тратит ресурсы на поиск уже скомпилированного плана в кэше).
3. Происходит физическое чтение данных с диска или из оперативной памяти сервера БД.
4. Результат передается по сети обратно на сервер 1С.

Если мы помещаем запрос в цикл, все эти накладные расходы умножаются на количество итераций. Если у нас 1000 покупателей, сервер выполнит 1000 запросов. Итоговое время выполнения задачи возрастает экспоненциально, база данных начинает "тормозить", возникают блокировки, и другие пользователи ощущают замедление работы системы. Именно поэтому от запросов в цикле необходимо избавляться, перенося всю логику выборки и подсчета на сторону СУБД.

Решение проблемы: Левое соединение и группировка

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

Проанализируем итоговый рабочий код запроса:

ВЫБРАТЬ
    Покупатели.Ссылка КАК Ссылка,
    КОЛИЧЕСТВО(Тточка.Ссылка) КАК КоличествоТочек,
    ПОДСТРОКА(Покупатели.ИНН, 0, 20) КАК ИНН,
    ПОДСТРОКА(Покупатели.Адрес, 0, 200) КАК Адрес
ИЗ
    Справочник.Покупатели КАК Покупатели
        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Тточка КАК Тточка
        ПО Тточка.Контрагент = Покупатели.Ссылка
ГДЕ
    Покупатели.ИНН ПОДОБНО &ИНН

СГРУППИРОВАТЬ ПО
    Покупатели.Ссылка,
    ПОДСТРОКА(Покупатели.ИНН, 0, 20),
    ПОДСТРОКА(Покупатели.Адрес, 0, 200)

В данном примере мы берем основную таблицу покупателей и присоединяем к ней таблицу торговых точек по ссылке контрагента. При группировке по ссылке покупателя, функция КОЛИЧЕСТВО(Тточка.Ссылка) подсчитает только те строки из правой таблицы, которые успешно соединились. Если у покупателя нет торговых точек, мы получим ноль, что нас полностью устраивает.

Дополнительная оптимизация: как сделать код еще лучше

Выясним причину, по которой представленный выше запрос хоть и работает, но не является на 100% идеальным. Рассмотрим несколько критически важных моментов для повышения производительности.

1. Правильная работа с фильтром по ИНН (Равенство вместо ПОДОБНО)

В тексте запроса используется условие Покупатели.ИНН ПОДОБНО &ИНН. Использование оператора ПОДОБНО оправдано только в том случае, когда вы ищете по маске или части строки (например, с использованием спецсимвола %). Если вам нужен поиск по конкретному ИНН, необходимо использовать оператор точного равенства =. Оператор ПОДОБНО часто лишает СУБД возможности использовать индексы при поиске, заставляя сервер выполнять медленное полное сканирование всей таблицы (Table Scan или Clustered Index Scan).

2. Пакетная обработка и использование условия "В"

Если в вашем сценарии изначально есть некий список (массив) значений ИНН, ни в коем случае не выполняйте запрос для каждого элемента массива отдельно. Вместо этого передайте весь массив как один параметр в запрос и используйте условие ГДЕ Покупатели.ИНН В (&МассивИНН). Для очень больших списков оптимальнее загрузить их во временную таблицу с помощью объекта МенеджерВременныхТаблиц и уже эту временную таблицу соединять с основным запросом.

3. Проблема строковых функций в группировках

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

Чтобы избежать этой проблемы, правильнее использовать оператор приведения типа ВЫРАЗИТЬ. Перепишем часть запроса:

ВЫБРАТЬ
    Покупатели.Ссылка КАК Ссылка,
    КОЛИЧЕСТВО(Тточка.Ссылка) КАК КоличествоТочек,
    ВЫРАЗИТЬ(Покупатели.ИНН КАК Строка(20)) КАК ИНН,
    ВЫРАЗИТЬ(Покупатели.Адрес КАК Строка(200)) КАК Адрес
...

4. Использование индексов для быстрых соединений

Посмотрим на условие соединения таблиц: ПО Тточка.Контрагент = Покупатели.Ссылка. Для того чтобы это левое соединение выполнялось моментально даже на таблицах с миллионами записей, поле, по которому происходит связывание в присоединенной таблице (в нашем случае реквизит Контрагент в справочнике Торговых точек), обязательно должно быть проиндексировано. Зайдите в конфигуратор, откройте свойства реквизита Контрагент и установите свойство "Индексировать" в значение "Индексировать" или "Индексировать с доп. упорядочиванием". Платформа 1С автоматически создает индексы для ссылочных полей самой таблицы (поле Ссылка), но для пользовательских реквизитов вы должны настраивать индексы самостоятельно.

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