← На главную

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

Давайте представим следующую ситуацию, которая была описана нашими коллегами: у нас есть справочник Номенклатура, в котором присутствует реквизит UIDВоВнешнейСистеме. Этот реквизит имеет тип «Строка», длину 36 символов, и по сути является уникальным идентификатором, используемым во внешней системе. Из этой внешней системы мы получаем запись, которая содержит несколько (например, от 1 до 30) UID-ов, связанных с одной исходной записью. Нам нужно получить соответствующие записи номенклатуры (их ссылки, наименования, а также дополнительные реквизиты, такие как Артикул) и представить их в виде ТаблицыЗначений на форме. Важно, что справочник Номенклатура со временем может значительно увеличиваться, достигая 5-20 тысяч записей, в то время как список UID-ов для каждого конкретного запроса всегда остается небольшим.

Оптимальные стратегии выполнения запроса и их интерпретация SQL

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

1. Использование временной таблицы со списком UID-ов и последующее соединение со справочником Номенклатура

   Этот подход является наиболее рекомендуемым и эффективным для нашего сценария. Он особенно оправдан, когда список UID-ов, поступающих из внешней системы, невелик (до 30 элементов), а сам справочник «Номенклатура» содержит значительное количество записей (например, от 5 до 20 тысяч).

   Как реализовать в 1С

   Первым шагом мы создадим объект ТаблицаЗначений в 1С. Эту таблицу мы заполним всеми уникальными идентификаторами (UID-ами), которые были получены нами из внешней системы. Затем сформированную ТаблицуЗначений мы передадим в качестве параметра в наш запрос 1С. Рассмотрим пример кода:

   Скопировать код

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

   Почему это правильно и эффективно (с точки зрения SQL)

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

   1. Минимизация объема обрабатываемых данных: Мы не заставляем СУБД работать со всем справочником Номенклатура сразу. Вместо этого мы сначала формируем очень небольшой набор уникальных UID-ов. Когда ТаблицаЗначений передается в запрос 1С, платформа конвертирует ее в объект, который СУБД обрабатывает как временную или виртуальную таблицу. Эта таблица содержит всего несколько десятков записей, что значительно меньше, чем весь справочник.
   2. Эффективное соединение: Затем СУБД выполняет внутреннее соединение между этой небольшой временной таблицей (с нашими до 30 UID-ами) и основной таблицей Справочник.Номенклатура. Это соединение очень эффективно, так как одна из соединяемых сторон (временная таблица) крайне мала.
   3. Максимальное использование индексов: Если реквизит UIDВоВнешнейСистеме справочника Номенклатура правильно проиндексирован (что является критически важным условием для высокой производительности), СУБД сможет найти соответствующие записи очень быстро. Оптимизатор запросов СУБД, анализируя запрос и видя наличие индекса на поле UIDВоВнешнейСистеме, выберет стратегию поиска по индексу (index seek) вместо полного сканирования таблицы (table scan). Поиск по индексу позволяет СУБД моментально перейти к нужным записям, не просматривая каждую строку большой таблицы. Это значительно сокращает время выполнения запроса и снижает нагрузку на дисковую подсистему и процессор.

2. Объединение значений в одном запросе с помощью оператора "В" (ГДЕ ... В (...))

   Данный вариант также является приемлемым и может быть использован, особенно если количество UID-ов, по которым мы хотим выполнить отбор, действительно очень мало (например, от 1 до 5). Однако, если список приближается к 30 элементам, первый вариант с временной таблицей может предложить более предсказуемую и стабильную производительность.

   Как реализовать в 1С

   Для реализации этого подхода нам необходимо собрать все требуемые UID-ы в объект СписокЗначений 1С и затем передать его в наш запрос в качестве параметра. Рассмотрим пример кода:

   Скопировать код

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

   Почему это правильно (с точки зрения SQL)

   1. Оптимизация оператора WHERE IN: Современные системы управления базами данных очень хорошо оптимизируют оператор IN, особенно если количество элементов в списке небольшое и по полю, используемому в условии, существует индекс. Оптимизатор запросов может внутренне преобразовать условие IN в серию условий OR (например, Field = Value1 OR Field = Value2 OR ...) или применить более сложные внутренние стратегии, такие как построение временной хеш-таблицы или использование битовых карт, чтобы эффективно найти совпадающие записи. Ключевым фактором для производительности здесь, как и в первом подходе, является наличие правильно настроенного индекса на поле UIDВоВнешнейСистеме.
   2. Простота написания: Этот подход зачастую более прост в написании и понимании, чем использование временных таблиц, если, конечно, не требуется сложная логика предварительной обработки или модификации списка UID-ов.

