Ограничения на соединения с вложенными запросами и виртуальными таблицами
Область применения: управляемое приложение, мобильное приложение, обычное приложение.
1.1. При написании запросов не следует использовать соединения с вложенными запросами. Следует соединять друг с другом только объекты метаданных или временные таблицы. Если запрос использует соединения с вложенными запросами, то его следует переписать с использованием временных таблиц (не важно с какой стороны соединения находится вложенный запрос), кроме случая, когда вложенный запрос сканирует мало записей.
Если запрос содержит соединения с вложенными запросами, то это может привести к следующим негативным последствиям:
- Крайне медленное выполнение запроса при слабой загрузке серверного оборудования. Замедление запроса может быть очень значительным (до нескольких порядков);
- Нестабильная работа запроса. При некоторых условиях запрос может работать достаточно быстро, при других — очень медленно;
- Значительная разница по времени выполнения запроса на разных СУБД;
- Повышенная чувствительность запроса к актуальности и полноте статистик. Сразу после полного обновления статистик запрос может работать быстро, но через некоторое время опять замедлиться.
Пример потенциально опасного запроса, использующего соединение с вложенным запросом:
ВЫБРАТЬ .
ИЗ Документ.РеализацияТоваровУслуг
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ ИЗ РегистрСведений.Лимиты
ГДЕ .
СГРУППИРОВАТЬ ПО .
) ПО .
Оптимизатор сервера СУБД (независимо от того, какую СУБД вы используете) не всегда может правильно оптимизировать подобный запрос. В данном случае, проблемой для оптимизатора является выбор правильного способа соединения. Существуют несколько алгоритмов соединения двух выборок. Выбор того или иного алгоритма зависит от того, сколько записей будет содержаться в одной и в другой выборке. В том случае, если вы соединяете две физические таблицы, СУБД может легко определить объем обоих выборок на основании имеющейся статистики. Если же одна из соединяемых выборок представляет собой вложенный запрос, то понять, какое количество записей она вернет, становится очень сложно. В этом случае СУБД может ошибиться с выбором плана, что приведет к катастрофическому падению производительности запроса.
1.2. Для вышеприведенного примера получится следующий пакетный запрос:
// Создать менеджер временных таблиц
МенеджерВТ = Новый МенеджерВременныхТаблиц;
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = МенеджерВТ;
// Текст пакетного запроса
Запрос.Текст
Переписывание запроса по приведенной выше методике имеет своей целью упростить работу оптимизатору СУБД. В переписанном запросе все выборки, участвующие в соединениях будут представлять собой физические таблицы, и СУБД сможет легко определить размер каждой выборки. Это позволит СУБД гарантированно выбрать самый быстрый из всех возможных планов. Причем, СУБД будет делать правильный выбор независимо ни от каких условий. Переписанный подобным образом запрос будет работать одинаково хорошо на любых СУБД, что особенно важно при разработке тиражных решений. Кроме того, переписанный подобным образом запрос лучше читается, проще для понимания и отладки.
2. Если в запросе используется соединение с виртуальной таблицей языка запросов 1С:Предприятия (например, РегистрНакопления.Товары.Остатки ) и запрос работает с неудовлетворительной производительностью, то рекомендуется вынести обращение к виртуальной таблице в отдельный запрос с сохранением результатов во временной таблице (см. пункт 1.1).
3. Следует избегать неявных подзапросов, которые получаются при использовании вложенных соединений:
ВЫБРАТЬ .
ИЗ Справочник.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыОрганизаций
ПО .
ПО .
Проблема в том, что, по сути, этот запрос аналогичен следующему:
ВЫБРАТЬ .
ИЗ Справочник.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ .
ИЗ РегистрНакопления.ТоварыНаСкладах
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыОрганизаций
ПО . )
ПО .
Вместо вложенных соединений, как показано выше, следует использовать последовательные соединения:
ВЫБРАТЬ .
ИЗ Справочник.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах
ПО .
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыОрганизаций
ПО .
При этом следует понимать, что вложенные и последовательные соединения – это разные запросы, которые могут дать разный результат.
Если вложенное соединение использовано из предположения, что оно аналогично последовательному соединению, то следует просто переписать его на последовательное соединение.
Если вложенное соединение делается осмысленно, то от него следует отказаться, т.к. оно может существенно снизить производительность, как и соединение с подзапросом. Как и в случае с подзапросом, такое соединение можно заменить на соединение с временной таблицей, но лучше вначале подумать, как заменить его на последовательное соединение, т.к. оно будет работать эффективнее временной таблицы.
См. также
- Использование вложенных запросов в условии соединения
- Использование временных таблиц
Вложенные запросы
Вложенным называется запрос, который является источником выборки основного запроса. Допускается произвольная вложенность таких запросов.
- Предназначен для предварительной обработке данных, либо для более удобного восприятия.
- Может использоваться в качестве параметра условия В()
- Синтаксис вложенного запроса:
- Прочие сведения о вложенных запросах
- Зачем может потребоваться вложенный запрос в приоритетном порядке:
Синтаксис вложенного запроса:
ВЫБРАТЬ * из (ВЫБРАТЬ 1) КАК ВложенныйЗапрос
ВЫБРАТЬ * ИЗ Справочник.Контрагенты ГДЕ Ссылка В (ВЫБРАТЬсколько с Ссылка ИЗ Справочник.Контрагенты)
ВЫБРАТЬ * ИЗ Справочник.Контрагенты ГДЕ (Ссылка,Наименование) В (ВЫБРАТЬ Ссылка,Наименование ИЗ Справочник.Контрагенты)
- Общее правило — расположение в скобках.
- В качестве источника должен обязательно иметь псевдоним запроса.
- В качестве условия вхождения он должен иметь столько полей сколько значений проверяется (либо будет ошибка «В запросах операции «В» должно выбираться столько полей, сколько операндов имеет левая часть операции»).
В конструкторе вложенный запрос создается на основной вкладке по специальной кнопке:
Иконка у таблицы вложенного запроса отличается от других таблиц:
Прочие сведения о вложенных запросах
Считается, что вложенные запросы менее оптимизированы на скорость, но с появлением платформы 8.3 данный фактор справедлив только для Postgres SQL (возможно особенность конкретной сборки).
В целом, в большинстве случаев, запросы можно переписать на использование временных таблиц, но не для запроса динамического списка управляемого интерфейса (там это запрещено на уровне платформы). Вложенный запрос же использовать в нем допустимо, если он не вызывает дублей ключевых полей (Ссылка).
Если вложенный запрос основной, то основная таблица динамического списка будет пустой, что отключит событийные команды такого списка.
(для примера выше, нельзя будет добавить новый элемент или группу).
Допускается вложенный запрос и при соединениях таблиц.
Например:
ВЫБРАТЬ * ИЗ Справочник.Контрагенты КАК Основная ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ * ИЗ Справочник.Контрагенты ) КАК Вложенный ПО Основная.Ссылка = Вложенный.Ссылка
Примеры выше подобраны в учебных целях: максимально простого синтаксиса, а не для решения какой-либо задачи.
Зачем может потребоваться вложенный запрос в приоритетном порядке:
В случае использования результата агрегатной функции в качестве условия соединения таблиц:
- Для простого отбора можно воспользоваться конструкцией ИМЕЮЩИЕ.
Если же нам необходимо посчитать остаток в рознице + в опте (в торговле) и вывести только, те у которых общий остаток меньше 10, без вложенного запроса невозможно будет обойтись. - Условия в запросах виртуальных таблиц работают лучше и быстрее, если используется В(), вместо обращения через точку:
ВЫБРАТЬ * ИЗ РегистрСведений.КурсыВалют.СрезПоследних(, Валюта В(ВЫБРАТЬ ссылка из Справочник.Валюты. ГДЕ Наименование = «Рубль»))
ВЫБРАТЬ * ИЗ РегистрСведений.КурсыВалют.СрезПоследних(, Валюта.Наименование = «Рубль»)
Половина денег, которые идут на рекламу, выбрасываются впустую; но как узнать, какая именно половина?
— Уильям Гескет Левер
Запросы 1С:Предприятие 8. Вложенные запросы
Вложенные запросы – это запросы, вызываемые другим, внешним, запросом. Они всегда заключаются в круглые скобки и им обязательно должен присваиваться псевдоним. Некоторые считают вложенный запрос аналогом временных таблиц, однако эти два инструмента имеют ряд отличий, которые мы рассмотрим в данной статье.
Вложенный запрос видит только себя, он не видит внешний запрос. Это значит, что нельзя, например, установить во вложенном запросе условие по значению поля внешнего запроса.
Большинство представленных запросов не имеют какой-либо ценности и могли бы быть выполнены проще. Они приведены только для иллюстрации механизма вложенных запросов.
Вложенные запросы могут использоваться в конструкции ИЗ:
Запрос . Текст = «ВЫБРАТЬ
ВложенныйЗапрос.Поле1,
ВложенныйЗапрос.Поле2
ИЗ
(ВЫБРАТЬ
Таблица1.Поле1,
Таблица1.Поле2
ИЗ ТаблицаДанных КАК Таблица1) КАК ВложенныйЗапрос» ;
В том числе в соединениях:
Запрос . Текст = «ВЫБРАТЬ
ВложенныйЗапрос.Наименование,
ЧерныйСписокСрезПоследних.Состояние
ИЗ
(ВЫБРАТЬ
Контрагенты.Ссылка КАК Ссылка,
Контрагенты.Наименование КАК Наименование
ИЗ
Справочник.Контрагенты КАК Контрагенты) КАК ВложенныйЗапрос
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЧерныйСписок.СрезПоследних КАК ЧерныйСписокСрезПоследних
ПО ВложенныйЗапрос.Ссылка = ЧерныйСписокСрезПоследних.Котрагент» ;
И в условиях запроса со сравнением В или В ИЕРАРХИИ:
Запрос . Текст = «ВЫБРАТЬ
ЧерныйСписокСрезПоследних.Состояние
ИЗ
РегистрСведений.ЧерныйСписок.СрезПоследних КАК ЧерныйСписокСрезПоследних
ГДЕ
ЧерныйСписокСрезПоследних.Котрагент В
(ВЫБРАТЬ ПЕРВЫЕ 10
Контрагенты.Ссылка
ИЗ
Справочник.Контрагенты КАК Контрагенты)» ;
При этом количество выбираемых полей вложенного запроса должно соответствовать количеству операндов в левой части выражения В или В ИЕРАРХИИ.
Существует мнение, что вложенные запросы в сложных конструкциях выполняются платформой 1С нерационально, требуют бОльших ресурсов и времени, нежели те же самые запросы, выполненные иначе, без использования вложенных запросов. Однако в ряде случаев, обойтись без вложенных запросов невозможно.
Вместе с тем, обычно эффективнее работает один большой запрос с вложенными, чем последовательность запросов из модуля.
Практически всегда альтернативой вложенному запросу является использование временных таблиц. Этот инструмент имеет ряд преимуществ:
- Запрос становится более структурированным, его легче читать.
- Результат, загруженный во временную таблицу можно использовать несколько раз, и при этом нет необходимости заново выполнять запрос, чтобы этот результат получить. А вложенный запрос будет каждый раз выполняться заново, излишне загружая ресурсы системы.
Подведем итог: вложенные запросы лучше всего применять в достаточно простых конструкциях, при этом использовать их стоит только тогда, когда по-другому задачу не решить; в сложных запросах лучше использовать временные таблицы.
Раздел: Программирование Статьи по 1С Метки: Основы программирования в 1С, язык запросов 1С
Запросы 1С:Предприятие 8. Вложенные запросы : 1 комментарий
- Евгений02.04.2018 При соединении подзапроса с другой таблицей на уровне СУБД всегда будет использоваться способ соединения nested loops, т.к для подзапроса нет статистики. Хорошо это или плохо — зависит от самого запроса и количества выбираемых данных. Именно поэтому при выполнении запроса, содержащего подзапрос, может проявляться замедление.
Добавить комментарий Отменить ответ
Теперь мы в соцсетях! Подписывайтесь, чтобы получать информацию о последних обновлениях или задать вопрос.
Вложенные запросы в конструкторе
В цикле статей о языке запросов 1С было рассмотрено как создавать вложенные запросы. Сегодня поговорим о том как делать вложенные запросы с использованием конструктора запросов. Перейдем сразу к примеру.
Допустим у нас есть вот такой простейший периодический регистр сведений, где хранятся цены в разрезе товаров и поставщиков:
Мы хотим запросом получить все товары у которых количество поставщиков больше одного. Это можно реализовать с помощью вот такого запроса:
ВЫБРАТЬ КоличествоПоставщиков.Товар КАК Товар ИЗ (ВЫБРАТЬ Цена.Товар КАК Товар, КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Цена.Поставщик) КАК Поставщики ИЗ РегистрСведений.Цена КАК Цена СГРУППИРОВАТЬ ПО Цена.Товар) КАК КоличествоПоставщиков ГДЕ КоличествоПоставщиков.Поставщики > 1
Формируем вложенный запрос в конструкторе
Сформируем вышеприведенный запрос с помощью конструктора.
Для этого в командной панели над полем Таблицы нажимаем кнопку Создать вложенный запрос: 
После чего у нас откроется окно с еще одним экземпляром конструктора запросов: 
И в этом новом окне конструируем вложенный запрос: 
Нажав на кнопку Запрос в нижнем левом углу мы можем посмотреть текст вложенного запроса: 
После нажатия на кнопку ОК во вспомогательном конструкторе, получаем в основном окне следующую картину: 
Поскольку словосочетание ВложенныйЗапрос не очень удобно для восприятия, давайте с помощью клика правой кнопкой мыши переименуем таблицу в КоличествоПоставщиков, выберем из нее поле Товар и на закладке Условия пропишем нужное условие: 
И после всех этих манипуляций мы и получаем нужный запрос. При необходимости можно создавать запросы с несколькими уровнями вложенности.
Как из обычного запроса сделать вложенный в конструкторе
Очень часто возникает ситуация, когда начинаешь делать запрос в конструкторе и в какой то момент понимаешь, что он должен быть вложенным. Конечно в нашем примере никакой проблемы нет — можно просто все удалить и накидать запрос заново. Но на практике встречаются гораздо более сложные примеры, например, с несколькими уровнями вложенности, когда на то чтобы сделать запрос было потрачено несколько часов. И в этом случае есть достаточно простой выход. Можно воспользоваться встроенным в конструктор текстовым редактором запросов. Надо с помощью кнопки Запрос получить текст запроса (см. картинку выше) и скопировать его в буфер. Далее создаем новый вложенный запрос опять нажимаем кнопку Запрос, вставляем текст из буфера, жмем ОК. Соответственно старый запрос на верхнем уровне зачищаем. Таким образом мы при необходимости можем легко на лету создавать многоуровневые вложенные запросы.
4 комментария на «“Вложенные запросы в конструкторе”»
Должно быть без РАЗЛИЧНЫЕ ВЫБРАТЬ
КоличествоПоставщиков.Товар КАК Товар
ИЗ
(ВЫБРАТЬ
Цена.Товар КАК Товар,
КОЛИЧЕСТВО(Цена.Поставщик) КАК Поставщики
ИЗ
РегистрСведений.Цена КАК Цена СГРУППИРОВАТЬ ПО
Цена.Товар) КАК КоличествоПоставщиков
ГДЕ
КоличествоПоставщиков.Поставщики
Это справедливо, если регистр сведений непериодический. В этом случае мы не сможем несколько раз добавить записи с одинаковым набором измерений (товар + поставщик). Я отдельно не оговаривал, что у меня регистр периодический (сейчас добавлю). А для периодического регистра сведений «РАЗЛИЧНЫЕ» будет необходимо.
При добавлении «Различные» запрос выдаст пустой результат с таким параметром.
Цитата из статьи: «Мы хотим запросом получить все товары у которых количество поставщиков больше одного».
Если у вас для одного товара всегда один и тот же поставщик, то да выдаст пустой результат. Так и задумывалось.
Проверяется легко в консоли: ВЫБРАТЬ
«Ручка» КАК Товар,
«ООО Ручки» КАК Поставщик
ПОМЕСТИТЬ ВТ_Цена ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ
«Ручка»,
«ООО Канцтовары»
; ////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
КоличествоПоставщиков.Товар КАК Товар
ИЗ
(ВЫБРАТЬ
Цена.Товар КАК Товар,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Цена.Поставщик) КАК Поставщики
ИЗ
ВТ_Цена КАК Цена
СГРУППИРОВАТЬ ПО
Цена.Товар) КАК КоличествоПоставщиков
ГДЕ
КоличествоПоставщиков.Поставщики > 1