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

Программист 1С v8.3 (Управляемые формы) Управленческий учет Торговля и дистрибуция
← На главную

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

Давайте представим, что перед нами стоит задача, аналогичная той, что была озвучена нашими коллегами на форуме: мы получили несколько кодов номенклатуры (например, &КОД1 и &КОД2) из некоторого внешнего источника. Нам необходимо сформировать результат, который будет содержать ссылки на соответствующие элементы справочника Номенклатура и их текущие цены из регистра сведений ЦеныНоменклатуры25.СрезПоследних для определенного вида цены &ВидЦены. При этом важно корректно обработать ситуации, когда номенклатура по коду не найдена или цена отсутствует.

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

Вариант 1: Прямое Левое Соединение на Левое Соединение

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

Пример кода Варианта 1:


ВЫБРАТЬ
    &КОД1 КАК НоменклатураКод
ПОМЕСТИТЬ ВТВнешнийИсточник
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
    &КОД2 КАК НоменклатураКод
;

ВЫБРАТЬ
    СписокНоменклатуры.Ссылка КАК Номенклатура,
    ЕСТЬNULL(Цены.Цена, 0) КАК Цена
ИЗ
    ВТВнешнийИсточник КАК Данные
        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК СписокНоменклатуры
        ПО Данные.НоменклатураКод = СписокНоменклатуры.Код
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры25.СрезПоследних КАК Цены
        ПО СписокНоменклатуры.Ссылка = Цены.Номенклатура
        И Цены.ВидЦены = &ВидЦены

Разберем работу Варианта 1

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

  1. Первое левое соединение связывает данные из ВТВнешнийИсточник со справочником Справочник.Номенклатура по полю Код. Это позволяет нам получить ссылку на элемент номенклатуры. Важно, что мы используем левое соединение, то есть, если номенклатура по коду не будет найдена, запись из ВТВнешнийИсточник все равно попадет в результат, а поля из Справочник.Номенклатура будут NULL.
  2. Второе левое соединение связывает результат первого соединения с виртуальной таблицей РегистрСведений.ЦеныНоменклатуры25.СрезПоследних. Условие соединения включает как ссылку на номенклатуру, так и требуемый вид цены. Функция ЕСТЬNULL(Цены.Цена, 0) гарантирует, что если цена не будет найдена (например, номенклатура не имеет цены указанного вида или сама номенклатура не найдена), то в поле Цена будет выведено 0 вместо NULL.

Преимущества Варианта 1

Недостатки Варианта 1

Вариант 2: С использованием временной таблицы для номенклатуры

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

Пример кода Варианта 2:


ВЫБРАТЬ
    &КОД1 КАК НоменклатураКод
ПОМЕСТИТЬ ВТВнешнийИсточник
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
    &КОД2 КАК НоменклатураКод
;

ВЫБРАТЬ
    СписокНоменклатуры.Ссылка КАК Номенклатура
ПОМЕСТИТЬ ВТНоменклатура
ИЗ
    ВТВнешнийИсточник КАК ВТ
        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК СписокНоменклатуры
        ПО ВТ.НоменклатураКод = СписокНоменклатуры.Код
;

ВЫБРАТЬ
    Данные.Номенклатура КАК Номенклатура,
    ЕСТЬNULL(Цены.Цена, 0) КАК Цена
ИЗ
    ВТНоменклатура КАК Данные
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры25.СрезПоследних КАК Цены
        ПО Данные.Номенклатура = Цены.Номенклатура
        И Цены.ВидЦены = &ВидЦены

Разберем работу Варианта 2

Этот вариант состоит из трех основных этапов:

  1. Как и в первом случае, мы начинаем с формирования временной таблицы ВТВнешнийИсточник, содержащей исходные коды номенклатуры.
  2. На втором шаге мы выполняем левое соединение ВТВнешнийИсточник со Справочник.Номенклатура. Результат этого соединения – список ссылок на номенклатуру (или NULL, если номенклатура не найдена) – сохраняется в новой временной таблице ВТНоменклатура. Этот шаг изолирует поиск номенклатуры от дальнейших операций.
  3. Наконец, на третьем шаге, мы соединяем ВТНоменклатура с виртуальной таблицей РегистрСведений.ЦеныНоменклатуры25.СрезПоследних. Это соединение происходит уже с меньшим объемом данных (только найденные и ненайденные ссылки на номенклатуру), и оно также использует ЛЕВОЕ СОЕДИНЕНИЕ, чтобы сохранить все исходные записи. Как и ранее, применяется ЕСТЬNULL для цен.

Преимущества Варианта 2

Недостатки Варианта 2

Вариант 3: Оптимизированный подход с использованием параметров виртуальных таблиц

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

Общая идея и важность: Крайне важно понимать, что виртуальные таблицы, такие как РегистрСведений.ЦеныНоменклатуры25.СрезПоследних, – это не просто обычные таблицы. Они являются частью языка запросов 1С:Предприятия и при трансляции в SQL могут "разворачиваться" в сложные подзапросы, получающие данные из нескольких физических таблиц СУБД (например, таблицы периодического регистра, таблицы значений, таблицы изменений). Если мы фильтруем такую виртуальную таблицу в секции ГДЕ основного запроса или в условии ПО при соединении, СУБД сначала может получить все данные из этой виртуальной таблицы (или очень большой объем), а уже потом применять к ним фильтр. Это крайне неэффективно.

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

