为什么从脚本执行存储过程比 SQL 查询快? [英] Why execute stored procedures is faster than SQL query from a script?

问题描述

事实上，如果我从我的应用程序调用存储过程，我需要连接到我的数据库.

In fact, if I call the stored procedures from my application, I need a connection to my DB.

那么，为什么调用存储过程"应该比传递 SQL 查询"字符串要执行得更快?

So, why calling a "stored procedures" should be faster than "passing a SQL query" string to be executed?

推荐答案

SQL Server 基本上通过这些步骤来执行任何查询(存储过程调用或临时 SQL 语句):

SQL Server basically goes through these steps to execute any query (stored procedure call or ad-hoc SQL statement):

1) 语法检查查询
2)如果没问题 - 它检查计划缓存以查看它是否已经具有该查询的执行计划
3)如果有执行计划 - 该计划被(重新)使用并执行查询
4) 如果还没有计划，则确定一个执行计划
5) 该计划被存储到计划缓存中以备后用
6) 查询被执行

1) syntactically check the query
2) if it's okay - it checks the plan cache to see if it already has an execution plan for that query
3) if there is an execution plan - that plan is (re-)used and the query executed
4) if there is no plan yet, an execution plan is determined
5) that plan is stored into the plan cache for later reuse
6) the query is executed

重点是:ad-hoc SQL 和存储过程没有区别.

The point is: ad-hoc SQL and stored procedures are treatly no differently.

如果临时 SQL 查询正确使用参数 - 无论如何，为了防止 SQL 注入攻击，它的性能特征没有什么不同，而且绝对不会比执行存储过程更差.

If an ad-hoc SQL query is properly using parameters - as it should anyway, to prevent SQL injection attacks - its performance characteristics are no different and most definitely no worse than executing a stored procedure.

存储过程还有其他好处(例如，无需授予用户直接表访问权限)，但在性能方面，使用正确参数化的即席 SQL 查询与使用存储过程一样高效程序.

Stored procedure have other benefits (no need to grant users direct table access, for instance), but in terms of performance, using properly parametrized ad-hoc SQL queries is just as efficient as using stored procedures.

更新:使用存储过程优于非参数化查询，主要有两个原因:

Update: using stored procedures over non-parametrized queries is better for two main reasons:

• 由于每个非参数化查询对于 SQL Server 来说都是一个新的、不同的查询，因此对于每个查询，它必须经历确定执行计划的所有步骤(从而浪费时间- 并且还浪费了计划缓存空间，因为将执行计划存储到计划缓存中最终并没有真​​正的帮助，因为该特定查询可能不会再次执行)

• since each non-parametrized query is a new, different query to SQL Server, it has to go through all the steps of determining the execution plan, for each query (thus wasting time - and also wasting plan cache space, since storing the execution plan into plan cache doesn't really help in the end, since that particular query will probably not be executed again)

非参数化查询存在 SQL 注入攻击的风险，应不惜一切代价避免

non-parametrized queries are at risk of SQL injection attack and should be avoided at all costs

这篇关于为什么从脚本执行存储过程比 SQL 查询快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！