Эффективные условия запросов

Область применения: управляемое приложение, мобильное приложение, обычное приложение.

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

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

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

Основное условие – это то, что позволяет ограничить объем выборки больше других условий и его составляющие объединены по И.

Дополнительное условие – это то, что объединено с основным условием по И и его составляющие могут быть любой сложности (НЕ, <>, +, -, /, *, функции и т.п.).

Основное условие должно содержать только такие операции, которые позволяют выполнять поиск по индексу:

• для первого и всех используемых полей индекса, кроме последнего, только = и И;
• для последнего или единственного используемого поля индекса допустимо использовать =, >, <, >=, <=, ПОДОБНО, МЕЖДУ, В, ИЛИ (приводимое к В);
• нельзя использовать арифметические операции, функции, отрицания и неравенства.

Для условий в ГДЕ или в виртуальной таблице следует индексировать поля в основной таблице, из которой выполняется выборка.

Для условий в ПО ЛЕВОГО соединения следует индексировать поля в правой таблице.

Для условий в ПО ВНУТРЕННЕГО соединения следует индексировать поля в таблице с большим количеством записей.

Основное условие желательно строить таким образом, чтобы оно использовало индексы, которые автоматически создает платформа.

1.1. Описанные выше требования допустимо не соблюдать, если в таблицах, из которых выполняется выборка, или с которыми выполняется соединение, всегда будет мало данных (менее 1000 записей) или запросы с такими условиями выполняются очень редко.

1.2. Если записей в таблице много и выполнить указанные выше требования невозможно, то можно попробовать:

• преобразовать условия (см. п. 3, п. 4);
• добавить в таблицу заранее вычисляемые индексированные поля, которые заполняются при записи в нее и используются вместо сложного условия;
• если указанные выше рекомендации не помогли, то следует пересмотреть архитектуру решения так, чтобы можно было выполнить эти условия.

2. Оператор ИЛИ

2.1. В основном условии оператор ИЛИ можно использовать только для последнего из используемых или единственного поля индекса, когда оператор ИЛИ можно заменить на оператор В.

ПРАВИЛЬНО:

ГДЕ
    Таблица.Поле = &Значение1
    ИЛИ Таблица.Поле = &Значение2

т.к. можно переписать при помощи оператора В (специально переписывать не нужно, можно оставить, как есть):

ГДЕ
    Таблица.Поле В (&Значения)

НЕПРАВИЛЬНО:

ГДЕ
    Таблица.Поле1 = &Значение1
    ИЛИ Таблица.Поле2 = &Значение2

нельзя переписать при помощи "В", но можно переписать при помощи "ОБЪЕДИНИТЬ ВСЕ" (каждое поле Поле1 и Поле2 должны быть проиндексированы):

ГДЕ
    Таблица.Поле1 = &Значение1

ОБЪЕДИНИТЬ ВСЕ

ГДЕ
    Таблица.Поле2 = &Значение1

Примечание: заменить ИЛИ на ОБЪЕДИНИТЬ ВСЕ можно не всегда, убедитесь, что результат будет действительно тем же, что и при ИЛИ, перед тем, как применять.

2.2. В дополнительном условии оператор ИЛИ можно использовать без ограничений.

ПРАВИЛЬНО 1:

ГДЕ
    Таблица.Поле1 = &Значение1 // Основное условие (использует индекс)
    И // Дополнительное условие (можно использовать ИЛИ)
    (Таблица.Поле2 = &Значение2 ИЛИ Таблица.Поле3 = &Значение3)

ПРАВИЛЬНО 2:

ГДЕ
    (Таблица.Поле1 = &Значение1 ИЛИ Таблица.Поле1 = &Значение2)
    И
    (Таблица.Поле2 = &Значение3 ИЛИ Таблица.Поле2 = &Значение4)

т.к. можно переписать при помощи В (специально переписывать не нужно, можно оставить, как есть):

ГДЕ
    Таблица.Поле1 В (&Значения1)   // Основное условие
    И Таблица.Поле2 В (&Значения2) // Дополнительное условие (или наоборот)

3. Оператор ПОДОБНО

В основном условии для последнего из используемых или единственного поля индекса можно использовать оператор ПОДОБНО. Функции работы со строками, в некоторых случаях, можно привести к оператору ПОДОБНО и использовать его в основном условии.