Неправильные подходы к запросам и почему их следует избегать

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

1. Многократные самосоединения справочника (как могли быть реализованы изначально): Если первоначальный разработчик реализовал логику получения данных через многократные соединения справочника Номенклатура с самим собой для каждого UID (например, четыре раза для получения трех номенклатур, как описывалось в исходной задаче), это является исключительно неэффективным подходом. При таком сценарии каждое самосоединение будет заставлять СУБД снова и снова просматривать (или индексировать, если есть индекс) всю таблицу. Это многократно увеличивает нагрузку на сервер СУБД, приводит к избыточным операциям ввода/вывода, увеличивает использование оперативной памяти и существенно замедляет время выполнения запроса. Вместо одной операции по поиску данных, мы получаем N операций, где N — количество соединений, что приводит к экспоненциальному росту затрат ресурсов.
2. Помещение всего справочника Номенклатура во временную таблицу: Еще один неоптимальный подход — это первоначальное копирование всего справочника Номенклатура (даже если он большой, как наши 5-20 тысяч записей) во временную таблицу, а затем уже работа с ней. Такой подход создает излишнюю нагрузку на дисковую подсистему (для записи всей временной таблицы) и оперативную память сервера СУБД. Нам требуется получить всего несколько десятков записей, а мы выполняем колоссальную работу по перемещению и хранению данных, которые в полном объеме нам не нужны. Это расточительное использование ресурсов, которое значительно ухудшает производительность системы.

Дополнительный вопрос: получение реквизита "Артикул"

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

Мы категорически рекомендуем всегда получать все необходимые реквизиты (включая Артикул) непосредственно в основном запросе.

1. Избежание "проблемы N+1 запросов": Если мы сначала получаем только ссылки на номенклатуру, а затем в цикле на форме обращаемся к каждому объекту номенклатуры (например, МояНоменклатура.Артикул), это приводит к так называемой "проблеме N+1 запросов". Суть проблемы заключается в том, что для каждой строки в таблице значений, которая отображается на форме, будет выполнен отдельный запрос к базе данных для получения артикула. Если в таблице 100 строк, это приведет к 100 дополнительным обращениям к СУБД. Такое огромное количество обращений значительно замедлит работу формы и создаст серьезную нагрузку на сервер базы данных.
2. Минимизация сетевого трафика и нагрузки на СУБД: Единый запрос, который выбирает все нужные поля (Ссылка, Наименование, Артикул), выполняет всего одну операцию обращения к базе данных. Это минимизирует накладные расходы, связанные с установлением соединений с СУБД, передачей данных по сети и обработкой каждого запроса самой СУБД. В итоге мы получаем все необходимые данные за один проход, что является гораздо более быстрым и эффективным подходом по сравнению с множественными запросами.

Как такие запросы интерпретирует SQL: глубже в работу оптимизатора

Каждая современная система управления базами данных (СУБД), будь то Microsoft SQL Server, PostgreSQL, Oracle или любая другая, используемая платформой 1С, включает в себя сложный и высокоинтеллектуальный компонент, который называется оптимизатором запросов. Давайте более подробно разберем его работу и то, как он влияет на выполнение наших запросов:

