为什么存储过程比查询快 [英] Why Stored Procedure is faster than Query
问题描述
我想编写一个简单的单行查询来仅从数据库中选择一个值.
I want to write a simple single line query to select only one value from database.
因此，如果我为此查询编写存储过程而不是在 C# 代码中编写简单的选择查询，那么我确信此简单选择查询的存储过程会更快，但为什么呢?
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?
我对存储过程与在我的代码中编写简单查询感到困惑?我很困惑，为什么存储过程比直接用代码编写的简单查询更快?
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?
推荐答案
存储过程比 SQL 代码快
Stored Procedures Are Faster Than SQL Code
这是一个神话，性能总是等价的，来自书中:为企业构建 Microsoft® .NET 解决方案:
This is a myth, the performance is always equivalent, from the book: Architecting Microsoft® .NET Solutions for the Enterprise:
SQL 是一种语言，您可以通过它声明您对要在数据库上执行的操作(查询、更新或管理操作)的意图.数据库引擎获得的只是文本.与编译器处理的 C# 源文件非常相似，SQL 源代码必须以某种方式编译以生成一系列较低级别的数据库操作——此输出以执行计划的名义进行.从概念上讲，执行计划的生成可以看作是编译程序的数据库副本.
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.
存储过程比普通 SQL 代码保证的性能提升在于执行计划的重用.换句话说，第一次执行 SP 时，DBMS 会生成执行计划，然后执行代码.下一次它只会重用之前生成的计划，从而更快地执行命令.所有 SQL 命令都需要一个执行计划.
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.
(错误的)神话是 DBMS 仅对存储过程重用执行计划.就 SQL Server 和 Oracle DBMS 而言，重用执行计划的好处适用于任何 SQL 语句.引自 SQL Server 2005 在线文档:
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:
在 SQL Server 2005 中执行任何 SQL 语句时，关系引擎首先查看过程缓存以验证相同 SQL 语句的现有执行计划是否存在.SQL Server 2005 重用它找到的任何现有计划，从而节省重新编译 SQL 语句的开销.如果不存在现有的执行计划，SQL Server 2005 会为查询生成一个新的执行计划.
围绕 SP 性能优于普通 SQL 代码的争论毫无意义.在性能方面，任何命中数据库的 SQL 代码都以相同的方式处理.编译后性能相当.期间.
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.
这篇关于为什么存储过程比查询快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
