SQL 哪个更好：即席查询或存储过程？

Which is better: Ad hoc queries or stored procedures?

提问by akmad

Assuming you can't use LINQ for whatever reason, is it a better practice to place your queries in stored procedures, or is it just as good a practice to execute ad hocqueries against the database (say, SQL Server for argument's sake)?

假设由于某种原因您不能使用 LINQ，将查询放在存储过程中是更好的做法，还是对数据库执行即席查询（例如，出于参数的缘故，SQL Server）也是一种好的做法？

回答by akmad

In my experience writing mostly WinForms Client/Server apps these are the simple conclusions I've come to:

根据我主要编写 WinForms 客户端/服务器应用程序的经验，这些是我得出的简单结论：

Use Stored Procedures:

使用存储过程：

1. For any complex data work. If you're going to be doing something truly requiring a cursor or temp tables it's usually fastest to do it within SQL Server.
2. When you need to lock down access to the data. If you don't give table access to users (or role or whatever) you can be sure that the only way to interact with the data is through the SP's you create.

1. 对于任何复杂的数据工作。如果您打算做一些真正需要游标或临时表的事情，通常在 SQL Server 中完成它是最快的。
2. 当您需要锁定对数据的访问时。如果您不向用户（或角色或其他）授予表访问权限，您可以确定与数据交互的唯一方法是通过您创建的 SP。

Use ad-hoc queries:

使用即席查询：

1. For CRUD when you don't need to restrict data access (or are doing so in another manner).
2. For simple searches. Creating SP's for a bunch of search criteria is a pain and difficult to maintain. If you can generate a reasonably fast search query use that.

1. 对于 CRUD，当您不需要限制数据访问（或以其他方式这样做）时。
2. 用于简单搜索。为一堆搜索条件创建 SP 是一件痛苦且难以维护的事情。如果您可以生成相当快速的搜索查询，请使用它。

In most of my applications I've used both SP's and ad-hoc sql, though I find I'm using SP's less and less as they end up being code just like C#, only harder to version control, test, and maintain. I would recommend using ad-hoc sql unless you can find a specific reason not to.

在我的大多数应用程序中，我同时使用了 SP 和 ad-hoc sql，但我发现我使用 SP 的次数越来越少，因为它们最终成为像 C# 一样的代码，只是更难进行版本控制、测试和维护。我会推荐使用 ad-hoc sql，除非你能找到一个不使用的具体原因。

回答by Dave Ward

I can't speak to anything other than SQL Server, but the performance argument is notsignificantly valid there unless you're on 6.5 or earlier. SQL Server has been caching ad-hoc execution plans for roughly a decade now.

除了 SQL Server 之外，我无法谈论任何其他内容，但除非您使用的是 6.5 或更早版本，否则性能参数在那里并不明显有效。SQL Server 已经缓存临时执行计划大约十年了。

回答by HLGEM

I think this is a basic conflict between people who must maintain the database and people who develop the user interfaces.

我认为这是必须维护数据库的人和开发用户界面的人之间的基本冲突。

As a data person, I would not consider working with a database that is accessed through adhoc queries because they are difficult to effectively tune or manage. How can I know what affect a change to the schema will have? Additionally, I do not think users should ever be granted direct access to the database tables for security reasons (and I do not just mean SQL injection attacks, but also because it is a basic internal control to not allow direct rights and require all users to use only the procs designed for the app. This is to prevent possible fraud. Any financial system which allows direct insert, update or delete rights to tables is has a huge risk for fraud. This is a bad thing.).

作为一名数据人员，我不会考虑使用通过即席查询访问的数据库，因为它们很难有效地调整或管理。我如何知道架构更改会产生什么影响？此外，我认为出于安全原因，用户不应被授予对数据库表的直接访问权限（我不仅指 SQL 注入攻击，还因为不允许直接访问权限并要求所有用户访问是基本的内部控制）只使用为应用程序设计的过程。这是为了防止可能的欺诈。任何允许直接插入、更新或删除表权限的金融系统都存在巨大的欺诈风险。这是一件坏事。）。

Databases are not object-oriented and code which seems good from an object-oriented perspective is can be extremely bad from a database perspective.

数据库不是面向对象的，从面向对象的角度来看看起来不错的代码从数据库的角度来看可能非常糟糕。

Our developers tell us they are glad that all our databse access is through procs becasue it makes it much faster to fix a data-centered bug and then simply run the proc on the production environment rather than create a new branch of the code and recompile and reload to production. We require all our procs to be in subversion, so source control is not an issue at all. If it isn't in Subversion, it will periodically get dropped by the dbas, so there is no resistance to using Source Control.

