Какой запрос оптимальнее: INNER JOIN или вложенный запрос в условии WHERE?

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

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

ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN) как правило, предпочтительнее

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

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

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

Преимущества INNER JOIN:

1. Большая свобода для оптимизатора: Оптимизатор запросов СУБД, как правило, получает больше информации и возможностей для построения эффективного плана выполнения, когда мы используем INNER JOIN. Он может переупорядочивать таблицы, выбирать оптимальные алгоритмы соединения (например, хэш-соединение, соединение слиянием или вложенные циклы), основываясь на статистике по данным и индексам. В некоторых случаях СУБД может даже преобразовать INNER JOIN в так называемый "полу-join" (semi-join), который ведет себя схожим образом с EXISTS, прекращая поиск после нахождения первого совпадения, что значительно экономит ресурсы.
2. Определенность поведения: Поведение INNER JOIN часто более предсказуемо и стабильно, так как оно реже приводит к неверным планам запросов со стороны оптимизатора, по сравнению с вложенными запросами.
3. Меньшая чувствительность к статистикам: Хотя статистики важны всегда, INNER JOIN может быть менее чувствителен к их устареванию или неточности, чем подзапросы, особенно в контексте 1С.

Однако, важно помнить, что если в правой таблице (в нашем случае, РегистрСведений.ЗначенияКатегорийНоменклатуры) для одной записи из левой таблицы (Справочник.Номенклатура) существует несколько совпадений, INNER JOIN может привести к дублированию строк в результирующей выборке. В примере автора выбирается только Номенклатура.Ссылка, и если каждая номенклатура встречается в регистре несколько раз для одной категории, то мы получим дубли. Для устранения дублей в таком случае потребуется добавить ключевое слово РАЗЛИЧНЫЕ (DISTINCT):

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

Добавление РАЗЛИЧНЫЕ влечет за собой дополнительные накладные расходы на сортировку и уникализацию, что также необходимо учитывать при оценке производительности.

Подзапрос в условии WHERE — риски и нюансы

Второй вариант, предложенный автором, использует вложенный запрос в условии ГДЕ с оператором В (IN):

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

Потенциальные проблемы с подзапросами в 1С:

1. Ошибки оптимизатора СУБД: Как отмечалось на форуме, СУБД, особенно используемая в файловом варианте 1С, а также более мощные СУБД при определенных условиях, могут ошибаться при построении планов запросов с вложенными подзапросами. Это может привести к крайне медленному выполнению запроса, иногда в несколько порядков. Проблема усугубляется при слабой загрузке серверного оборудования.
2. Нестабильная работа: Запросы с подзапросами могут быть нестабильными. В некоторых ситуациях они могут работать быстро, в других — значительно замедляться без видимых причин, что усложняет отладку и прогнозирование производительности.
3. Чувствительность к статистикам: Вложенные запросы очень чувствительны к актуальности и полноте статистик СУБД. После полного обновления статистик запрос может ускориться, но со временем, по мере устаревания статистик, производительность может вновь снизиться.
4. Разница между СУБД: Производительность одного и того же запроса с подзапросом может значительно отличаться на разных СУБД (например, PostgreSQL, MS SQL Server, Oracle).

IN против EXISTS:

Если мы говорим о вложенных запросах для фильтрации, стоит различать операторы В (IN) и СУЩЕСТВУЕТ (EXISTS):

• IN: Часто работает быстрее, когда подзапрос возвращает относительно небольшой набор данных. СУБД обычно строит хэш-таблицу из результатов подзапроса и затем использует ее для поиска совпадений. Однако, если подзапрос возвращает большое количество строк, этот подход может быть неэффективным.
• EXISTS: Предпочтительнее, когда подзапрос возвращает большое количество строк или когда нам нужно просто проверить наличие записи, а не получить ее значение. EXISTS прекращает поиск после нахождения первого совпадения, что экономит ресурсы. Оптимизатор может использовать индексы более эффективно. В большинстве случаев для проверки наличия связанных данных EXISTS является более производительным выбором, чем IN.

Пример использования СУЩЕСТВУЕТ:

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

Обратите внимание на использование ПЕРВЫЕ 1 (TOP 1) в подзапросе, что является распространенной оптимизацией для EXISTS, явно указывающей СУБД, что достаточно найти первое совпадение.

Оптимизация через подзапрос в INNER JOIN (производная таблица)

В сообщении 8 форума был предложен компромиссный и часто более эффективный вариант, который сочетает преимущества INNER JOIN с предварительной фильтрацией данных: использование подзапроса как производной таблицы (derived table) во ВНУТРЕННЕМ СОЕДИНЕНИИ.

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

Преимущества этого подхода:

1. Предварительная фильтрация: Мы сначала отбираем только необходимые записи из регистра сведений по категории, формируя таким образом значительно меньшую "временную" таблицу (производную таблицу).
2. Оптимизация соединения: Затем мы выполняем INNER JOIN основной таблицы Справочник.Номенклатура с уже отфильтрованной и, вероятно, небольшой производной таблицей. Это может существенно сократить объем данных, участвующих в операции соединения.
3. Уменьшение дублей: Если подзапрос в производной таблице также использует РАЗЛИЧНЫЕ (DISTINCT) по полю Номенклатура, то мы заранее исключаем дубли, что может быть эффективнее, чем применять РАЗЛИЧНЫЕ к конечному результату всего запроса.

Этот вариант является очень сильным кандидатом на оптимальное решение, если объем данных в регистре сведений большой, но после фильтрации по &Категория остается относительно небольшое количество уникальных номенклатур.

Ключевой метод: Анализ планов запросов

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

Мы используем следующие инструменты:

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

План запроса покажет нам:

• Порядок соединения таблиц.
• Использование или неиспользование индексов.
• Типы операций (сканирование таблиц, поиск по индексу, сортировка, хэш-соединение, вложенные циклы и т.д.).
• Оценочную и фактическую стоимость каждой операции.

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

Дополнительные факторы, влияющие на производительность запросов

Помимо выбора между INNER JOIN и подзапросами, на производительность запросов влияют и другие важные аспекты:

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

В целом, универсального ответа на вопрос "что оптимальнее" не существует. Мы всегда должны исходить из конкретной задачи, объемов и структуры данных, а также анализировать планы выполнения запросов. Начните с INNER JOIN, как с наиболее предсказуемого и часто оптимального варианта. Если производительность неудовлетворительна, мы можем экспериментировать с подзапросами в производных таблицах или EXISTS, и обязательно проверять результаты с помощью инструментов профилирования.