Как оптимизировать запросы 1С на больших объемах: сравнение WHERE IN, INNER JOIN, EXISTS и использование временных таблиц?

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

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

1. Исходная проблема и предложенные варианты запросов

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

Вариант 1: Использование `WHERE IN` во вложенных запросах

В этом варианте мы последовательно создаем две временные таблицы, используя условие ГДЕ ... В (ВЫБРАТЬ ...). Сначала мы выбираем идентификаторы контрагентов из нашей табличной части и помещаем их во временную таблицу ТЗДанные. Затем на основе этой таблицы формируем временную таблицу ВТ_Контрагенты, отбирая контрагентов из справочника по их идентификаторам. И, наконец, используем ВТ_Контрагенты для отбора номенклатуры.


ВЫБРАТЬ
    ТЗДанные.ИдентификаторКонтрагента КАК ИдентификаторКонтрагента
ПОМЕСТИТЬ ТЗДанные
ИЗ
    &ТЗДанные КАК ТЗДанные
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Контрагенты.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_Контрагенты
ИЗ
    Справочник.Контрагенты КАК Контрагенты
ГДЕ
    Контрагенты.ИдентификаторКонтрагента В
        (ВЫБРАТЬ
            ТЗДанные.ИдентификаторКонтрагента
        ИЗ
            ТЗДанные КАК ТЗДанные)
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Номенклатура.Ссылка КАК Ссылка
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    Номенклатура.Поставщик В
        (ВЫБРАТЬ
            ВТ_Контрагенты.Ссылка
        ИЗ
            ВТ_Контрагенты КАК ВТ_Контрагенты)

Вариант 2: Использование `ВНУТРЕННЕЕ СОЕДИНЕНИЕ`

Этот вариант также использует пакетную структуру запросов с временными таблицами, но вместо WHERE IN для связывания данных мы применяем ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Мы помещаем идентификаторы из внешней таблицы в ТЗДанные, затем соединяем Справочник.Контрагенты с ТЗДанные для получения нужных контрагентов и сохраняем результат в ВТ_Контрагенты. Последним шагом мы соединяем Справочник.Номенклатура с ВТ_Контрагенты.


ВЫБРАТЬ
    ТЗДанные.ИдентификаторКонтрагента КАК ИдентификаторКонтрагента
ПОМЕСТИТЬ ТЗДанные
ИЗ
    &ТЗДанные КАК ТЗДанные
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Контрагенты.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_Контрагенты
ИЗ
    Справочник.Контрагенты КАК Контрагенты
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ТЗДанные ПО Контрагенты.ИдентификаторКонтрагента = ТЗДанные.ИдентификаторКонтрагента
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Номенклатура.Ссылка КАК Ссылка
ИЗ
    Справочник.Номенклатура КАК Номенклатура
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Контрагенты ПО Номенклатура.Поставщик = ВТ_Контрагенты.Ссылка

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

2. Обязательность временных таблиц для передачи внешних данных

Прежде чем углубляться в сравнение, важно отметить один фундаментальный аспект работы с табличными параметрами (такими как &ТЗДанные или ДанныеИзЭксель) в запросах 1С. Мы не можем напрямую использовать табличный параметр в секции ИЗ или СОЕДИНЕНИЕ. Система 1С требует, чтобы содержимое табличного параметра было сначала помещено во временную таблицу.

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


// Этот первый пакет обязателен для любого из вариантов
ВЫБРАТЬ
    ТЗДанные.ИдентификаторКонтрагента КАК ИдентификаторКонтрагента
ПОМЕСТИТЬ ТЗДанные
ИЗ
    &ТЗДанные КАК ТЗДанные
;

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

3. Объединение запросов в один пакет для оптимального выполнения

Одним из ключевых принципов оптимизации запросов в 1С и в SQL в целом является стремление к объединению связанных операций в единый запрос или пакет запросов, для разработки которых может пригодиться консоль запросов. Разделение логически связанных операций на множество отдельных пакетов, каждый из которых создает свою временную таблицу, часто приводит к неоптимальной производительности.

Почему объединение важно:

  1. Единый план выполнения: Когда запросы объединены, оптимизатор СУБД (будь то встроенный в 1С или внешний SQL-сервер) может построить более целостный и эффективный план выполнения. Он видит всю логику сразу и может найти наиболее оптимальный путь для получения результата, минимизируя промежуточные операции.
  2. Сокращение ввода-вывода: Каждое создание и заполнение временной таблицы – это операция ввода-вывода (запись на диск или в оперативную память). Чем меньше таких операций, тем быстрее запрос. Объединяя запросы, мы уменьшаем количество промежуточных временных таблиц.
  3. Меньше накладных расходов: Создание каждой временной таблицы требует выделения ресурсов и выполнения дополнительных шагов, что замедляет процесс. Единый запрос позволяет избежать этих накладных расходов.

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