НЕПРАВИЛЬНО 1:

ГДЕ
    ПОДСТРОКА(Таблица.Поле, 1, 6) = "строка"

ПРАВИЛЬНО 1:

ГДЕ
    Таблица.Поле ПОДОБНО "строка%"

НЕПРАВИЛЬНО 2:

ГДЕ
    ПОДСТРОКА(Таблица.Поле, 3, 6) = "строка"

НЕПРАВИЛЬНО 2:

ГДЕ
    Таблица.Поле ПОДОБНО "__строка%" // Литерал не должен начинаться с символов "_" или "%"

ПРАВИЛЬНО 2:

Добавить новое вычисляемое при записи в таблицу поле, которое будет содержать фрагмент ПОДСТРОКА(Таблица.Поле, 3, 6). Проиндексировать это поле и искать по следующему условию:

ГДЕ
    Таблица.ВычисляемоеПоле ПОДОБНО "строка%"

4. Оператор МЕЖДУ

В основном условии для последнего из используемых или единственного поля индекса можно использовать оператор МЕЖДУ. Функции работы с датой, в некоторых случаях, можно привести к оператору МЕЖДУ и использовать его в основном условии.

НЕПРАВИЛЬНО:

ГДЕ
    МЕСЯЦ(Таблица.Поле) = 1

ПРАВИЛЬНО:

ГДЕ
    Таблица.Поле МЕЖДУ &ДатаНачалаМесяца И &ДатаКонцаМесяца

Например, ДатаНачалаМесяца=01.01.2016, ДатаКонцаМесяца=31.01.2016 23:59:59

5. Выражение ВЫБОР

Выражение ВЫБОР можно использовать только в дополнительных условиях.

ПРАВИЛЬНО:

ГДЕ
    Таблица.Поле1 = &Значение1 // Основное условие (использует индекс)
    И // Дополнительное условие (можно использовать ВЫБОР)
    ВЫБОР
        КОГДА Таблица.Поле2 = &Значение2
            ТОГДА Таблица.Поле3 = &Значение3
        ИНАЧЕ Таблица.Поле4 = &Значение4
    КОНЕЦ

НЕПРАВИЛЬНО:

ГДЕ
    ВЫБОР // Основное условие (поиск по индексу использоваться не будет)
        КОГДА Таблица.Поле2 = &Значение2
            ТОГДА Таблица.Поле3 = &Значение3
        ИНАЧЕ Таблица.Поле4 = &Значение4
    КОНЕЦ

6. Арифметические операции

Арифметические операции над полями можно выполнять только в дополнительных условиях.

ПРАВИЛЬНО:

ГДЕ
    Таблица.Поле1 = &Значение1 // Основное условие (использует индекс)
    И // Дополнительное условие (можно выполнять арифметические операции)
    Таблица.Поле2 - 1 > 0

НЕПРАВИЛЬНО:

ГДЕ
    Таблица.Поле1 - 1 > 0 // Основное условие (поиск по индексу невозможен)

7. Если в конфигурации описано несколько ролей с разным ограничением доступа на уровне записей (RLS), то не следует назначать одному пользователю более одной такой роли. Если один пользователь будет включен, например, в две роли с RLS - бухгалтер и кадровик, то при выполнении всех его запросов к их условиям будут добавляться условия обоих RLS с использованием логического ИЛИ. Таким образом, даже если в исходном запросе нет условия ИЛИ, оно появится там после добавления условий RLS. Такой запрос так же может выполняться неоптимально - медленно и с избыточными блокировками.

Вместо этого следует:

• Пересмотреть состав ролей таким образом, чтобы к одному объекту метаданных давала доступ только одна роль (на чтение, запись и т.п.);
• При необходимости разработки нескольких ролей, предоставляющих доступ к одному объекту метаданных, задавать в них одинаковые условия RLS. В этом случае к тексту запроса будет добавлено только одно условие, без объединения по ИЛИ;
• Либо если это допустимо с точки зрения прикладной области, создать "смешанную" роль - "бухгалтер-кадровик" и прописать ее RLS таким образом, чтобы избежать использования ИЛИ в условии, а пользователя включить в эту одну роль.

См. также