我们的开发人员告诉我们，他们很高兴我们所有的数据库访问都是通过 procs 进行的，因为它可以更快地修复以数据为中心的错误，然后只需在生产环境中运行 proc，而不是创建代码的新分支并重新编译和重新加载到生产。我们要求所有的 proc 都处于 subversion 状态，因此源代码控制根本不是问题。如果它不在 Subversion 中，它会定期被 dbas 删除，因此对使用 Source Control 没有抵抗力。

回答by Eric Z Beard

Stored procedures represent a software contract that encapsulates the actions taken against the database. The code in the procedures, and even the schema of the database itself can be changed without affecting compiled, deployed code, just so the inputs and outputs of the procedure remain the same.

存储过程代表一个软件契约，它封装了对数据库采取的操作。过程中的代码，甚至数据库本身的模式都可以在不影响编译、部署的代码的情况下进行更改，因此过程的输入和输出保持不变。

By embedding queries in your application, you are tightly coupling yourself to your data model.

通过在应用程序中嵌入查询，您将自己与数据模型紧密耦合。

For the same reason, it is also not good practice to simply create stored procedures that are just CRUD queries against every table in your database, since this is still tight coupling. The procedures should instead be bulky, coarse grained operations.

出于同样的原因，简单地创建只是针对数据库中每个表的 CRUD 查询的存储过程也不是好的做法，因为这仍然是紧密耦合的。程序应该是庞大的、粗粒度的操作。

From a security perspective, it is good practice to disallow db_datareader and db_datawriter from your application and only allow access to stored procedures.

从安全角度来看，在您的应用程序中禁止 db_datareader 和 db_datawriter 并且只允许访问存储过程是一种很好的做法。

回答by Krantz

Stored procedures are definitely the way to go...they are compiled, have execution plan before hand and you could do rights management on them.

存储过程绝对是要走的路……它们被编译，事先有执行计划，你可以对它们进行权限管理。

I do not understand this whole source control issue on stored procedure. You definitely can source control them, if only you are a little disciplined.

我不了解存储过程的整个源代码控制问题。你绝对可以对它们进行源代码控制，只要你有点自律。

Always start with a .sql file that is the source of your stored procedure. Put it in version control once you have written your code. The next time you want to edit your stored procedure get it from your source control than your database. If you follow this, you will have as good source control as your code.

始终从作为存储过程源的 .sql 文件开始。编写代码后，将其置于版本控制中。下次你想编辑你的存储过程时，从你的源代码管理而不是你的数据库中获取它。如果您遵循这一点，您将拥有与您的代码一样好的源代码控制。

I would like to quote Tom Kyte from Oracle here...Here's his rule on where to write code...though a bit unrelated but good to know I guess.

我想在这里引用 Oracle 的 Tom Kyte ......这是他关于在哪里编写代码的规则......虽然有点无关，但我想知道很高兴。

1. Start with stored procedures in PL/SQL...
2. If you think something can't be done using stored procedure in PL/SQL, use Java stored procedure.
3. If you think something can't be done using Java Stored procedure, consider Pro*c.
4. If you think you can't achieve something using Pro*C, you might want to rethink what you need to get done.

1. 从 PL/SQL 中的存储过程开始...
2. 如果您认为在 PL/SQL 中使用存储过程无法完成某些事情，请使用 Java 存储过程。
3. 如果您认为使用 Java 存储过程无法完成某些事情，请考虑 Pro*c。
4. 如果您认为使用 Pro*C 无法实现某些目标，您可能需要重新考虑需要完成的工作。

回答by garethm

In our application, there is a layer of code that provides the content of the query (and is sometimes a call to a stored procedure). This allows us to:

在我们的应用程序中，有一层代码提供查询的内容（有时是对存储过程的调用）。这使我们能够：

• easily have all the queries under version control
• to make what ever changes are required to each query for different database servers
• eliminates repetition of the same query code through out our code

• 轻松将所有查询置于版本控制之下
• 对不同数据库服务器的每个查询进行所需的更改
• 在我们的代码中消除了相同查询代码的重复

Access control is implemented in the middle layer, rather than in the database, so we don't need stored procedures there. This is in some ways a middle road between ad hoc queries and stored procs.

访问控制是在中间层实现的，而不是在数据库中，所以我们不需要那里的存储过程。在某些方面，这是临时查询和存储过程之间的中间道路。

回答by Almond

