database 存储过程的缺点
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/226859/
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
Disadvantage of stored procedures
提问by CSharpAtl
Would like to get a list of advantages and disadvantages of using Stored Procedures. The main advantage of SPs seems to be precompiled and an abstraction of data from the application. Give me your thoughts....
想获得使用存储过程的优点和缺点的列表。SP 的主要优点似乎是预编译和应用程序数据的抽象。给我你的想法......
采纳答案by Ryan Lundy
Correction: Whether they're precompiled depends on the database. In SQL Server, for instance, they're not. Stored procedures and parameterized SQL are both compiled before being run. A stored procedure can sometimes reuse an execution plan if a corresponding one exists...but so can parameterized SQL.
更正：它们是否预编译取决于数据库。例如，在 SQL Server 中，它们不是。存储过程和参数化 SQL 都在运行前编译。如果存在相应的执行计划，存储过程有时可以重用执行计划……但参数化 SQL 也可以。
Edit:Here's what MSDN says about it:
编辑：这是 MSDN 所说的：
SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.
SQL Server 2000 和 SQL Server 7.0 版对语句处理进行了大量更改，这些更改将存储过程的许多性能优势扩展到所有 SQL 语句。SQL Server 2000 和 SQL Server 7.0 在创建存储过程时不保存部分编译的计划。存储过程在执行时编译，就像任何其他 Transact-SQL 语句一样。SQL Server 2000 和 SQL Server 7.0 在过程缓存中保留所有 SQL 语句的执行计划，而不仅仅是存储过程执行计划。
回答by ckarras
Advantages: Provides a "public interface" to a database (another abstraction layer).
优点：为数据库（另一个抽象层）提供“公共接口”。
Also groups all queries at the same location, making it easier for DBAs to see how the database is queried and optimize it accordingly.
还将所有查询分组在同一位置，使 DBA 更容易查看数据库是如何查询的并相应地对其进行优化。
Disadvantages: May not be the best place to put complex logic. However, following the idea that complex logic belongs in application code and not in stored procedures, stored procedure become simply CRUD operations (each table has a "Create", "Read", "Update" and "Delete" procedure). In that case, stored procedures don't add any value to the application, they only complexify maintenance and become waste.
缺点：可能不是放置复杂逻辑的最佳位置。然而，按照复杂逻辑属于应用程序代码而不是存储过程的想法，存储过程变成了简单的 CRUD 操作（每个表都有一个“创建”、“读取”、“更新”和“删除”过程）。在这种情况下，存储过程不会为应用程序增加任何价值，它们只会使维护复杂化并成为浪费。
Queries are all grouped together, so it's harder to see the context of the application where they are being used. Analyzing the impact of a change is longer, and doing the change is longer as well.
查询全部组合在一起，因此更难查看使用它们的应用程序的上下文。分析变更影响的时间更长，执行变更的时间也更长。
Therefore: use stored procedures to encapsulate complex queries (complex joins, complex where clauses, ...). But don't use stored procedure for complex application/domain/business logic, and don't use stored procedures for CRUD either. So stored procedures should be used in a minority of cases rather than be the standard tool for all queries in an application.
因此：使用存储过程来封装复杂的查询（复杂的连接，复杂的 where 子句，...）。但是不要对复杂的应用程序/域/业务逻辑使用存储过程，也不要对 CRUD 使用存储过程。所以存储过程应该在少数情况下使用，而不是作为应用程序中所有查询的标准工具。
Group code (including queries) to achieve "functional cohesion" instead of grouping by tool/technology. To allow a DBA to optimize a database based on how it is being queried, use a profiler.
分组代码（包括查询）以实现“功能内聚”，而不是按工具/技术分组。要允许 DBA 根据查询方式优化数据库，请使用分析器。
回答by DCNYAM
By using SPs, you also avoid having to give users direct access to tables. All access can be controlled via the SPs.
通过使用 SP，您还可以避免让用户直接访问表。所有访问都可以通过 SP 进行控制。
回答by Patrick Desjardins
Disadvantages
缺点
Refactoring is harder. Renaming or changing where the stored procedure is might produce a bad effect.
Unit testing stored procedure require code assistance outside the DB
重构更难。重命名或更改存储过程的位置可能会产生不良影响。
单元测试存储过程需要数据库外的代码帮助
Advantage
优势
- You do not need to deploy to make a change.
- Faster sometime
- Easier to expand a system
- 您无需部署即可进行更改。
- 有时更快
- 更容易扩展系统
回答by Vin
With the current .Net 3.5 framework libraries, I would use Linq to perform most database operations. There might be places where SP makes more sense. But Linq has provisions to run an SP too.
使用当前的 .Net 3.5 框架库，我会使用 Linq 来执行大多数数据库操作。可能有些地方 SP 更有意义。但 Linq 也有运行 SP 的规定。
On the topic of disadvantages of SP, check out the following link - an interesting analysis. Check the blog post's comments too.
关于 SP 的缺点，请查看以下链接 - 一个有趣的分析。也检查博客文章的评论。
http://www.spoiledtechie.com/post/Whats-up-with-Stored-Procedures-these-days.aspx
http://www.spoiledtechie.com/post/Whats-up-with-Stored-Procedures-these-days.aspx
回答by Steve
Advantage: Stored procedures can be used to maintain data integrity and enforce database policy without relying on an external program to do so.
优点：存储过程可用于维护数据完整性和执行数据库策略，而无需依赖外部程序来执行此操作。
Disadvantage: Can make debugging more complex. Can also be sensitive to being dropped during copy operations, if not done correctly.
缺点：会使调试更加复杂。如果没有正确完成，也可能对在复制操作期间被丢弃很敏感。
回答by yogman
Another disadvantage is version control, because some of the business logic is now in the database side. Can you easily roll back to v1 (one year ago) from v2 (now)?
另一个缺点是版本控制，因为一些业务逻辑现在在数据库端。您可以轻松地从 v2（现在）回滚到 v1（一年前）吗？
A feasible solution is versioning the stored procedure names. But now the database is a mess with old and new stored procedures.
一个可行的解决方案是对存储过程名称进行版本控制。但是现在数据库是旧的和新的存储过程一团糟。
回答by Martynnw
Disadvantages
缺点
- Source control can be a pain.
- Debugging is hard.
- If you have a lot of functionality in procs it will making swapping between different database systems harder - It also creates more work if you want to support different database systems.
- Developing stored procedures can be a fairly specialised task, especially as they get more complex.
- 源代码控制可能是一种痛苦。
- 调试很困难。
- 如果你在 procs 中有很多功能，它会使不同数据库系统之间的交换变得更加困难 - 如果你想支持不同的数据库系统，它也会创建更多的工作。
- 开发存储过程可能是一项相当专业的任务，尤其是当它们变得更加复杂时。
回答by Jeff Jones
Just a few reasons I use stored procedures exclusively when building applications:
我在构建应用程序时专门使用存储过程的几个原因：
- Stored procedures can be the interface between your application and the underlying database. This way, the server on which the database resides, which is usually more powerful than a desktop machine, can be used to perform more complex procedures.
- If you need to change the structure of the database, you can do so without breaking your application if stored procedures are used as the interface.
- As you write, stored procedures contain precompiled and pre-tested SQL.
- It is easier to perform complex operations with stored procedures than with SQL generated by the client or GUI.
- 存储过程可以是您的应用程序和底层数据库之间的接口。这样，数据库所在的服务器（通常比台式机更强大）可用于执行更复杂的过程。
- 如果您需要更改数据库的结构，并且使用存储过程作为接口，则可以在不破坏应用程序的情况下进行更改。
- 在您编写时，存储过程包含预编译和预测试的 SQL。
- 使用存储过程执行复杂操作比使用客户端或 GUI 生成的 SQL 更容易。