Пример кода Варианта 3 (комбинированный и оптимизированный):


// Шаг 1: Получаем исходные коды во временную таблицу
ВЫБРАТЬ
    &КОД1 КАК НоменклатураКод
ПОМЕСТИТЬ ВТВнешнийИсточник
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
    &КОД2 КАК НоменклатураКод
;

// Шаг 2: Получаем ссылки на номенклатуру (используем ВНУТРЕННЕЕ СОЕДИНЕНИЕ,
// если нам не нужны ненайденные позиции, или ЛЕВОЕ, если нужны).
// Для получения списка цен нам нужны только существующие ссылки.
ВЫБРАТЬ РАЗЛИЧНЫЕ
    СписокНоменклатуры.Ссылка КАК НоменклатураСсылка
ПОМЕСТИТЬ ВТНоменклатураДляЦен
ИЗ
    ВТВнешнийИсточник КАК ВТ
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК СписокНоменклатуры
        ПО ВТ.НоменклатураКод = СписокНоменклатуры.Код
ГДЕ
    СписокНоменклатуры.Ссылка ЕСТЬ НЕ NULL
;

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

// Шаг 4: Соединяем исходные коды (или ссылки на номенклатуру) с найденными ценами.
// Здесь мы можем решить, нужны ли нам ненайденные номенклатуры.
// Если нужны все исходные коды (даже те, для которых нет номенклатуры/цены):
ВЫБРАТЬ
    ВТ.НоменклатураКод КАК ИсходныйКод,
    СписокНоменклатуры.Ссылка КАК Номенклатура,
    ЕСТЬNULL(ВТЦены.Цена, 0) КАК Цена
ИЗ
    ВТВнешнийИсточник КАК ВТ
        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК СписокНоменклатуры
        ПО ВТ.НоменклатураКод = СписокНоменклатуры.Код
        ЛЕВОЕ СОЕДИНЕНИЕ ВТЦены КАК ВТЦены
        ПО СписокНоменклатуры.Ссылка = ВТЦены.Номенклатура

Разберем работу Варианта 3

  1. Подготовка исходных кодов (ВТВнешнийИсточник): Мы начинаем так же, как и в предыдущих вариантах, собирая все коды из внешнего источника в первую временную таблицу.
  2. Получение уникальных ссылок на номенклатуру (ВТНоменклатураДляЦен):
    • Здесь мы соединяем ВТВнешнийИсточник со Справочник.Номенклатура. Мы можем использовать ВНУТРЕННЕЕ СОЕДИНЕНИЕ, если мы хотим получить цены только для тех позиций, которые реально существуют в справочнике. Если нам нужны все коды из внешнего источника, включая те, что не нашлись в справочнике, то на этом этапе нам все равно нужен будет список найденных ссылок для запроса цен.
    • Важно: Мы используем ВЫБРАТЬ РАЗЛИЧНЫЕ, чтобы избежать дублирования ссылок, если вдруг внешние коды могут повторяться, а также для получения чистого списка номенклатуры для передачи в параметры среза.
    • Мы фильтруем СписокНоменклатуры.Ссылка ЕСТЬ НЕ NULL, чтобы в следующую временную таблицу попали только реально найденные ссылки.
  3. Оптимизированное получение цен (ВТЦены):
    • Это ключевой шаг. Мы вызываем виртуальную таблицу РегистрСведений.ЦеныНоменклатуры25.СрезПоследних и передаем ей все необходимые условия в качестве параметров:
      • &Дата: Дата, на которую нам нужен срез цен.
      • Номенклатура В (ВЫБРАТЬ НоменклатураСсылка ИЗ ВТНоменклатураДляЦен): Здесь мы передаем список ссылок на номенклатуру, полученный на предыдущем шаге. Это позволяет СУБД сразу же отобрать цены только для нужной номенклатуры, не обрабатывая весь регистр.
      • ВидЦены = &ВидЦены: Условие по виду цены также передается как параметр.
    • Результат этого оптимизированного запроса мы сохраняем во временной таблице ВТЦены.
  4. Финальное формирование результата:
    • На последнем шаге мы соединяем нашу исходную ВТВнешнийИсточник со Справочник.Номенклатура (чтобы получить ссылку на номенклатуру по исходному коду) и затем с ВТЦены.
    • Здесь мы снова используем ЛЕВОЕ СОЕДИНЕНИЕ с ВТЦены, чтобы гарантировать, что все номенклатурные позиции (даже те, для которых цены не найдены или сама номенклатура не найдена) попадут в конечный результат.
    • Применяем ЕСТЬNULL(ВТЦены.Цена, 0) для корректного отображения цен.

Преимущества Варианта 3

Когда использовать Вариант 3