Store procedures should be used as much as possible, if your writing SQL into code your already setting yourself up for headaches in the futures. It takes about the same time to write a SPROC as it does to write it in code.

应该尽可能多地使用存储过程，如果您将 SQL 写入代码中，您已经让自己在未来感到头疼。编写 SPROC 所需的时间与编写代码所需的时间大致相同。

Consider a query that runs great under a medium load but once it goes into fulltime production your badly optimized query hammers the system and brings it to a crawl. In most SQL servers you are not the only application/service that is using it. Your application has now brought a bunch of angry people at your door.

考虑一个在中等负载下运行良好的查询，但是一旦它进入全职生产，您优化不当的查询就会重创系统并使其陷入困境。在大多数 SQL 服务器中，您并不是唯一使用它的应用程序/服务。你的申请现在已经把一群愤怒的人带到你家门口。

If you have your queries in SPROCs you also allow your friendly DBA to manage and optimize with out recompiling or breaking your app. Remember DBA's are experts in this field, they know what to do and not do. It makes sense to utilise their greater knowledge!

如果您在 SPROC 中有查询，您还可以让友好的 DBA 管理和优化，而无需重新编译或破坏您的应用程序。请记住，DBA 是该领域的专家，他们知道该做什么和不该做什么。利用他们更多的知识是有意义的！

EDIT: someone said that recompile is a lazy excuse! yeah lets see how lazy you feel when you have to recompile and deploy your app to 1000's of desktops, all because the DBA has told you that your ad-hoc Query is eating up too much Server time!

编辑：有人说重新编译是一个懒惰的借口！是的，让我们看看当您必须重新编译并将您的应用程序部署到 1000 台桌面时，您是多么懒惰，这一切都是因为 DBA 告诉您，您的临时查询占用了太多服务器时间！

回答by Seibar

My answer from a differentpost: Stored Procedures are MOREmaintainable because:

我在另一篇文章中的回答：存储过程更易于维护，因为：

• You don't have to recompile your C# app whenever you want to change some SQL
• You end up reusing SQL code.

• 每当您想更改某些 SQL 时，都不必重新编译 C# 应用程序
• 您最终会重用 SQL 代码。

Code repetition is the worstthing you can do when you're trying to build a maintainable application!

当您尝试构建可维护的应用程序时，代码重复是最糟糕的事情！

What happens when you find a logic error that needs to be corrected in multiple places? You're more apt to forget to change that last spot where you copy & pasted your code.

当您发现需要在多个地方更正的逻辑错误时会发生什么？您更容易忘记更改复制和粘贴代码的最后一个位置。

In my opinion, the performance & security gains are an added plus. You can still write insecure/inefficient SQL stored procedures.

在我看来，性能和安全性的提升是一个额外的好处。您仍然可以编写不安全/低效的 SQL 存储过程。

Easier to port to another DB - no procs to port

更容易移植到另一个数据库 - 没有移植的过程

It's not very hard to script out all your stored procedures for creation in another DB. In fact - it's easierthan exporting your tables because there are no primary/foreign keys to worry about.

将所有存储过程编写为在另一个数据库中创建的脚本并不难。事实上 - 它比导出表更容易，因为没有主键/外键需要担心。

回答by JamesSugrue

There are persuasive arguments for both - stored procedures are all located in a central repository, but are (potentially) hard to migrate and ad hoc queries are easier to debug as they are with your code, but they can also be harder to find in the code.

两者都有说服力的论据 - 存储过程都位于中央存储库中，但（可能）难以迁移，并且临时查询更容易调试，因为它们与您的代码一样，但它们也更难在代码。

The argument that stored procedures are more efficient doesn't hold water anymore. link text

存储过程更有效的论点不再成立。 链接文字

Doing a google for Stored Procedure vs Dynamic Query will show decent arguments either way and probably best for you to make your own decision...

为存储过程与动态查询做一个谷歌将显示体面的论点无论哪种方式，可能最适合你做出自己的决定......

回答by AR.

Some things to think about here: Who Needs Stored Procedures, Anyways?

这里需要考虑的一些事情：无论如何，谁需要存储过程？

Clearly it's a matter of your own needs and preferences, but one very important thing to think about when using ad hoc queries in a public-facing environment is security. Always parameterize them and watch out for the typical vulnerabilities like SQL-injection attacks.

显然，这取决于您自己的需求和偏好，但是在面向公众的环境中使用临时查询时要考虑的一件非常重要的事情是安全性。始终对它们进行参数化，并注​​意SQL 注入攻击等典型漏洞。