Вложенный запрос в где 1с

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

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

1.1. При написании запросов не следует использовать соединения с вложенными запросами. Следует соединять друг с другом только объекты метаданных или временные таблицы. Если запрос использует соединения с вложенными запросами, то его следует переписать с использованием временных таблиц (не важно с какой стороны соединения находится вложенный запрос), кроме случая, когда вложенный запрос сканирует мало записей.

Если запрос содержит соединения с вложенными запросами, то это может привести к следующим негативным последствиям:

• Крайне медленное выполнение запроса при слабой загрузке серверного оборудования. Замедление запроса может быть очень значительным (до нескольких порядков);
• Нестабильная работа запроса. При некоторых условиях запрос может работать достаточно быстро, при других — очень медленно;
• Значительная разница по времени выполнения запроса на разных СУБД;
• Повышенная чувствительность запроса к актуальности и полноте статистик. Сразу после полного обновления статистик запрос может работать быстро, но через некоторое время опять замедлиться.

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

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

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

1.2. Для вышеприведенного примера получится следующий пакетный запрос:

// Создать менеджер временных таблиц
МенеджерВТ = Новый МенеджерВременныхТаблиц;
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = МенеджерВТ;
// Текст пакетного запроса
Запрос.Текст

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

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

3. Следует избегать неявных подзапросов, которые получаются при использовании вложенных соединений:

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

Проблема в том, что, по сути, этот запрос аналогичен следующему:

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

Вместо вложенных соединений, как показано выше, следует использовать последовательные соединения:

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

При этом следует понимать, что вложенные и последовательные соединения – это разные запросы, которые могут дать разный результат.

Если вложенное соединение использовано из предположения, что оно аналогично последовательному соединению, то следует просто переписать его на последовательное соединение.

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

См. также

• Использование вложенных запросов в условии соединения
• Использование временных таблиц

Вложенный запрос в где 1с

Почему 1С ругается на конструкцию типа

ВЫБРАТЬ
ПерваяТаб.А КАК А
ПерваяТаб.Б КАК Б
(ВЫБРАТЬ ПЕРВЫЕ 1 ВтораяТаб.С
ИЗ ВтораяТаб
ГДЕ (ПерваяТаб.А = ВтораяТаб.А) И (ПерваяТаб.Б = ВтораяТаб.Б)) КАК С
ИЗ ПерваяТаб

Ругается именно на ВЫБРАТЬ в скобках. Нельзя в качестве поля запроса использовать вложенный запрос?

Запятые забыл расставить. Вопрос в силе.
ВЫБРАТЬ
ПерваяТаб.А КАК А,
ПерваяТаб.Б КАК Б,
(ВЫБРАТЬ ПЕРВЫЕ 1 ВтораяТаб.С
ИЗ ВтораяТаб
ГДЕ (ПерваяТаб.А = ВтораяТаб.А) И (ПерваяТаб.Б = ВтораяТаб.Б)) КАК С
ИЗ ПерваяТаб

Вложенные запросы в 1С 8.3

Вложенный запрос – это запрос, который вложен в другой запрос. Синтаксически выглядит, как запрос, заключенный в круглые скобки. Ему присваивается псевдоним. Как правило, используется для получения отборов, группировок и агрегатных функций с последующим соединением в запросе верхнего уровня, также в операторах «В», «ИЗ», «ГДЕ» и других.

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

 ВЫБРАТЬ ПартииТоваров.Номенклатура КАК Номенклатура, ПодЗапрос.ПриходнаяНакладная КАК Приходная, ПодЗапрос.КоличествоПриход КАК Приход, ВЫРАЗИТЬ(ПартииТоваров.Регистратор КАК Документ.РеализацияТоваров) КАК Регистратор, ПартииТоваров.КоличествоРасход КАК КоличествоРасход ИЗ (ВЫБРАТЬ ПЕРВЫЕ 5 ПартииТоваров.Номенклатура КАК Товар, ПартииТоваров.ДокументПоступления КАК ПриходнаяНакладная, МАКСИМУМ(ПартииТоваров.КоличествоПриход) КАК КоличествоПриход ИЗ РегистрНакопления.ПартииТоваров.Обороты(&НачалоПериода, &КонецПериода, Регистратор,Номенклатура В (&Товары)) КАК ПартииТоваров СГРУППИРОВАТЬ ПО ПартииТоваров.Номенклатура, ПартииТоваров.ДокументПоступления УПОРЯДОЧИТЬ ПО КоличествоПриход УБЫВ) КАК ПодЗапрос ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ПартииТоваров.Обороты(, , Регистратор,Номенклатура В (&Товары)) КАК ПартииТоваров ПО ПодЗапрос.Товар = ПартииТоваров.Номенклатура И ПодЗапрос.ПриходнаяНакладная = ПартииТоваров.ДокументПоступления 

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

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

