C# 为什么存储过程比查询快
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12948312/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why Stored Procedure is faster than Query
提问by Ammar Raja
I want to write a simple single line query to select only one value from database.
我想编写一个简单的单行查询来仅从数据库中选择一个值。
So if I write stored procedures for this query rather than writing simple select query in c# code, then I am sure that stored procedure for this simple select query will be faster but why?
因此，如果我为此查询编写存储过程而不是在 c# 代码中编写简单的选择查询，那么我确信此简单选择查询的存储过程会更快，但为什么呢？
I am confused with stored procedure vs writing simple query in my code? I am confused that why stored procedure are faster than simple one query written directly in code?
我对存储过程与在我的代码中编写简单查询感到困惑？我很困惑，为什么存储过程比直接用代码编写的简单查询更快？
采纳答案by cuongle
Stored Procedures Are Faster Than SQL Code
存储过程比 SQL 代码快
This is a myth, the performance is always equivalent, from the book: Architecting Microsoft? .NET Solutions for the Enterprise:
这是一个神话，性能总是等价的，出自： Architecting Microsoft? .NET 企业解决方案：
SQL is a language through which you declare your intentions about the operations (query, update, or management operations) to execute on the database. All that the database engine gets is text. Much like a C# source file processed by a compiler, the SQL source code must be compiled in some way to produce a sequence of lower-level database operations—this output goes under the name of execution plan. Conceptually, the generation of the execution plan can be seen as the database counterpart of compiling a program.
The alleged gain in performance that stored procedures guarantee over plain SQL code lies in the reuse of the execution plan. In other words, the first time you execute an SP, the DBMS generates the execution plan and then executes the code. The next time it will just reuse the previously generated plan, thus executing the command faster. All SQL commands need an execution plan.
The (false) myth is that a DBMS reuses the execution plan only for stored procedures. As far as SQL Server and Oracle DBMS are concerned, the benefit of reusing execution plans applies to any SQL statements. Quoting from the SQL Server 2005 online documentation:
When any SQL statement is executed in SQL Server 2005, the relational engine first looks through the procedure cache to verify that an existing execution plan for the same SQL statement exists. SQL Server 2005 reuses any existing plan it finds, saving the overhead of recompiling the SQL statement. If no existing execution plan exists, SQL Server 2005 generates a new execution plan for the query.
The debate around SPs performing better than plain SQL code is pointless. Performance wise, any SQL code that hits the database is treated the same way. Performance is equivalent once compiled. Period.
SQL 是一种语言，您可以通过它声明您对要在数据库上执行的操作（查询、更新或管理操作）的意图。数据库引擎获得的只是文本。与编译器处理的 C# 源文件非常相似，SQL 源代码必须以某种方式编译以生成一系列较低级别的数据库操作 - 此输出以执行计划的名义进行。从概念上讲，执行计划的生成可以看作是编译程序的数据库副本。
存储过程比普通 SQL 代码保证的性能提升在于执行计划的重用。换句话说，第一次执行 SP 时，DBMS 会生成执行计划，然后执行代码。下次它只会重用之前生成的计划，从而更快地执行命令。所有 SQL 命令都需要一个执行计划。
（错误的）神话是 DBMS 仅对存储过程重用执行计划。就 SQL Server 和 Oracle DBMS 而言，重用执行计划的好处适用于任何 SQL 语句。引自 SQL Server 2005 在线文档：
当在 SQL Server 2005 中执行任何 SQL 语句时，关系引擎首先查看过程缓存以验证相同 SQL 语句的现有执行计划是否存在。SQL Server 2005 重用它找到的任何现有计划，从而节省重新编译 SQL 语句的开销。如果不存在现有的执行计划，SQL Server 2005 会为查询生成一个新的执行计划。
围绕 SP 性能优于普通 SQL 代码的争论毫无意义。在性能方面，任何命中数据库的 SQL 代码都以相同的方式处理。编译后性能相当。时期。
回答by Yaniv
Stored procedures are precompiled and optimised, which means that the query engine can execute them more rapidly. By contrast, queries in code must be parsed, compiled, and optimised at runtime. This all costs time.
存储过程经过预编译和优化，这意味着查询引擎可以更快地执行它们。相比之下，代码中的查询必须在运行时进行解析、编译和优化。这一切都需要时间。
回答by wizgot
This depends on the query, for simple queries it is best written and executed as a query itself. However when you have more processing to do on the database side (you want to take the data in a cursor manipulate it and so on) , stored procedures are better as they execute on the database server and avoid unnecessary overheads such as parsing and extra communication.
这取决于查询，对于简单查询，最好将其作为查询本身编写和执行。然而，当你在数据库端有更多的处理要做（你想在游标中获取数据来操作它等等），存储过程会更好，因为它们在数据库服务器上执行并避免不必要的开销，例如解析和额外的通信.
回答by muhammad kashif
Stored Procedures are stored queries in Database. They are precompiled. When you request database to execute a stored procedure (SQL Server) , SQL server already has the execution plan for the stored procedure. While simple queries need to create their execution plan on run time. you need to study more here
存储过程是存储在数据库中的查询。它们是预编译的。当您请求数据库执行存储过程（SQL Server）时，SQL Server 已经有了该存储过程的执行计划。而简单的查询需要在运行时创建它们的执行计划。你需要在这里学习更多
回答by Tahir77667
"Stored procedures are precompiled and cached so the performance is much better."
This was heart breaking for me as it would be for you when you come to know that this was true until SQL Server 2005.This article shatters the myth Stored Procedures DO NOT increase performance
这对我来说是令人心碎的，因为当您知道在 SQL Server 2005 之前这是真的，这对您来说是令人心碎的。这篇文章打破了存储过程不会提高性能 的神话
Christa Carpentiere from Microsoft Corp.wrote An Evaluation of Stored Procedures for the .NET Developer
来自Microsoft Corp. 的Christa Carpentiere为 .NET 开发人员编写了对存储过程的评估
