Ограничения на соединения с вложенными запросами и виртуальными таблицами
Область применения: управляемое приложение, мобильное приложение, обычное приложение.
1.1. При написании запросов не следует использовать соединения с вложенными запросами. Следует соединять друг с другом только объекты метаданных или временные таблицы. Если запрос использует соединения с вложенными запросами, то его следует переписать с использованием временных таблиц (не важно с какой стороны соединения находится вложенный запрос), кроме случая, когда вложенный запрос сканирует мало записей.
Если запрос содержит соединения с вложенными запросами, то это может привести к следующим негативным последствиям:
- Крайне медленное выполнение запроса при слабой загрузке серверного оборудования. Замедление запроса может быть очень значительным (до нескольких порядков);
- Нестабильная работа запроса. При некоторых условиях запрос может работать достаточно быстро, при других — очень медленно;
- Значительная разница по времени выполнения запроса на разных СУБД;
- Повышенная чувствительность запроса к актуальности и полноте статистик. Сразу после полного обновления статистик запрос может работать быстро, но через некоторое время опять замедлиться.
Пример потенциально опасного запроса, использующего соединение с вложенным запросом:
ВЫБРАТЬ .
ИЗ Документ.РеализацияТоваровУслуг
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ ИЗ РегистрСведений.Лимиты
ГДЕ .
СГРУППИРОВАТЬ ПО .
) ПО .
Оптимизатор сервера СУБД (независимо от того, какую СУБД вы используете) не всегда может правильно оптимизировать подобный запрос. В данном случае, проблемой для оптимизатора является выбор правильного способа соединения. Существуют несколько алгоритмов соединения двух выборок. Выбор того или иного алгоритма зависит от того, сколько записей будет содержаться в одной и в другой выборке. В том случае, если вы соединяете две физические таблицы, СУБД может легко определить объем обоих выборок на основании имеющейся статистики. Если же одна из соединяемых выборок представляет собой вложенный запрос, то понять, какое количество записей она вернет, становится очень сложно. В этом случае СУБД может ошибиться с выбором плана, что приведет к катастрофическому падению производительности запроса.
1.2. Для вышеприведенного примера получится следующий пакетный запрос:
// Создать менеджер временных таблиц
МенеджерВТ = Новый МенеджерВременныхТаблиц;
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = МенеджерВТ;
// Текст пакетного запроса
Запрос.Текст
Переписывание запроса по приведенной выше методике имеет своей целью упростить работу оптимизатору СУБД. В переписанном запросе все выборки, участвующие в соединениях будут представлять собой физические таблицы, и СУБД сможет легко определить размер каждой выборки. Это позволит СУБД гарантированно выбрать самый быстрый из всех возможных планов. Причем, СУБД будет делать правильный выбор независимо ни от каких условий. Переписанный подобным образом запрос будет работать одинаково хорошо на любых СУБД, что особенно важно при разработке тиражных решений. Кроме того, переписанный подобным образом запрос лучше читается, проще для понимания и отладки.
2. Если в запросе используется соединение с виртуальной таблицей языка запросов 1С:Предприятия (например, РегистрНакопления.Товары.Остатки ) и запрос работает с неудовлетворительной производительностью, то рекомендуется вынести обращение к виртуальной таблице в отдельный запрос с сохранением результатов во временной таблице (см. пункт 1.1).
3. Следует избегать неявных подзапросов, которые получаются при использовании вложенных соединений:
ВЫБРАТЬ .
ИЗ Справочник.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыОрганизаций
ПО .
ПО .
Проблема в том, что, по сути, этот запрос аналогичен следующему:
ВЫБРАТЬ .
ИЗ Справочник.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ .
ИЗ РегистрНакопления.ТоварыНаСкладах
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыОрганизаций
ПО . )
ПО .
Вместо вложенных соединений, как показано выше, следует использовать последовательные соединения:
ВЫБРАТЬ .
ИЗ Справочник.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах
ПО .
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыОрганизаций
ПО .
При этом следует понимать, что вложенные и последовательные соединения – это разные запросы, которые могут дать разный результат.
Если вложенное соединение использовано из предположения, что оно аналогично последовательному соединению, то следует просто переписать его на последовательное соединение.
Если вложенное соединение делается осмысленно, то от него следует отказаться, т.к. оно может существенно снизить производительность, как и соединение с подзапросом. Как и в случае с подзапросом, такое соединение можно заменить на соединение с временной таблицей, но лучше вначале подумать, как заменить его на последовательное соединение, т.к. оно будет работать эффективнее временной таблицы.
См. также
- Использование вложенных запросов в условии соединения
- Использование временных таблиц
Вложенный запрос или временная таблица что лучше 1с
один человек упорно пишет запросы с кучей временных таблиц и ни разу не применяет вложенные запросы. В связ с этим возникает вопрос: это лучше работает?
Я за временные таблицы. По крайней мере, если база на скуле. Скуль не умеет составить оптимальный план выполнения запроса, если используются вложенные запросы. Исключение — вложенный запрос, использующий временную таблицу.
(46) Причиной распухания темпдб являются не столько временные таблицы, сколько как раз вложенные запросы. А что касается временных таблиц, то я их всегда уничтожаю, не полагаясь на автоматику.
Вложенный запрос или временная таблица что лучше 1с
Подскажите пожалуйста, в каких случаях следует использовать вложенный запрос, а в каких временные таблицы?
(0)я лично временные использую только когда запрос супер навороченный или скуль ругается на 255 таблиц.
(1)Я наоборот, минимум вложенных, все во временные.
Единственное могу сказать, что после мыслишь по другому, как-то проще, может это и плохо.
(0) Зависит от количества таких обращений. если «разово», то наверное удобно использовать вложенный запрос, что бы не тратить ресурсы на механизм временных таблиц — если таких обращений много — из лучше выносить во временную.
но опять же — для удобства отладки и логики иногда удобнее разворачивать навороты вложенных таблиц через временные таблицы
1. Если кто знает, какая позиция 1С по этому вопросу?
2. Просто у меня возник спор с коллегой, я говорил что если выборка данных используется только один раз — то он делает вложенным запросом, если к этой выборке обращаться приходится неоднократно — то веременная таблица.
Коллега же утверждал что если выборка небольшая (до 100 строк) то временная таблица будет быстрее отрабатывать.
чем я руководствовался:
Дял создания временной таблицы требуется
1- создать менеджер временных таблиц
2-запросом выбрать данные в неё
3 записать во временную память результат
4 сделать основной запрос к БД + к этому обекту (временной таблице)
в если делать вложенным запросом — то только один запрос к БД
тут даже вопрос не в тех сотых долях секунды =) а именно в подходе
(5) Не спорь больше с другом.
Если мой подход неверен — я с радостью его пересмотрю и исправлюсь =)
(5) Если ВТ используется одноразово, то менеджер временных таблиц — не нужен
он не нужен если запрос пакетный,
в данном случае обсуждаем два запроса отдельных
для временных таблиц еще могут создаваться индексы
главным в использовании временных таблиц считаю исключение переборов и циклов
(11) SQL настолько суров, что обходится без переборов и циклов. Даже при работе с индексом.
(8) Временная таблица храниться в SQL, в базе tempdb, и, в зависимости от ситуации, может быть скинута на жесткий диск.
Поэтому — не все йогурты одинаково полезны.
Что для себя — то юзаю ВТ пакетно тогда, когда уже отфильтрованные данные нужны несколько раз, например в параметрах фильтра виртуальных таблиц по Остаткам на складе, и резервам на складе.
H A D G E H O G s — а если результат ВТ используешь один раз?
(12) давай я тебе помогу немножко http://tinyurl.com/3ybagmf
больше нет версий по вопросу ветки?
(17) однозначного ответа нет — все зависит от конкретного запроса и конкретной базы
пента — спасибо.
(16) Гммм, там ангицкий, а я — ленив.
Но спрошу — SQL создает ВТ — одним махом, или все же циклично читает источник и добавляет строки ?
(20) Ну и поиск в ВТ — одним махом, или циклично по ее строкам/рекурсивно по узлам дерева поиска(для индекса)?
(20) циклы могут быть для вложенных запросов
Сейчас пытаюсь найти информацию по данному вопросу на ИТС — пока безрезультатно. Хочется разобраться в этой теме максимально детально. Нет ли у вас источников, которые можно изучить кроме ИТС?
(22) Несомненно. Просто ты так говоришь, что вот у меня возникло ощущение, что при исп. ВТ вообще циклов нет.
Спасибо =) наверное то, что надо
(0) http://kb.1c.ru/articleView.jsp?id=44 если есть доступ почитай.
Лучше вообще не пользоваться вложенными запросами — такие запросы плохо обрабатываются на стороне субд (трудно найти оптимальный индекс и скорее всего будет сделан полный перебор).
Применительно к 1с8 говорить о запросах без привязки к конкретной субд
бессмысленно.
опять же тут вопрос удобства разработки — поддержки
что тоже важно.
левая рука или правая нога?
что выбрать?
посоветуйте за и против..
ненужное планирую отрезать.
берем типический пакетный запрос.. в нем первым запросом создается виртуальная таблица а второй запро, ВНИМАНИЕ!, позапросом обращается к этой виртуальной таблице.
типа что я должен выбрать?
(31)
виртуальная или временная ?
а за (30) — 5
виртуальная все же 🙂
(33)
тогда (31) — тоже шутка.
IamAlexy — очень рад, что поднял Ваше настроение.
В теме нет такой мысли что надо отказаться либо от вложенных запросов либо от временных таблиц вообще.
Вопрос был в том — в каких случаях надо использовать одно а в каких другое?
если вам предложат поесть — то наверное Вы выберите левую руку (надеюсь), или если вам предложат померять ботинок — то скорее всего правую ногу.
так и сдесь — в таких то случаях применяют временную таблицу, а в таких то — вложенный запрос.
Догадки и предположения у меня у самого есть. нет конкретного алгоритма. так как нет понимания их различаев.
Если у кого есть это понимания, прошу разъяснить =)
Спасибо.
для отладки удобней применять врем таблицы
для наглядности (понятности) текста запроса — тоже
если увал объединение в 20-40 таблиц
такой мега джоин отлаживать очень ссложно
проще разбить на части и олаживать по частям
В этом случае нужно обратить внимание
применительно к 1с8
какие ограничения на создание индексов ВТ существуют
для оценки производительности выборки запроса
принцип декомпозиции при создании ПО желательно соблюдать.
про остальное я указал (29)
Использование упорядочивания во вложенных запросах
В языке запросов реализована возможность использования упорядочивания во вложенных запросах. В данной статье рассказывается о данной возможности, показываются примеры использования.
Для того чтобы во вложенном запросе было возможно упорядочивание, необходимо, чтобы во вложенном запросе осуществлялось ограничение по количеству получаемых записей. Т.е, вложенный запрос может содержать упорядочивание только в том случае, если он также содержит конструкцию ПЕРВЫЕ .
В качестве примера рассмотрим запрос, который получает объем продаж по пяти самым дорогим товарам. Запрос будет выглядеть следующим образом:
ВЫБРАТЬ
ВложенныйЗапрос.Ссылка,
ПродажиОбороты.КоличествоОборот,
ПродажиОбороты.СуммаОборот
ИЗ
(ВЫБРАТЬ ПЕРВЫЕ 5
Номенклатура.Ссылка КАК Ссылка
ИЗ
Справочник.Номенклатура КАК Номенклатура
УПОРЯДОЧИТЬ ПО
Номенклатура.ЗакупочнаяЦена УБЫВ) КАК ВложенныйЗапрос
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты(.
Номенклатура В
(ВЫБРАТЬ ПЕРВЫЕ 5
Номенклатура.Ссылка КАК Ссылка
ИЗ
Справочник.Номенклатура КАК Номенклатура
УПОРЯДОЧИТЬ ПО
Номенклатура.ЗакупочнаяЦена УБЫВ)) КАК ПродажиОбороты
ПО ВложенныйЗапрос.Ссылка = ПродажиОбороты.Номенклатура
В данном запросе вложенный запрос получает пять товаров с самым большим значением поля «ЗакупочнаяЦена», во внешнем запросе полученные товары связываются с регистром продаж, из которого получается информация о продажах. Для ограничения расчета оборотов, в условие таблицы «РегистрНакопления.Продажи.Обороты» также передается список самых дорогих товаров.