Особенности использования группового оператора вхождения

В язык запросов 1С:Предприятия версии 8.1.10 и более поздних добавлена обобщенная форма оператора "В"/ "НЕ В". В разделе поясняются особенности выполнения этого оператора, учет которых позволит повысить производительность запросов.

Групповой оператор вхождения

Оператор вхождения имеет вид:

<Выражение> В (<Список значений>)

Оператор вхождения является одним из операторов сравнения, определенных в языке запросов 1С:Предприятия, и принимает значение "Истина", если значение его левого операнда присутствует в списке значений из правого операнда. Список значений может задаваться в виде:

• списка выражений;
• параметра, значением которого является коллекция значений;
• вложенного запроса.

Если список значений задан вложенным запросом, то возможно использование обобщенной формы оператора вхождения - группового оператора вхождения:

(<Выражение 1>, ..., <Выражение N>) В (<Вложенный запрос>)

Вложенный запрос должен определять выборку из N колонок.

Групповой оператор вхождения принимает значение "Истина", если запись (<Выражение 1>, ..., <Выражение N>) совпадает с одной из записей выборки, определяемой вложенным запросом. Например, пусть левым операндом является список:

(1, 2, 3)

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

1, 1, 1
2, 2, 2
2, 2, 3

то результатом группового оператора вхождения будет "Ложь", а если результатом вложенного запроса является выборка:

1, 1, 1
1, 2, 3
2, 2, 3

то результатом группового оператора вхождения будет "Истина".

Наряду с оператором вхождения ("В") в языке запросов предусмотрен оператор не вхождения ("НЕ В"), результатом которого является отрицание оператора "В" с такими же операндами. Все, сказанное про особенности выполнения оператора "В", относится и к оператору "НЕ В".

Реализация группового оператора вхождения

В отличие от простого оператора вхождения, аналог которого имеется в большинстве СУБД (оператор IN языка SQL), групповой оператор вхождения не имеет точных аналогов в SQL. Поэтому при использовании группового оператора вхождения важно учитывать механизм его перевода в SQL. Оператор вида:

(<Выражение 1>, ..., <Выражение N>) В (
    ВЫБРАТЬ <Колонка 1>, ..., <Колонка N>
    ИЗ <Источники>
    ГДЕ <Условие>
)

при выполнении его на СУБД будет иметь вид:

EXISTS(
    SELECT 1
    FROM <Источники>
    WHERE (<Условие>) AND <Выражение 1> = <Колонка 1> AND ... AND <Выражение N> = <Колонка N>
)

Если вложенный запрос содержит агрегатные функции и/или раздел СГРУППИРОВАТЬ ПО, то групповой оператор вхождения вида:

(<Выражение 1>, ..., <Выражение N>) В (
    ВЫБРАТЬ <Колонка 1>, ..., <Колонка N>
    ИЗ <Источники>
    ГДЕ <Условие 1>
    СГРУППИРОВАТЬ ПО <Список группировки>
    ИМЕЮЩИЕ <Условие 2>
)

на языке SQL будет записан так:

EXISTS(
    SELECT 1
    FROM <Источники>
    WHERE <Условие 1>
    GROUP BY <Список группировки>
    HAVING (<Условие 2>) AND <Выражение 1> = <Колонка 1> AND ... AND <Выражение N> = <Колонка N>
)

Замечания о скорости выполнения

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

Короткие таблицы

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

Индексы

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

• используйте в качестве значений колонок <Колонка 1>, ..., <Колонка N> только наименования полей таблиц (не выражения);
• выберите среди полей, используемых в качестве значений колонок <Колонка 1>, ..., <Колонка N> такое, значения которого реже всего повторяются (наиболее селективное поле), и включите индексирование по этому полю;
• не используйте агрегатные функции и группировки в запросе, вложенном в групповой оператор вхождения;
• включайте наиболее селективные поля в список группового оператора вхождения. Например, при проверке остатков на складах по позициям номенклатуры из табличной части документа "РасходныйОрдерНаТовары", на который указывает параметр "ДокументСсылка", следующий запрос будет выполняться медленно из-за того, что в таблице Документ.РасходныйОрдерНаТовары.Товары по группе полей (Номенклатура, ХарактеристикаНоменклатуры, Качество) невозможно построить индекс.