1. Парсинг и связывание: На самом первом этапе СУБД анализирует синтаксис нашего SQL-запроса, проверяя его на корректность и соответствие правилам языка. После успешного синтаксического анализа происходит этап "связывания" (binding) — СУБД убеждается, что все упомянутые в запросе объекты (таблицы, поля, функции, представления) действительно существуют в базе данных и к ним есть необходимый доступ.
2. Генерация альтернативных планов выполнения: Оптимизатор запросов — это не просто исполнитель. Он не выполняет запрос "как написано" дословно. Вместо этого он генерирует множество возможных планов выполнения для данного запроса. Каждый план выполнения представляет собой детальную пошаговую инструкцию, описывающую последовательность операций, которые СУБД может выполнить для получения желаемого результата. Эти шаги могут включать в себя выбор конкретных индексов для доступа к данным, алгоритмы для соединения таблиц, порядок применения фильтрации и сортировки, использование временных структур и многое другое.
3. Выбор оптимального плана: На основе обширной внутренней статистики, которую СУБД собирает по таблицам и индексам (например, количество записей в таблице, распределение данных в полях, уникальность значений, плотность данных), оптимизатор выбирает из сгенерированных вариантов тот план, который, по его расчетам, будет наиболее эффективным. Эффективность здесь измеряется как минимальное использование ресурсов (центрального процессора, оперативной памяти, операций дискового ввода/вывода) и максимально быстрое время выполнения запроса.
   • Использование индексов: Для полей, по которым существуют индексы (как наш UIDВоВнешнейСистеме), оптимизатор с очень высоким приоритетом будет стремиться использовать поиск по индексу (index seek). Это несравнимо быстрее, чем сканирование таблицы (table scan), при котором СУБД вынуждена просматривать каждую строку таблицы, чтобы найти нужные данные. Индекс позволяет СУБД моментально перейти к физическому расположению необходимых записей, минуя ненужные.
   • Временные таблицы и параметры: Когда мы используем конструкцию ПОМЕСТИТЬ в запросе 1С для создания временной таблицы, или когда платформа 1С преобразует ТаблицуЗначений или СписокЗначений в виртуальную/временную таблицу СУБД (например, в SQL Server это может быть реализовано через Table Valued Parameters), оптимизатор обрабатывает это как полноценный объект базы данных. Для этих временных структур также могут собираться статистики (если они не слишком малы), и оптимизатор будет учитывать их характеристики при выборе оптимального плана соединения.
   • Алгоритмы соединения: Оптимизатор запросов также тщательно выбирает подходящий алгоритм соединения таблиц (например, Nested Loop Join, Hash Join, Merge Join). Выбор зависит от множества факторов: размера соединяемых таблиц, наличия индексов на соединяемых полях, а также от так называемой кардинальности данных (то есть, количества уникальных значений в соединяемых полях). В нашем случае, когда небольшой список UID-ов соединяется с большой таблицей по индексированному полю, оптимизатор часто выбирает такие алгоритмы, как Nested Loop Join или Hash Join, где индекс играет ключевую роль в быстром поиске совпадений.

Критическая важность индексирования

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

Без наличия индекса на поле UIDВоВнешнейСистеме, каждый раз, когда мы будем выполнять поиск по этому полю (будь то в условии ГДЕ ... В (...) или при операциях соединения), СУБД будет вынуждена выполнять полное сканирование всей таблицы Справочник.Номенклатура. При количестве записей в 20 тысяч, и тем более при большем, такое полное сканирование может занимать неприемлемо долгое время, приводя к задержкам и "тормозам" в работе системы.

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

Подводя итог, для нашего сценария — с небольшим количеством исходных UID-ов (до 30) и при условии, что поле UIDВоВнешнейСистеме справочника Номенклатура проиндексировано — использование временной таблицы с этими UID-ами и последующее внутреннее соединение с основной таблицей Справочник.Номенклатура является наиболее эффективным, надежным и правильным подходом с точки зрения производительности и общей оптимизации запросов в SQL. И всегда помните: получайте все необходимые данные в одном запросе, чтобы избежать лишних обращений к базе данных и связанных с этим проблем производительности.