Этот вариант является рекомендуемым для большинства сценариев, особенно если вы работаете с:

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

Выбор типа соединения: Левое или Внутреннее?

Важный аспект, который мы должны рассмотреть при построении запросов, – это выбор между ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN) и ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN).

Когда использовать ЛЕВОЕ СОЕДИНЕНИЕ

Мы используем ЛЕВОЕ СОЕДИНЕНИЕ, когда нам необходимо получить все записи из левой таблицы, даже если для них не найдено соответствий в правой таблице. В этом случае поля из правой таблицы для несоответствующих записей будут заполнены значениями NULL.

Примеры использования:

  1. Нам нужно вывести все номенклатурные позиции из нашего списка, независимо от того, есть ли для них цены. Если цены нет, мы хотим видеть номенклатуру и, например, "0" в поле цены.
  2. Если нам важно знать, какие из внешних кодов не нашли соответствия в справочнике Номенклатура, чтобы сообщить об этом пользователю или занести в лог ошибок. При ЛЕВОМ СОЕДИНЕНИИ мы увидим эти коды, а поле Ссылка из справочника будет NULL.

Когда использовать ВНУТРЕННЕЕ СОЕДИНЕНИЕ

ВНУТРЕННЕЕ СОЕДИНЕНИЕ возвращает только те записи, для которых есть совпадения в обеих таблицах. Если для записи из левой таблицы не найдено соответствия в правой, эта запись не попадет в результат.

Примеры использования:

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

Влияние на бизнес-логику

Выбор между ЛЕВОЕ и ВНУТРЕННЕЕ СОЕДИНЕНИЕ напрямую зависит от бизнес-логики и требований к конечному результату запроса. Всегда задавайте себе вопрос: "Что должно произойти, если соответствие не найдено?".

С точки зрения производительности, ВНУТРЕННЕЕ СОЕДИНЕНИЕ часто может быть немного быстрее, так как СУБД обрабатывает меньший объем данных. Однако это преимущество не должно быть решающим, если оно противоречит требованиям бизнес-логики.

Общие рекомендации по оптимизации запросов 1С

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

Анализ планов запросов (SQL Execution Plans)

Самый надежный и единственный объективный способ определить, какой вариант запроса работает лучше, – это анализировать планы запросов (SQL Execution Plans) на реальных данных. Все наши обсуждения производительности без этого анализа остаются гипотетическими и "сферическими конями в вакууме".

Как это сделать:

  1. Используйте встроенные инструменты 1С:Предприятия, такие как "Консоль запросов" или "Центр управления производительностью", которые могут показать план запроса на стороне СУБД.
  2. Если вы имеете доступ к СУБД (например, MS SQL Server, PostgreSQL), используйте его родные средства профилирования и анализа запросов (например, SQL Server Management Studio для MS SQL Server). Это позволит нам увидеть, какие индексы используются, какие операции выполняются, сколько ресурсов затрачивается на каждом шаге запроса.

Анализируя план запроса, мы можем выявить "узкие места", такие как табличные сканирования вместо индексных поисков, большие объемы временных таблиц или неоптимальные операции соединения, и целенаправленно их устранять.

Роль временных таблиц

Временные таблицы (ВТ) – это мощный инструмент оптимизации сложных запросов. Мы должны использовать их с умом, а не избегать по умолчанию.

Ключевые сценарии использования ВТ:

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

Однако помните, что слишком большое количество очень объемных временных таблиц может, наоборот, создать дополнительную нагрузку на TempDB/TempSpace СУБД. Всегда ищите баланс.

Корректная работа с виртуальными таблицами

Как мы подробно обсудили в Варианте 3, это одно из самых важных правил:

  1. Передавайте все возможные условия отбора и параметры непосредственно в саму виртуальную таблицу. Используйте синтаксис РегистрСведений.ИмяРегистра.ВиртуальнаяТаблица(&Параметр1, Условие1 И Условие2).
  2. Избегайте фильтрации результатов виртуальных таблиц в секции ГДЕ или в условиях ПО после их присоединения, если эти условия могут быть переданы в параметры. Это приводит к тому, что виртуальная таблица сначала получает весь объем данных, а потом он фильтруется, что крайне неэффективно.

Обработка NULL значений

Когда мы используем ЛЕВОЕ СОЕДИНЕНИЕ, в выборке могут появиться значения NULL для полей из правой таблицы, если соответствие не было найдено. Важно корректно обрабатывать эти значения:

  1. В запросе: Используйте функцию ЕСТЬNULL(Поле, ЗначениеПоУмолчанию), чтобы заменить NULL на подходящее значение (например, 0 для чисел, пустую строку для строк, неопределенную ссылку для ссылок). Это делает результат запроса более предсказуемым и пригодным для дальнейшей обработки.
  2. В коде 1С: Если NULL значения остаются в результате, убедитесь, что ваш код 1С корректно обрабатывает их, чтобы избежать ошибок.

Заключение

Мы проанализировали несколько подходов к решению нашей задачи по получению данных о номенклатуре и ценах из внешнего источника. Каждый из них имеет свои особенности:

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

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

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

← На главную