// Первый пакет остается обязательным
ВЫБРАТЬ
    ТЗДанные.ИдентификаторКонтрагента КАК ИдентификаторКонтрагента
ПОМЕСТИТЬ ТЗДанные
ИЗ
    &ТЗДанные КАК ТЗДанные
;
////////////////////////////////////////////////////////////////////////////////
// Объединенный запрос для получения Номенклатуры
ВЫБРАТЬ
    Номенклатура.Ссылка КАК Ссылка
ИЗ
    Справочник.Номенклатура КАК Номенклатура
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Контрагенты КАК Контрагенты
            ПО Номенклатура.Поставщик = Контрагенты.Ссылка
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ТЗДанные КАК ТЗДанные_ВТ // Использование временной таблицы с идентификаторами
            ПО Контрагенты.ИдентификаторКонтрагента = ТЗДанные_ВТ.ИдентификаторКонтрагента

В этом оптимизированном варианте мы напрямую связываем Справочник.Номенклатура с Справочник.Контрагенты, а затем результат этого соединения — с нашей временной таблицей ТЗДанные, содержащей идентификаторы из Excel. Такой подход позволяет СУБД построить один эффективный план, избегая создания лишней промежуточной таблицы ВТ_Контрагенты.

4. Индексирование временных таблиц: когда и зачем

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

Когда индексировать?

Индексирование временных таблиц целесообразно, если:

  1. Большой объем данных: Во временной таблице ожидается более 1000 записей (это общая рекомендация, точное число зависит от СУБД и конфигурации). Для очень маленьких таблиц создание индекса может занять больше времени, чем его использование.
  2. Частое использование в соединениях и отборах: Поля временной таблицы будут использоваться в условиях СОЕДИНЕНИЕ (JOIN ON) или ГДЕ (WHERE) в последующих запросах.
  3. Сложные запросы: Временная таблица является частью многоступенчатого запроса с большим количеством связей.

Какие поля индексировать?

Индексировать следует только те колонки, которые будут задействованы в условиях соединений или фильтрации. Например, если временная таблица ТЗДанные используется для соединения по полю ИдентификаторКонтрагента, то именно это поле и нужно индексировать.

Синтаксис индексирования:

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


ВЫБРАТЬ
    ТЗДанные.ИдентификаторКонтрагента КАК ИдентификаторКонтрагента,
    ТЗДанные.ДругоеПоле КАК ДругоеПоле // Если есть другие поля
ПОМЕСТИТЬ ТЗДанные
ИЗ
    &ТЗДанные КАК ТЗДанные
ИНДЕКСИРОВАТЬ ПО ИдентификаторКонтрагента // Индексируем по полю, используемому в соединении
;
////////////////////////////////////////////////////////////////////////////////
// Далее ваш объединенный запрос, использующий ТЗДанные
// ...

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

Влияние на производительность:

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

5. Сравнение подходов: `WHERE IN` vs `INNER JOIN` vs `EXISTS`

Мы рассмотрели два основных способа фильтрации данных: WHERE IN и ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Давайте глубже проанализируем эти и другие подходы.

5.1. `INNER JOIN` против `WHERE IN`

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

  1. `INNER JOIN`:
    • Часто считается более эффективным для больших объемов данных, так как он лучше масштабируется и взаимодействует с индексами.
    • Предпочтителен, когда вам нужно получить данные из обеих таблиц или когда требуется более сложное условие соединения.
    • С точки зрения читаемости, INNER JOIN более явно указывает на связь между таблицами.
  2. `WHERE IN`:
    • Может быть удобнее и не сильно уступит в производительности для небольших фиксированных списков значений (например, ГДЕ Поле В ("Значение1", "Значение2")).
    • Если список значений для IN формируется сложным вложенным запросом без использования временных таблиц, это может привести к неоптимальному плану выполнения, так как СУБД может каждый раз выполнять вложенный запрос.

В контексте нашей задачи, когда данные поступают из внешней таблицы, ВНУТРЕННЕЕ СОЕДИНЕНИЕ (как показано в оптимизированном варианте в разделе 3) обычно показывает лучшую производительность, так как оно позволяет СУБД более эффективно использовать индексы и строить единый план выполнения.

5.2. Подход с `EXISTS`

Оператор EXISTS – это еще одна мощная конструкция для проверки наличия записей. Он часто работает эффективнее, чем IN, особенно когда требуется только факт наличия, а не сами данные. Ключевое преимущество EXISTS в том, что СУБД прекращает поиск сразу после первого найденного совпадения, что позволяет избежать полного сканирования таблицы. Это особенно полезно, когда не требуется выводить поля из вложенного запроса.

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


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

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

Замечание из дискуссии: "Вложенный запрос убивает связь по индексам" (Сообщение 18) относится к неоптимальным вложенным запросам, которые могут повторно сканировать данные. Однако, когда вложенный запрос использует уже подготовленную и проиндексированную временную таблицу (как в примере с EXISTS), это может быть очень эффективным.