ВЫБРАТЬ
Док.Номенклатура.Представление КАК НоменклатураПредставление,
Док.Номенклатура.ЕдиницаХраненияОстатков.Представление КАК ЕдиницаХраненияОстатковПредставление,
Док.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
Док.СерияНоменклатуры КАК СерияНоменклатуры,
Док.Качество КАК Качество,
СУММА(ВЫРАЗИТЬ(Док.Количество * Док.Коэффициент / Док.Номенклатура.ЕдиницаХраненияОстатков.Коэффициент КАК ЧИСЛО(15,3))) КАК ДокументКоличество,
ЕСТЬNULL(МАКСИМУМ(Остатки.КоличествоОстаток), 0) КАК ОстатокКоличество
ИЗ 
Документ.РасходныйОрдерНаТовары.Товары КАК Док
ЛЕВОЕ СОЕДИНЕНИЕ
РегистрНакопления.ТоварыКПередачеСоСкладов.Остатки(,
  Склад = &Склад И (Номенклатура, ХарактеристикаНоменклатуры, Качество) В (
    ВЫБРАТЬ
    Док.Номенклатура, Док.ХарактеристикаНоменклатуры, Док.Качество
    ИЗ
    Документ.РасходныйОрдерНаТовары.Товары КАК Док
    ГДЕ
    Док.Ссылка = &ДокументСсылка
    И НЕ Док.Номенклатура.Комплект 
  ) И &ДокументПередачи = ДокументПередачи) КАК Остатки
ПО 
Док.Номенклатура = Остатки.Номенклатура
И Док.ХарактеристикаНоменклатуры = Остатки.ХарактеристикаНоменклатуры
И Док.Качество = Остатки.Качество
И ((НЕ Док.СерияУказываетсяПриОтпускеСоСклада И Остатки.СерияНоменклатуры = Док.СерияНоменклатуры) ИЛИ
   (Док.СерияУказываетсяПриОтпускеСоСклада И Остатки.СерияНоменклатуры = &ПустаяСерия))
ГДЕ
Док.Ссылка = &ДокументСсылка
СГРУППИРОВАТЬ ПО
Док.Номенклатура,
Док.ХарактеристикаНоменклатуры,
Док.СерияНоменклатуры,
Док.Качество,
&ДокументПередачи
ИМЕЮЩИЕ
ЕСТЬNULL(МАКСИМУМ(Остатки.КоличествоОстаток), 0) < СУММА(ВЫРАЗИТЬ(Док.Количество * Док.Коэффициент / Док.Номенклатура.ЕдиницаХраненияОстатков.Коэффициент КАК ЧИСЛО(15,3)))

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

ВЫБРАТЬ
Док.Номенклатура.Представление КАК НоменклатураПредставление,
Док.Номенклатура.ЕдиницаХраненияОстатков.Представление КАК ЕдиницаХраненияОстатковПредставление,
Док.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
Док.СерияНоменклатуры КАК СерияНоменклатуры,
Док.Качество КАК Качество,
СУММА(ВЫРАЗИТЬ(Док.Количество * Док.Коэффициент / Док.Номенклатура.ЕдиницаХраненияОстатков.Коэффициент КАК ЧИСЛО(15,3))) КАК ДокументКоличество,
ЕСТЬNULL(МАКСИМУМ(Остатки.КоличествоОстаток), 0) КАК ОстатокКоличество
ИЗ 
Документ.РасходныйОрдерНаТовары.Товары КАК Док
ЛЕВОЕ СОЕДИНЕНИЕ
РегистрНакопления.ТоварыКПередачеСоСкладов.Остатки(,
  (Склад, Номенклатура, Качество, ХарактеристикаНоменклатуры) В (
    ВЫБРАТЬ
    Док.Ссылка.Склад, Док.Номенклатура, Док.Качество, Док.ХарактеристикаНоменклатуры
    ИЗ
    Документ.РасходныйОрдерНаТовары.Товары КАК Док
    ГДЕ
    Док.Ссылка = &ДокументСсылка
    И НЕ Док.Номенклатура.Комплект 	
  ) И &ДокументПередачи = ДокументПередачи) КАК Остатки
ПО 
Док.Ссылка.Склад = Остатки.Склад
И Док.Номенклатура = Остатки.Номенклатура
И Док.Качество = Остатки.Качество
И Док.ХарактеристикаНоменклатуры = Остатки.ХарактеристикаНоменклатуры
И ((НЕ Док.СерияУказываетсяПриОтпускеСоСклада И Остатки.СерияНоменклатуры = Док.СерияНоменклатуры) ИЛИ
   (Док.СерияУказываетсяПриОтпускеСоСклада И Остатки.СерияНоменклатуры = &ПустаяСерия))
ГДЕ
Док.Ссылка = &ДокументСсылка
СГРУППИРОВАТЬ ПО
Док.Номенклатура,
Док.ХарактеристикаНоменклатуры,
Док.СерияНоменклатуры,
Док.Качество,
&ДокументПередачи
ИМЕЮЩИЕ
ЕСТЬNULL(МАКСИМУМ(Остатки.КоличествоОстаток), 0) < СУММА(ВЫРАЗИТЬ(Док.Количество * Док.Коэффициент / Док.Номенклатура.ЕдиницаХраненияОстатков.Коэффициент КАК ЧИСЛО(15,3)))

Временные таблицы

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

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