Еще пример вложенного запроса с использованием оператора-условия «В» в свойствах виртуальной таблицы регистра накопления. Вложенный запрос здесь используется как отбор данных, который будет использован в запросе верхнего уровня.

 ВЫБРАТЬ ПодЗапрос.Товар КАК Товар, ВЫБОР КОГДА ЕСТЬNULL(ТоварыНаСкладах.ВНаличииОстаток, 0) >= ПодЗапрос.Количество ТОГДА "Товар достаточно" ИНАЧЕ "Товара не достаточно" КОНЕЦ КАК Достаточность ИЗ (ВЫБРАТЬ РасходнаяНакладная.Номенклатура КАК Товар, СУММА(РасходнаяНакладная.Количество) КАК Количество ИЗ Документ.РеализацияТоваровУслуг.Товары КАК РасходнаяНакладная ГДЕ РасходнаяНакладная.Ссылка = &Документ СГРУППИРОВАТЬ ПО РасходнаяНакладная.Номенклатура) КАК ПодЗапрос ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&ВыбДата, ) КАК ТоварыНаСкладах ПО ПодЗапрос.Товар = ТоварыНаСкладах.Номенклатура 

Фирма 1С не рекомендует использовать вложенные запросы без особой потребности и предлагает заменять их временными таблицами или соединениями таблиц, замечая при этом, что результат такого изменения может быть другим. Такая рекомендация объясняется тем, что при использовании вложенных запросов оптимизатор СУБД не всегда может правильно определить размер выборки вложенного запроса и построить оптимальный план обращений к физическим таблицам базы данных, что сильно (иногда в десятки раз) может замедлить выполнение запроса.

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

Тем не менее, вложенные запросы по-прежнему востребованы и используются в случаях:

• При работе в 1С:Предприятие с версиями платформы 1С 8.3.7 и ниже и при использовании обратной совместимости с такими платформами по-прежнему невозможно использовать временные таблицы в динамических списках;
• Во вложенных динамических списках эффективность применения временных таблиц может быть сильно снижена из-за того, что таблицы требуется создавать при каждом вызове (и поэтому данные не кэшируются). В противном случае данные сохраненной таблицы могут оказаться уже не актуальными;
• Вложенные запросы нужно применять для заведомо небольших выборок, где временные таблицы менее эффективны, так как занимают оперативную память, а при использовании индексирования на больших выборках могут выгружать индексные файлы на носитель, что критически скажется на скорости получения данных из запроса.

Нужно заметить, что еще существуют неявные вложенные запросы. Это:

Запросы с использованием вложенных соединений. Конструкция вида:

 ВЫБРАТЬ Поле1, Поле2, … ИЗ Источник1 ЛЕВОЕ СОЕДИНЕНИЕ Источник_2 ЛЕВОЕ СОЕДИНЕНИЕ Источник_3 ПО (условия для Источник_2 = условие Источник_3) ПО (условия для Источник_2 = условие Источник_1) 

Пример в 1С УТ (ред. 11.2.3.300):

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

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

Запросы 1С:Предприятие 8. Вложенные запросы

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

Вложенный запрос видит только себя, он не видит внешний запрос. Это значит, что нельзя, например, установить во вложенном запросе условие по значению поля внешнего запроса.

Большинство представленных запросов не имеют какой-либо ценности и могли бы быть выполнены проще. Они приведены только для иллюстрации механизма вложенных запросов.

Вложенные запросы могут использоваться в конструкции ИЗ:

Запрос . Текст = «ВЫБРАТЬ
ВложенныйЗапрос.Поле1,
ВложенныйЗапрос.Поле2
ИЗ
(ВЫБРАТЬ
Таблица1.Поле1,
Таблица1.Поле2
ИЗ ТаблицаДанных КАК Таблица1) КАК ВложенныйЗапрос» ;

В том числе в соединениях:

Запрос . Текст = «ВЫБРАТЬ
ВложенныйЗапрос.Наименование,
ЧерныйСписокСрезПоследних.Состояние
ИЗ
(ВЫБРАТЬ
Контрагенты.Ссылка КАК Ссылка,
Контрагенты.Наименование КАК Наименование
ИЗ
Справочник.Контрагенты КАК Контрагенты) КАК ВложенныйЗапрос
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЧерныйСписок.СрезПоследних КАК ЧерныйСписокСрезПоследних
ПО ВложенныйЗапрос.Ссылка = ЧерныйСписокСрезПоследних.Котрагент» ;

И в условиях запроса со сравнением В или В ИЕРАРХИИ:

Запрос . Текст = «ВЫБРАТЬ
ЧерныйСписокСрезПоследних.Состояние
ИЗ
РегистрСведений.ЧерныйСписок.СрезПоследних КАК ЧерныйСписокСрезПоследних
ГДЕ
ЧерныйСписокСрезПоследних.Котрагент В
(ВЫБРАТЬ ПЕРВЫЕ 10
Контрагенты.Ссылка
ИЗ
Справочник.Контрагенты КАК Контрагенты)» ;

При этом количество выбираемых полей вложенного запроса должно соответствовать количеству операндов в левой части выражения В или В ИЕРАРХИИ.

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

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

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

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

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

Раздел: Программирование Статьи по 1С Метки: Основы программирования в 1С, язык запросов 1С

Запросы 1С:Предприятие 8. Вложенные запросы : 1 комментарий

1. Евгений02.04.2018 При соединении подзапроса с другой таблицей на уровне СУБД всегда будет использоваться способ соединения nested loops, т.к для подзапроса нет статистики. Хорошо это или плохо — зависит от самого запроса и количества выбираемых данных. Именно поэтому при выполнении запроса, содержащего подзапрос, может проявляться замедление.

Добавить комментарий Отменить ответ

Теперь мы в соцсетях! Подписывайтесь, чтобы получать информацию о последних обновлениях или задать вопрос.