5.3. Передача параметра-массива (`&МассивИдентификаторов`)

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


// Подготовка параметра:
// МассивИдентификаторов = ДанныеИзЭксель.ВыгрузитьКолонку("ИдентификаторКонтрагента");

ВЫБРАТЬ
    Номенклатура.Ссылка КАК Ссылка
ИЗ
    Справочник.Контрагенты КАК Контрагенты
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК Номенклатура
            ПО Номенклатура.Поставщик = Контрагенты.Ссылка
ГДЕ
    Контрагенты.ИдентификаторКонтрагента В (&МассивИдентификаторов)

Этот подход прост и читаем. Однако в дискуссии было справедливо отмечено, что "Загрузка ТЗ как параметра - одна из медленных операций" (Сообщение 11). Если &МассивИдентификаторов содержит очень большое количество элементов (например, десятки тысяч), передача такого массива в параметрах может быть медленнее, чем использование временной таблицы.

Плюс в том, что этот запрос можно объединить в один пакет, а условие ГДЕ может быть достаточно эффективно для простых отборов, особенно если по полю Контрагенты.ИдентификаторКонтрагента есть индекс.

6. Заключительные рекомендации по оптимальным запросам 1С

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

  1. Используйте временные таблицы для внешних данных: Если вы передаете табличные данные (например, из Excel) в запрос, всегда помещайте их во временную таблицу с помощью ПОМЕСТИТЬ. Это обязательный шаг и основа для дальнейшей оптимизации.
  2. Объединяйте запросы в единые пакеты: После того как временные таблицы с исходными данными созданы, старайтесь объединять все последующие операции соединения и фильтрации в один большой пакет запросов. Это позволяет СУБД построить более оптимальный план выполнения, сократить дисковые операции и накладные расходы на создание промежуточных временных таблиц.
  3. Предпочитайте `ВНУТРЕННЕЕ СОЕДИНЕНИЕ` для связывания и фильтрации больших таблиц: В большинстве случаев INNER JOIN более эффективно обрабатывается СУБД, особенно с использованием индексов, чем WHERE IN со вложенным запросом.
  4. Индексируйте временные таблицы: Если ваша временная таблица содержит значительное количество записей (например, более тысячи) и ее поля используются в условиях СОЕДИНЕНИЕ или ГДЕ, обязательно индексируйте эти поля с помощью ИНДЕКСИРОВАТЬ ПО. Выбирайте для индексации те поля, по которым будет выполняться связь или отбор.
  5. Рассмотрите `EXISTS` для проверки наличия: Если вам нужно только проверить факт существования записей, а не извлекать сами данные из связанных таблиц, используйте конструкцию EXISTS с ПЕРВЫЕ 1 Истина. Этот подход может быть очень быстрым.
  6. Будьте осторожны с большими табличными параметрами: Хотя передача массивов в параметрах запроса удобна, для очень больших объемов данных (десятки тысяч элементов) загрузка их как параметра может быть медленнее, чем помещение во временную таблицу.
  7. Анализируйте планы запросов: Идеальный подход – это всегда тестировать различные варианты на реальных данных и анализировать планы выполнения запросов. Хотя в 1С это не так просто, как в чистом SQL, понимание принципов работы оптимизатора запросов помогает писать более эффективный код.

7. Дополнительные советы по оптимизации запросов

Помимо рассмотренных методов, существуют и другие общие рекомендации, которые помогают повысить производительность запросов:

  1. Выбирайте только необходимые поля: Избегайте использования ВЫБРАТЬ *. Всегда явно перечисляйте только те поля, которые действительно нужны для дальнейшей работы. Это сокращает объем передаваемых данных и нагрузку на СУБД.
  2. Ранняя фильтрация: Применяйте условия фильтрации как можно раньше в запросе. Чем меньше данных будет обработано на каждом последующем этапе, тем быстрее будет выполнен запрос.
  3. Оптимизация виртуальных таблиц: Если вы работаете с виртуальными таблицами 1С (например, ОстаткиИОбороты, СрезПоследних), всегда накладывайте отборы по их параметрам (период, измерения) непосредственно в параметрах виртуальной таблицы, а не в секции ГДЕ основного запроса. Это позволяет виртуальной таблице сформироваться уже с отфильтрованными данными.
  4. Избегайте "двух точек" в условии `ГДЕ`: В 1С обращение к реквизитам связанных объектов через "точку" (например, Номенклатура.Поставщик.Наименование) в условиях ГДЕ может приводить к неоптимальным запросам, так как СУБД может выполнить лишние соединения. Лучше явно соединить таблицы или использовать ВЫРАЗИТЬ, если это требуется.
  5. Используйте индексирование на справочниках и регистрах: Убедитесь, что поля, участвующие в условиях СОЕДИНЕНИЕ и ГДЕ в основных таблицах базы данных (справочниках, регистрах), имеют соответствующие индексы. Это фундаментальный аспект производительности любой базы данных.

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

← На главную