数据集与数据库
在设计应用程序时，将所有业务逻辑放在一个地方是一种非常好的做法。那么为什么我们有时会将业务逻辑存储在存储过程中呢？我们可以从数据库中获取所有数据并将其存储在 DataSet 中然后进行处理吗？在这种情况下应用程序的性能如何？
While designing applications it is a very good practice to have all the business logic in one place. So why then we sometimes have the business logic in stored procs? Can we fetch all data from the DB and store it in a DataSet and then process it? What would be the performance of the app in this scenario?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
因为有时您需要在将数据返回给客户端之前进行一些处理，当您只需要一个子集时，返回一堆行会很浪费。例如，当您必须涉及临时表或链接服务器时，一些复杂的事情在存储过程中也更容易完成
because sometime you need to do some processing before returning the data to the client, it would be wasteful to return a bunch of rows when you only need a subset. Some complex things are also easier to do in a stored proc when you have to involve temp tables or linked servers for example
想想这个基本的例子，它应该可以回答你的问题：
您希望最大限度地减少传递到应用程序层的数据量。您还希望以尽可能最快的速度完成该数据的处理。将其存储在数据集中不会为您提供索引和全文搜索选项等。我们使用数据库来存储和检索数据是有原因的，否则我们在启动时加载到内存中的简单平面文件就足够了。如果您的应用程序和数据很小，那么这可能是一种替代方案，但在大多数情况下并非如此。
Think of this basic example and it should answer your question:
You want to minimize the amount of data being passed across to your application layer. You also want to let the processing of that data be done in the fastest possible place. Storing it in a dataset will not give you indexes and full text search options etc. There is a reason we use databases for storing and retrieving data or else simple flat file that we load at startup into memory would be all that is needed. If your app and data is small then this could be an alternative but in most cases it's not.
我想应该进行处理，这样才更有意义。
例如，如果您的应用程序有一些进程需要来自应用程序和应用程序的较少输入。更多来自数据库，最好在数据库级别完成。
这还取决于您想要做的事情以及您想要做的事情。在数据库级别支持此类事情。
示例：正则表达式或数学函数的使用。
我认为将数据库中的所有数据放入应用程序内存中是没有意义的。
答案取决于数据有多少？它经常改变吗？如果数据库中的数据发生更改，您的应用程序将如何表现？
一般来说，如果您有某种静态数据，则可以将其放在应用程序内存和应用程序内存中。不然的话。
I guess the processing should be done, where it is more meaningful.
For example, if your application has some process for which it needs less input from application & more from database, it is better done at database level.
It also depends on things you are trying to do & support for such things at database level.
example: usage of regular expressions or mathematical functions.
I don't think it makes sense to have all the data from DB into application memory.
The answer depends on how much is the data? does it change often? how does your application behave, if the data changes in the database?
In general, if you have some kind of static data, it is ok to have it in application memory & not otherwise.
另一个重要原因是安全。您可能希望通过在存储过程中实现代码来监视对数据的访问（尤其是写入访问）。这样，所有对数据的访问都通过存储过程进行隧道传输，并且可以防止可疑访问
Another important reason is security. You might want to monitor access (especially write access) to data by implementing code in stored procedures. This way all access to data is tunneled through a stored procedure and suspicious access can be prevented
这两种模式都存在。
如果您需要进行严格的数学和字符串操作或矩阵数学，您最终可能会将大量数据库拉入内存。
如果您正在做的工作有一个可以通过 join 和 where 子句解决的解决方案，那么 SQL 就是合适的地方，即使人们可以提出一个哲学论点，认为它可以归类为业务逻辑。将基于集合的逻辑移至中间层可能会导致在中间层重新发明关系数据库，而且这不会做得很好！
当行数非常小时（而且总是如此），那么这并不重要。在单元测试、工具支持等方面，与 C# 或 java 相比，SQL 是一种相当贫乏的编程语言，因此，如果您的数据库中有 100 行（并且永远不会有更多！），那么无论如何，请随意将它们放入内存数据结构中——尽管如此，数据库在排序等领域仍然会更加高效并且错误更少。
Both patterns exist.
If you need to do serious math and string manipulation or matrix math, you might end up pulling a good deal of the db into memory.
If you doing work that has a solution that can be solved with join and where clauses, then SQL is the appropriate place for that, even if one could make a philosophical argument that it could be categorized as business logic. Moving that set based logic into a middle tier would likely lead to the re-inventing of the relational database in the middle tier and it wouldn't be done well!
When the number of rows is very small (and always will be), then it doesn't really matter. SQL is a rather impoverished programming language compared to C# or java when it comes to unit testing, tooling support, etc, so if you have 100 rows in your database (and never will have much more!), then by all means, feel free to put those into some in memory data structure-- although even there, a database will still be more efficient and less buggy in areas like sorting.