Зачем выполнять хранимые процедуры быстрее, чем SQL-запрос из script?

Ответ 1

SQL Server в основном выполняет эти шаги для выполнения запроса любой (вызов хранимой процедуры или оперативного SQL-запроса):

1) синтаксически проверить запрос
2) если все в порядке - он проверяет кеш плана, чтобы увидеть, имеет ли он уже план выполнения для этого запроса
3) если есть план выполнения - этот план используется (повторно) и выполняется запрос 4) если план еще не установлен, определяется план выполнения
5), что план хранится в кеше плана для последующего повторного использования
6) выполняется запрос

Точка: ad-hoc SQL и хранимые процедуры обрабатываются не иначе.

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

У сохранной процедуры есть другие преимущества (нет необходимости предоставлять пользователям прямой доступ к таблице, например), но с точки зрения производительности использование правильно параметризованных специальных SQL-запросов так же эффективно, как использование сохраненных процедуры.

Обновление с использованием хранимых процедур с помощью непараметризированных запросов лучше по двум основным причинам:

• поскольку каждый непараметризированный запрос представляет собой новый, другой запрос к SQL Server, он должен пройти все этапы определения плана выполнения для каждого запроса (таким образом, теряя время - а также тратить пространство кэша плана, поскольку сохранение плана выполнения в кеше плана в конце концов не помогает, так как этот конкретный запрос будет, вероятно, не выполняться снова)

• непараметризированные запросы подвержены риску атаки SQL-инъекций и их следует избегать любой ценой

Ответ 2

Поскольку каждый раз, когда вы передаете строку запроса на SQL Server, код должен быть скомпилирован и т.д., хранимые процедуры уже скомпилированы и готовы к запуску на сервере.

Также вы отправляете меньше данных по сети, хотя это, как правило, минимальное влияние.

EDIT: В качестве побочных примечаний сохраненные процедуры имеют другие преимущества.

1) Безопасность. Поскольку фактический запрос хранится на сервере, вы не передаете его по сети, что означает, что любой, кто перехватывает ваш сетевой трафик, не получает никакого представления о вашей структуре таблицы. Также хорошо спроектированный СП предотвратит инъекционные атаки.

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

3) Поддержание работоспособности и повторное использование кода, вы можете многократно использовать процедуру, не копируя вставить запрос, также если вы хотите обновить запрос, вам просто нужно обновить его в одном месте.

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

Ответ 3

Ваше утверждение о том, что хранимые процедуры выполняется быстрее, чем SQL-запросы, является лишь частично истинным. Объяснить: Большинство из этих ответов уже объясняют, что с помощью хранимых процедур создается и кэшируется план запроса. Поэтому, если вы снова вызовете хранимую процедуру, SQL-движок сначала ищет в своем списке планов запросов, и если он найдет совпадение, он использует оптимизированный план.

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

Хорошие новости. Вы можете включить кэширование планов для своих запросов, используя Parametized queries, новую функцию в SQL. Это позволяет создавать планы для ваших запросов и может быть очень эффективным в вашей ситуации, поскольку большинство запросов, переданных из кода, остаются теми же, за исключением переменных в предложении Where в основном. Существует также настройка, в которой вы можете принудительно параметризовать все ваши запросы. Поиск MSDN для этой темы поможет вам решить, что лучше всего.

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

Надеюсь, что это было полезно!

Ответ 4

собираются и кэшируются. Но операторы SQL будут сравниваться с существующими планами выполнения и если используется совпадение, что сбивает несколько преимуществ.

Какова фактическая разница в производительности после нескольких исполнений?

Ответ 5

Другие проблемы, которые были просмотрены, сравнивают сетевой трафик между веб-сервером и сервером базы данных -

exec someproc @var1 = 'blah', @var2 = 'blah', @var3 = 'blah'

Для этого -

Выберите поле1, поле2, поле3, поле4, поле5, поле6.... поле30 join table1 на table2.field12 = table1.field12 где бла-бла-бла и table1.field3 = @var1 и table2.field44 = @var2 и (table1.field1 имеет значение null или table1.field1 = @var3.......

Посмотрите разницу? Для 99% из нас это, вероятно, не имеет значения, но для некоторых из вас можно писать приложения с высокой производительностью, хотя, возможно, есть некоторые кеширование или другие способы борьбы с этим.

Я думаю, что многие люди, которые утверждают, что нет разницы между adhoc-запросами и хранимыми процедурами, обычно используют таблицы в качестве хранилищ объектов для любых ORM, которые они используют, и это нормально. До сих пор существует множество бизнес-приложений с большим объемом данных, которые правильно или ошибочно работают с 1000-строчными хранимыми процедурами. Вы можете работать над ними. Кроме того, для тех из вас, кто может время от времени вносить изменения в производство и им необходимо обходить формальный процесс, гораздо проще сделать это в базе данных, чем в компилированном коде производства. Измените процесс... сделано. Ковбой, ужасный, злой, случается. Да, я знаю, что это непростительный грех во многих ваших умах, знак сдвига, но это случается. Просто о чем подумать.

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

Ответ 6

В отличие от стандартных операторов SQL, хранимые процедуры компилируются и оптимизируются сервером базы данных. Эта оптимизация включает использование информации о структуре конкретной базы данных, которая требуется во время выполнения хранимой процедурой. Этот процесс хранения информации о выполнении (план выполнения) представляет собой потрясающую экономию времени, особенно если хранимая процедура вызывается много раз.

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

Ответ 7

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

Ответ 8

Эта статья объясняет это довольно хорошо: https://codingsight.com/dynamic-sql-vs-stored-procedure/

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

Я запустил код, подобный следующему, для таблицы, заполненной данными для входа в систему.

msgstr "выберите top 1 * из Logons, где ComputerName = @порядок ComputerName по LogonTime desc"

Потребовалось 2 часа, чтобы выполнить запрос на 7000 имен компьютеров.

Когда я поместил запрос в хранимую процедуру, потребовалось около минуты для запуска на 7000 имен компьютеров.

Я уверен, что выполнение 1 секунды против 10 миллисекунд на запрос не имеет большого значения для людей, если вы выполняете запрос только один раз. Однако, если вам нужно выполнить запрос тысячу раз, это будет разница в 1000 секунд (около 16 минут) против 10 секунд.

Ответ 9

Динамический SQL против хранимой процедуры

• Performance:

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

• Network Traffic:

Хранимые процедуры производят меньше сетевого трафика, чем динамический SQL, потому что для выполнения хранимой процедуры требуется отправлять по сети только имя и параметры процедуры (если таковые имеются).

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

• SQL Injection Attacks:

Хранимые процедуры не уязвимы для атак внедрения SQL. Динамические запросы SQL уязвимы для атак внедрения SQL, если параметризованные запросы не используются, а параметризованные запросы нельзя использовать с динамическим SQL, если в качестве параметра передается имя таблицы или столбца.

• Re-usability of Cached Query Plans:

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

• Security:

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

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