为什么从脚本执行存储过程比 SQL 查询快？

Why execute stored procedures is faster than SQL query from a script?

提问by markzzz

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

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

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

那么，为什么调用“存储过程”应该比“传递 SQL 查询”字符串要快？

回答by marc_s

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

SQL Server 基本上通过这些步骤来执行任何查询（存储过程调用或即席 SQL 语句）：

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

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

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

重点是：即席 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 worsethan executing a stored procedure.

如果临时 SQL 查询正确使用参数 - 无论如何，为了防止 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 efficientas using stored procedures.

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

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

更新：在非参数化查询上使用存储过程更好，原因有两个：

• since each non-parametrized query is a new, differentquery 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 notbe executed again)

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

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

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

回答by Purplegoldfish

Because every time you pass a query string to SQL Server the code has to be compiled etc, stored procedures are already compiled and ready to run on the server.

因为每次向 SQL Server 传递查询字符串时，都必须编译代码等，所以存储过程已经编译并准备好在服务器上运行。

Also you are sending less data over the network although this is generally a minimal impact anyway.

此外，您通过网络发送的数据较少，尽管这通常影响很小。

EDIT: As a side note stored procedures have other benefits.

编辑：作为旁注，存储过程还有其他好处。

1) Security - Since the actual query is stored on the server you are not transmitting this over the network which means anyone intercepting your network traffic does not gain any insight into your table structure. Also a well designed SP will prevent injection attacks.

1) 安全性 - 由于实际查询存储在服务器上，因此您不会通过网络传输它，这意味着任何拦截您的网络流量的人都无法深入了解您的表结构。此外，精心设计的 SP 将防止注入攻击。

2) Code seperation, you keep your database code in your database and your application code in your application, there is very little crossover and I find this makes bug fixing a lot nicer.

2) 代码分离，将数据库代码保存在数据库中，将应用程序代码保存在应用程序中，几乎没有交叉，我发现这使得错误修复更好。

3) Maintainability and Code Reuse, you can reuse a procedure many times without having to copy paste the query, also if you wish to update the query you just have to update it in one place.

3）可维护性和代码重用，您可以多次重用一个过程而无需复制粘贴查询，如果您希望更新查询，您只需在一个地方更新它。

4) Decreased network traffic. As mentioned above this may not be an issue for most people but with a large application you can significantly reduce the ammount of data being transferred via your network by switching to using stored procedures.

4) 网络流量减少。如上所述，这对大多数人来说可能不是问题，但是对于大型应用程序，您可以通过切换到使用存储过程来显着减少通过网络传输的数据量。

回答by Charl

Your statement that Stored Procedures are faster than SQL Queries is only partially true. To explain: Most of these answers already explain that with stored procedures a query plan is generated and cached. So if you call the stored procedure again, the SQL engine first searches through its list of query plans and if it finds a match, it uses the optimized plan.

您关于存储过程比 SQL 查询更快的说法只是部分正确。解释：这些答案中的大多数已经解释了使用存储过程生成和缓存查询计划。因此，如果您再次调用存储过程，SQL 引擎首先搜索其查询计划列表，如果找到匹配项，则使用优化计划。

Passing a normal query does not allow for this advantage as the SQL engine does not know what to expect and thus it cannot find a match for your query. It creates a plan from scratch and then renders your results.

传递普通查询不允许这种优势，因为 SQL 引擎不知道会发生什么，因此它无法为您的查询找到匹配项。它从头开始创建计划，然后呈现您的结果。

Good news: You can enable plan caching for your queries by using Parametized queries, a new feature in SQL. This enables you to generate plans for your queries and can be very effective in your situation as most of the queries you pass from code, remains the same, except for variables in the Where clause mostly. There is also a setting where you can force parameterization for all your queries. Search MSDN for this topic, should help you in deciding what's best.

好消息：您可以使用 SQL 中的一项新功能参数化查询为您的查询启用计划缓存。这使您能够为您的查询生成计划，并且在您的情况下非常有效，因为您从代码传递的大多数查询保持不变，除了大部分 Where 子句中的变量。还有一个设置，您可以在其中强制对所有查询进行参数化。在 MSDN 上搜索这个主题，应该可以帮助你决定什么是最好的。

However, this said, Stored Procedures remains a good way to to interact with the DB from your applications as it provides an additional security layer.

然而，这就是说，存储过程仍然是从应用程序与数据库交互的好方法，因为它提供了额外的安全层。

Hope this was helpful!

希望这是有帮助的！

回答by infocyde

Another issues that is over looked, compare the network traffic between the web server and the database server of this-

另一个被忽略的问题，比较这个的web服务器和数据库服务器之间的网络流量-

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

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

To this-

对此——

