вложенный запрос или временная таблица что лучше 1с
Ограничения на соединения с вложенными запросами и виртуальными таблицами
Область применения: управляемое приложение, мобильное приложение, обычное приложение.
1.1. При написании запросов не следует использовать соединения с вложенными запросами. Следует соединять друг с другом только объекты метаданных или временные таблицы. Если запрос использует соединения с вложенными запросами, то его следует переписать с использованием временных таблиц (не важно с какой стороны соединения находится вложенный запрос), кроме случая, когда вложенный запрос сканирует мало записей.
Если запрос содержит соединения с вложенными запросами, то это может привести к следующим негативным последствиям:
Пример потенциально опасного запроса, использующего соединение с вложенным запросом:
Оптимизатор сервера СУБД (независимо от того, какую СУБД вы используете) не всегда может правильно оптимизировать подобный запрос. В данном случае, проблемой для оптимизатора является выбор правильного способа соединения. Существуют несколько алгоритмов соединения двух выборок. Выбор того или иного алгоритма зависит от того, сколько записей будет содержаться в одной и в другой выборке. В том случае, если вы соединяете две физические таблицы, СУБД может легко определить объем обоих выборок на основании имеющейся статистики. Если же одна из соединяемых выборок представляет собой вложенный запрос, то понять, какое количество записей она вернет, становится очень сложно. В этом случае СУБД может ошибиться с выбором плана, что приведет к катастрофическому падению производительности запроса.
1.2. Для вышеприведенного примера получится следующий пакетный запрос:
Переписывание запроса по приведенной выше методике имеет своей целью упростить работу оптимизатору СУБД. В переписанном запросе все выборки, участвующие в соединениях будут представлять собой физические таблицы, и СУБД сможет легко определить размер каждой выборки. Это позволит СУБД гарантированно выбрать самый быстрый из всех возможных планов. Причем, СУБД будет делать правильный выбор независимо ни от каких условий. Переписанный подобным образом запрос будет работать одинаково хорошо на любых СУБД, что особенно важно при разработке тиражных решений. Кроме того, переписанный подобным образом запрос лучше читается, проще для понимания и отладки.
2. Если в запросе используется соединение с виртуальной таблицей языка запросов 1С:Предприятия (например, РегистрНакопления.Товары.Остатки ) и запрос работает с неудовлетворительной производительностью, то рекомендуется вынести обращение к виртуальной таблице в отдельный запрос с сохранением результатов во временной таблице (см. пункт 1.1).
3. Следует избегать неявных подзапросов, которые получаются при использовании вложенных соединений:
Проблема в том, что, по сути, этот запрос аналогичен следующему:
Вместо вложенных соединений, как показано выше, следует использовать последовательные соединения:
При этом следует понимать, что вложенные и последовательные соединения – это разные запросы, которые могут дать разный результат.
Если вложенное соединение использовано из предположения, что оно аналогично последовательному соединению, то следует просто переписать его на последовательное соединение.
Если вложенное соединение делается осмысленно, то от него следует отказаться, т.к. оно может существенно снизить производительность, как и соединение с подзапросом. Как и в случае с подзапросом, такое соединение можно заменить на соединение с временной таблицей, но лучше вначале подумать, как заменить его на последовательное соединение, т.к. оно будет работать эффективнее временной таблицы.
что лучше, подзапрос или временная таблица?
| 3. Да | 50% (2) |  |
| 5. Другое | 50% (2) |  |
| 1. Запрос | 0% (0) |  |
| 2. Временная таблица | 0% (0) |  |
| 4. Нет | 0% (0) |  |
Всего мнений: 4
Внимание! Не забудьте проиндексировать созданную временную таблицу. В качестве индексных полей следует указать все поля, которые используются в условии соединения.
(33) Бутылочным. Горлышком. Ага.
Я расскажу об этом серверу 1С.
за времянки:
1. ВТ нужна когда к ее результатам есть множественный вызов или применяется дополнительное индексирование.
за вложеные
1. Меньше использется дисковая система (запись идет но на более короткое время), для ВТ реально видел запрос который темдб раздувал до 300 гигов и сервер падал по сколько он был на C:
я сторонник вложеных запросов, и в редких исключениях использую ВТ
(46) Причиной распухания темпдб являются не столько временные таблицы, сколько как раз вложенные запросы. А что касается временных таблиц, то я их всегда уничтожаю, не полагаясь на автоматику.
Какие запросы лучше вложенные или в виде временных таблиц и почему?
| 1. В виде временных таблиц | 100% (9) | |
| 2. Вложенные | 0% (0) | |
Всего мнений: 9
Однако мне кажется что есть резерв у временных таблиц.
Подозреваю что на них не распостраняется commit в полной мере.
Это надо проверить. Но сложилось впечатление, что MS SQL 2005 часто (иногда?) оставляет их в памяти и не пишет на диск даже при commit.
Временные ведь.
для разных субд будет по-разному.
так что тема бессмысленна.
(30) Как же запись на диск? Вот сожрет SQL всю память индексами и что делать 🙂 Только RAM. Ну а че, я бы гиг выделил.
Интересно, есть ли настройка SQL принудительно сбрасывать tempDB на диск. Чтобы он точно RAm диск юзал а не висел в оперативке.
(47) Ты последнее время меня пугаешь.
Путаешь пакетные запросы с обычными, простые фразы осилить не можешь.
Как Новокузнечанин, что не так?
1. В виде временных таблиц.
Кста, Е.Гилёв на своих курсах тоже говорит за временные таблицы в подавляющем большинстве случаев.
Кроме плана запроса и упрощения отладки для меня большой плюс удобочитаемость. Давая понятные имена временным таблицам, получается нечто похожее на функции.
Также, чтобы редактировать запрос конструктором, стараюсь не делать конструирование запроса из строк вида:
(68) план всегда надо смотреть
вот например в этом запросе: v8: Поясните почему не работает запрос?
если бы я использовал ВТ, то ошибки бы не возникало
но более правильно при повторном использовании данных, особенно когда изначально можно сделать выборку из большой таблицы, лучше использовать ВТ
столкнулся с этим еще когда писал запросы на семерке, где все было построено на периодических реквизитах, и _1SConst составлял половину базы по размеру
Типичные причины неоптимальной работы запросов и методы оптимизации
Краткое содержание:
В статье приводятся типичные причины неоптимальной работы запросов, диагностируемые на уровне кода конфигурации, и рассматриваются методики оптимизации запросов.
Значительная часть проблем, приводящих к неоптимальной работе запросов, может быть обнаружена путем анализа кода конфигурации и структуры метаданных. Имеется перечень типичных ошибок в коде и структуре данных, последствия которых достаточно хорошо изучены и легко предсказуемы. Анализ кода с использованием этого перечня позволяет решить большую часть проблем с производительностью запросов, не углубляясь в детальную техническую информацию (текст запроса на языке SQL, план запроса и т.д.).
Основные причины неоптимальной работы запросов, диагностируемые на уровне кода конфигурации и структуры метаданных:
В настоящей статье рассматриваются перечисленные причины неоптимальной работы запросов и даются рекомендации по их оптимизации.
Cоединения с подзапросами
Рекомендации
Если запрос содержит соединения с подзапросами, то это может привести к следующим негативным последствиям:
Пример потенциально опасного запроса, использующего соединение с подзапросом:
Обратите внимание на то, что возможность использования временных таблиц появилась в 1С:Предприятии начиная с версии 8.1. Если вы используете версию 8.0, то для решения проблемы производительности такого запроса следует перейти на 8.1.
Для оптимизации запроса следует разбить его на несколько отдельных запросов (по числу подзапросов, используемых в соединениях). Эти запросы рекомендуется поместить в один пакетный запрос.
Внимание! Не забудьте проиндексировать созданную временную таблицу. В качестве индексных полей следует указать все поля, которые используются в условии соединения.
Для вышеприведенного примера получится следующий пакетный запрос:
Пояснения
Оптимизатор сервера СУБД (независимо от того, какую СУБД вы используете) не всегда может правильно оптимизировать подобный запрос. В данном случае, проблемой для оптимизатора является выбор правильного способа соединения. Существуют несколько алгоритмов соединения двух выборок. Выбор того или иного алгоритма зависит от того, сколько записей будет содержаться в одной и в другой выборке. В том случае, если вы соединяете две физические таблицы, СУБД может легко определить объем обоих выборок на основании имеющейся статистики. Если же одна из соединямых выборок представляет собой подзапрос, то понять, какое количество записей она вернет, становится очень сложно. В этом случае СУБД может ошибиться с выбором плана, что приведет к катастрофическому падению производительности запроса.
Переписывание запроса по приведенной выше методике имеет своей целью упростить работу оптимизатору СУБД. В переписанном запросе все выборки, участвующие в соединениях будут представлять собой физические таблицы, и СУБД сможет легко определить размер каждой выборки. Это позволит СУБД гарантированно выбрать самый быстрый из всех возможных планов. Причем, СУБД будет делать правильный выбор независимо ни от каких условий. Переписанный подобным образом запрос будет работать одинаково хорошо на любых СУБД, что особенно важно при разработке тиражных решений. Кроме того, переписанный подобным образом запрос лучше читается, проще для понимания и отладки.
Cоединения с виртуальными таблицами
Рекомендации
Если в запросе используется соединение с виртуальной таблицей языка запросов 1С:Предприятия (например, «РегистрНакопления.Товары.Остатки()») и запрос работает с неудовлетворительной производительностью, то рекомендуется вынести обращение к виртуальной таблице в отдельный запрос с сохранением результатов во временной таблице.
То есть, следует использовать ту же рекомендацию, что и в случае соединения с подзапросом.
Пояснения
Виртуальные таблицы, используемые в языке запросов 1С:Предприятия, могут разворачиваться в подзапросы при трансляции в язык SQL. Это связано с тем, что виртуальная таблица часто (но не всегда) получает данные из нескольких физических таблиц СУБД. Если вы используете соединение с виртуальной таблицей, то на уровне SQL оно может быть в некоторых случаях реализовано, как соединение с подзапросом. В этом случае оптимизатор СУБД может точно так же выбрать неоптимальный план, как при работе с подзапросом, использованным в языке 1С:Предприятия в явном виде.
Несоответствие индексов и условий запроса
Рекомендации
Убедитесь в том, что для всех условий, использованных в запросе, имеются подходящие индексы.
Условия используются в следующих секциях запроса:
Для каждого условия должен существовать подходящий индекс. Подходящим является индекс, удовлетворяющий следующим требованиям:
При создании объекта метаданных 1С:Предприятие автоматически создает индексы, которые должны подходить для работы большинства запросов.
Основные идексы, создаваемые 1С:Предприятием:
Детальная информация по индексам, автоматически создаваемым 1С:Предприятием содержится в статье «Индексы таблиц базы данных 1С:Предприятия 8.1».
В тех случаях, когда автоматически созданных индексов недостаточно, можно дополнительно проиндексировать реквизиты объекта метаданных. Информация о том, какие индексы при этом будут созданы, содержится в этой же статье. Следует иметь в виду, что создание индекса ускоряет процесс поиска информации, но может несколько замедлить процесс ее изменения (добавления, редактирования и удаления). Поэтому индексы следует создавать осознанно и только в том случае, если точно известен запрос, для которого такой индекс необходим. Не следует создавать индексы «на всякий случай» или заведомо избыточные индексы. Например никогда не следует дополнительно индексировать первое измерение регистра, поскольку для поиска по значению первого измерения подходит основной индекс таблицы итогов, который автоматически создаст платформа.
Пояснения
Если в структуре базы данных отсутствует индекс, удовлетворяющий всем перечисленным условиям, то для получения результата СУБД будет вынуждена сканировать таблицу или один из ее индексов. Это приведет к увеличению времени выполнения запроса, а так же к возможному снижению параллельности системы, поскольку возрастет количество установленных блокировок.
Примеры
В конфигурации описан регистр накопления ТоварыНаСкладах:
Платформа 1С:Предприятие автоматически создаст для таблицы остатков данного регистра индекс по периоду и всем измерениям в том порядке, в котором они перечислены в конфигураторе.
Рассмотрим несколько примеров запросов и проанализируем, смогут ли они оптимально выполняться при такой структуре данных.
Запрос 1
В данном случае нарушено требование 2. В условии отсутствует отбор по первому полю индекса (Склад). Такой запрос не сможет выполниться оптимально. Для его выполнения серверу СУБД придется перебирать (сканировать) все записи таблицы. Время выполнения этой операции напрямую зависит от количества записей в таблице остатков регистра и может быть очень большим (и будет увеличиваться с ростом количества данных).
Запрос 2
В данном случае нарушено требование 3. Между измерениями «Склад» и «Качество» в структуре регистра находится измерение «Номенклатура», которое не задано в условии запроса. Этот запрос так же не сможет выполняться оптимально. При его выполнении СУБД выполнит поиск по первому полю индекса, но затем вынужденно просканирует некоторую его часть. Сканирование приведет к увеличению времени выполнения запроса и к блокировке избыточных записей в таблице, то есть к снижению общей пропускной способности системы.
Запрос 3
В этом случае требования соответствия индекса и запроса не нарушены. Данный запрос будет выполнен СУБД оптимальным способом. Обратите внимание на то, что порядок следования условий в запросе не обязан совпадать с порядком следования полей в индексе. Это не является проблемой и будет нормально обработано СУБД.
Использование логического ИЛИ в условиях
Рекомендации
Использование логического ИЛИ в секции ГДЕ запроса
Не следует использовать ИЛИ в секции ГДЕ запроса. Это может привести к тому, что СУБД не сможет использовать индексы таблиц и будет выполнять сканирование, что увеличит время работы запроса и вероянтность возникновения блокировок. Вместо этого следует разбить один запрос на несколько и объединить результаты.
следует заменить на запрос
Включение пользователей в несколько ролей, каждая из которых имеет RLS
Использование ИЛИ в условиях соединения
Не рекомендуется использовать логическое ИЛИ в условиях соединения, то есть в секции ПО запроса. Это так же может привести к выбору неоптимального плана и медленной работе запроса. Простого универсального способа переписать такой запрос без использования ИЛИ не существует. Следует проанализировать решаемую задачу и попытаться найти другой алгоритм ее решения.
Использование подзапросов в условии соединения
Рекомендации
Не следует использовать подзапросы в условии соединения. Это может привести к значительному замедлению запроса и (в отдельных случаях) к его полной неработоспособности на некоторых СУБД. Пример запроса с использованием подзапроса в условии соединения:
В данном случае подзапрос в условии соединения используется для получения как бы «среза последних» на конец предыдущего периода. Причем, для каждой номенклатуры период может быть разным. Подобный запрос рекомендуется переписать с использованием временных таблиц. Например, это можно сделать следующим образом:
Получение данных через точку от полей составного типа
Рекомендации
Если в запросе используется получение значения через точку от поля составного ссылочного типа, то при выполнении этого запроса будет выполняться соединение со всеми таблицами объектов, входящими в этот составной тип. В результате SQL текст запроса чрезвычайно усложняется, и при его выполнении оптимизатор СУБД может выбрать неоптимальный план. Это может привести к серьезным проблемам производительности и даже к неработоспособности запроса в отдельных случаях.
Общая рекомендация заключается в том, чтобы по возможности ограничить количество соединений в таких запросах. Для этого можно использовать следующие приемы:
Пример
В данном запросе используется обращение к реквизитам регистратора. Регистратор является полем составного типа, которое может принимать значения ссылки на один из 56 видов документов.
SQL-текст этого запроса будет включать 56 левых соединений с таблицами документов. Это может привести к серьезным проблемам производительности при выполнении запроса. Однако, для решения данной конкретной задачи нет необходимости соединяться со всеми 56 видами документов. Условия запроса таковы, что при его выполнении будут выбраны только движения документов «РеализацияТоваровУслуг» и «ЗаказыПокупателя». В этом случае мы можем значительно ускорить работу запроса, ограничив количество соединений при помощи функции ВЫРАЗИТЬ().
Фильтрация виртуальных таблиц без использования параметров
При использовании виртуальных таблиц в запросах, следует передавать в параметры таблиц все условия, относящиеся к данной виртуальной таблице. Не рекомендуется фильтровать виртуальные таблицы при помощи условий в секции ГДЕ и т.п. Такой запрос будет возвращать правильный (с точки зрения функциональности) результат, но СУБД будет намного сложнее выбрать оптимальный план для его выполнения. В некоторых случаях это может привести к ошибкам оптимизатора СУБД и значительному замедлению работы запроса.
Например, следующий запрос использует секцию ГДЕ запроса для выборки из виртуальной таблицы.
Возможно, что в результате выполнения этого запроса сначала будут выбраны все записи виртуальной таблицы, а затем из них будет отобрана часть, соответствующая заданному условию. Было бы оптимальным вариантом ограничивать количество выбираемых записей на самом раннем этапе обработки запроса. Для этого следует передать условия в параметры виртуальной таблицы.
ВременныеТаблицы замедляют работу Запросов?
| 3. Временные таблицы быстрее | 54% (13) | |
| 4. Позовите специалиста | 21% (5) | |
| 5. Попробуй | 21% (5) | |
| 2. Вложенные запросы быстрее | 4% (1) | |
| 1. Одинаково | 0% (0) | |
Всего мнений: 24
(4) только кажется, ВТ сидит в оперативки и заливается на диск только если жутко не хватает памяти.
Потом, вложенный будет выполняться каждый раз а ВТ уже хранит готовый результат, поэтому чаще всего ВТ.
(0) согласна с (12) : Все зависит от самих запросов, структуры данных и кривизны рук разработчика этой структуры данных.
Считается, что в случае вложенных запросов не всегда получается построить оптимальный план выполнения. В случае временной таблицы всегда заранее известно размер выборки, а значит можно построить оптимальный план выполнения..
(26) >В случае временной таблицы всегда заранее известно размер выборки
И вообще если не ошибаюсь что 15 лет назад что сейчас кроме DB/2 никто не может выдать на гора прогнозное время выполнения запроса
На курсах Гилева по оптимизации очень хорошо рассказывали про то, почему временные запросы лучше вложенных таблиц.
Без цитат, т.к это видеокурс. Просто, что называется, believe me 🙂
Есть еще книга от 1С Язык запросов. Вот цитата из нее про вложенные таблицы :
http://screencast.com/t/gMRHdpbM
http://screencast.com/t/intmUKMFvI
Очень часто вложенные запросы используются в типовых до сих пор например у нас в УПП, поэтому с ними приходится возиться.
Смешно читать комментарии которые говорят что временные таблицы использую tempdb и это замедляет.
А что если я скажу Вам, что практически все запросы из 1с используют tempdb, т.к. туда попадают все транзакционные запросы (а в 1с по-моему все запросы делаются в транзакции с определенным уровнем изоляции)
Про плюсы временных таблиц уже написано много и можно посмотреть курсы Гилева и других интересных товарищей.
В общем вложенный запрос может отработать чуть-чуть быстрее, а может отработать значительно медленнее, в зависимости от 100500 причин