为什么存储过程要比快查询 [英] Why Stored Procedure is faster than Query
问题描述
如果我想要写简单的单网上查询，查询到从数据库中只有一个值，所以如果我写存储过程，对于这个简单的查询，而不是写在C＃code选择查询比我确信存储过程这个简单的选择查询将是快，但为什么呢？
我与存储过程中混淆VS在我的code编写简单的查询？
我很困惑，之所以存储过程比简单的直接在code编写一个查询更快？
存储过程是不是SQL code
更快这是一个的神话的，总是表现的相当，从书：微软架构对于企业.NET解决方案：
SQL是通过它你宣布你的意图有关的操作（查询，更新，或管理操作）在数据库上执行的语言。所有数据库引擎得到的是文本。就像由编译器处理的C＃源文件，SQL源代码code必须以某种方式产生较低级别的数据库操作，这种输出序列被编译进执行计划的名下。在概念上，执行计划的产生可以被看作是编译程序的数据库的对应
在性能据称增益的存储过程保证在普通的SQL code在于执行计划的重用。换句话说，你执行一个SP在第一时间，在DBMS生成执行计划，然后执行code。下一次它只会重用previously产生的方案，进而执行命令更快。所有的SQL命令需要一个执行计划。
的（假）的神话是一个DBMS重用的执行计划只针对存储过程。至于SQL Server和Oracle数据库管理系统而言，重用执行计划的好处适用于任何SQL语句。从SQL Server 2005联机文档引用：
的当任何SQL语句在SQL Server 2005中执行，关系引擎首先会通过程序缓存来验证相同的SQL语句的现有执行计划的存在。 SQL Server 2005中重新使用任何现有计划发现，节省重新编译SQL语句的开销。如果现有的执行计划存在，SQL Server 2005生成用于查询一个新的执行计划。的
各地SP的表现比普通的SQL code更好的争论是没有意义的。 Performancewise，任何SQL code击中数据库的处理方式相同。一旦编译性能等同。期。
If I want to write simple single line query, query to select only one value from database so if I write stored procedures for this simple query rather to write select query in c# code than I am sure that stored procedure for this simple select query will be fast 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?
解决方案Stored Procedures Are Faster Than SQL Code
This is a myth, the performance is always equivalent, from the book: Architecting Microsoft® .NET Solutions for the Enterprise:
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. Performancewise, any SQL code that hits the database is treated the same way. Performance is equivalent once compiled. Period.
这篇关于为什么存储过程要比快查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