Select field1, field2, field3, field4, field5, field6....field30 join table1 on table2.field12 = table1.field12 where blah blah blah and table1.field3 = @var1 and table2.field44 = @var2 and (table1.field1 is null or table1.field1 = @var3.......

选择 field1, field2, field3, field4, field5, field6....field30 join table1 on table2.field12 = table1.field12 where blah blah blah and table1.field3 = @var1 and table2.field44 = @var2 and (table1.field1为 null 或 table1.field1 = @var3.......

See the difference? For 99% of us this probably won't matter, but for some of you writing high performance apps this might, though there are probably some caching or other ways of dealing with this.

看到不同？对于我们 99% 的人来说，这可能无关紧要，但对于你们中的一些编写高性能应用程序的人来说，这可能是，尽管可能有一些缓存或其他方法来处理这个问题。

I think a lot of people who claim there is no difference between adhoc queries and stored procedures are generally using tables as object stores for whatever ORM they are using, and that is fine. There are still a lot of heavy data driven enterprise apps, rightly or wrongly, that have 1000+ line stored procedures. You may end up working on them. Also, for those of you who may have to make changes in production every once in a while and need to bypass the formal process it is a loteasier to do that in the database than in production compiled code. Alter proc...done. Cowboy, horrible, evil, happens. Yes I know doing this is an unforgivable sin in many of your minds, sign of shear slop...but it happens. Just something to think about too.

我认为很多声称即席查询和存储过程之间没有区别的人通常使用表作为他们使用的任何 ORM 的对象存储，这很好。仍然有很多重数据驱动的企业应用程序，无论是对还是错，都有 1000 多行存储过程。你可能最终会为它们工作。此外，对于那些你们谁可能不得不做出改变生产每过一段时间，需要绕过正式的过程中它是很多容易做，在数据库中比在生产编译代码。更改程序...完成。牛仔，可怕的，邪恶的，发生了。是的，我知道这样做在你们的许多人看来是不可原谅的罪过，是剪切倾斜的迹象……但它确实发生了。也只是想想而已。

I know the latest tools generally make using stored procs a pain in the rear if you expect all your entities to be generated nicely for you, but stored procs still have their place sometimes.

我知道如果您希望为您很好地生成所有实体，那么最新的工具通常会使使用存储过程成为一种痛苦，但有时存储过程仍然有其一席之地。

回答by WWC

This article explains it pretty well: https://codingsight.com/dynamic-sql-vs-stored-procedure/

这篇文章解释得很好：https: //codingsight.com/dynamic-sql-vs-stored-procedure/

From my experience, Stored Procedures are definitely faster, because of decreased network traffic (don't have to send the whole query) and caching of the procedure and query plans.

根据我的经验，存储过程肯定更快，因为网络流量减少（不必发送整个查询）以及过程和查询计划的缓存。

I ran code similar to the following on a table filled with user logon data.

我在填充了用户登录数据的表上运行了类似于以下的代码。

"select top 1 * from Logons where ComputerName=@ComputerName order by LogonTime desc"

“从登录中选择前 1 个 * 其中 ComputerName=@ComputerName 按 LogonTime desc 排序”

It took 2 hours to run the query on 7000 computer names.

对 7000 个计算机名称运行查询需要 2 个小时。

When I placed the query into a stored procedure, it took about a minute to run on 7000 computer names.

当我将查询放入存储过程时，在 7000 个计算机名称上运行大约需要一分钟。

I'm certain that taking 1 second vs a 10 milliseconds per query doesn't make a big difference to humans if you are running the query just once. However, if you need to run the query one thousand times, it's a difference of 1000 seconds (approx. 16 min) vs 10 seconds.

我敢肯定，如果您只运行一次查询，则每次查询花费 1 秒与 10 毫秒对人类没有太大影响。但是，如果您需要运行查询一千次，则相差 1000 秒（约 16 分钟）与 10 秒。

回答by NimChimpsky

stored procedures are compiled and cached. But SQL statements will be compared to existing execution plans and if a match is present used, thus nullifying somewhat any advantage.

存储过程被编译和缓存。但是 SQL 语句将与现有的执行计划进行比较，如果存在匹配，则使用，因此在某种程度上抵消了任何优势。

Whats the actual performance difference after a number of executions ?

多次执行后的实际性能差异是什么？

回答by Gaurav Agrawal

Unlike standard SQL statements, stored procedures are compiled and optimized by the database server. This optimization involves using information about the structure of a particular database that's required at execution time by the stored procedure. This process of storing execution information (the execution plan) is a tremendous time saver, especially if the stored procedure is called many times.

与标准 SQL 语句不同，存储过程由数据库服务器编译和优化。此优化涉及使用有关存储过程在执行时所需的特定数据库结构的信息。这种存储执行信息（执行计划）的过程可以节省大量时间，尤其是在多次调用存储过程的情况下。

Speed is also improved by the fact that stored procedures run entirely on the database server - there's no need to pass large chunks of SQL code over a network. For a simple SELECT statement, that might not make a big difference, but in cases where we perform a series of loops and calculations, it can have a significant effect.

存储过程完全在数据库服务器上运行的事实也提高了速度 - 无需通过网络传递大量 SQL 代码。对于简单的 SELECT 语句，这可能不会产生很大的不同，但在我们执行一系列循环和计算的情况下，它可能会产生显着的影响。

回答by Oleg Dok

1. Stored procedures sometimes run a little bit faster because or using RPC calls when possible
2. SP runs faster for queries that have to be recompiled - for ex. - using temp tables creations somewhere in the middle

1. 存储过程有时会运行得更快一点，因为或在可能的情况下使用 RPC 调用
2. 对于必须重新编译的查询，SP 运行得更快 - 例如。- 在中间某处使用临时表创建