可以“将业务逻辑移至应用层” 提高性能?
在我当前的项目中,业务逻辑是在存储过程(超过 1000 个)中实现的,现在他们希望随着业务的增长而扩展它。 架构师决定将业务逻辑移至应用程序层 (.net),以提高性能和可扩展性。 但他们并没有重新设计/重写任何东西。 简而言之,从 SP 激发的相同 SQL 查询将从使用 ADO.Net 的 .net 函数激发。 这怎么能产生任何性能呢?
据我所知,当我们需要数据库独立性或者某些业务逻辑可以用 OOP 语言比 RDBMS 引擎更好地实现时(例如遍历层次结构或某些图像处理, ETC..)。 其余情况下,如果没有复杂的业务逻辑需要实现,我认为最好将业务逻辑保留在DB本身中,这样至少可以避免应用层和DB之间的网络延迟。
请告诉我您的看法。 我是一名开发人员,在考虑一些架构决策时有些犹豫,请原谅我对这个主题的无知。
In my current project, the business logic is implemented in stored procedures (a 1000+ of them) and now they want to scale it up as the business is growing. Architects have decided to move the business logic to application layer (.net) to boost performance and scalability. But they are not redesigning/rewriting anything. In short the same SQL queries which are fired from an SP will be fired from a .net function using ADO.Net. How can this yield any performance?
To the best of my understanding, we need to move business logic to application layer when we need DB independence or there is some business logic that can be better implemented in a OOP language than an RDBMS engine (like traversing a hierarchy or some image processing, etc..). In rest of the cases, if there is no complicated business logic to implement, I believe that it is better to keep the business logic in DB itself, at least the network delays between application layer and DB can be avoided this way.
Please let me know your views. I am a developer looking at some architecture decisions with a little hesitation, pardon my ignorance in the subject.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
如果您的业务逻辑仍然在 SQL 语句中,数据库将执行与以前一样多的工作,并且您不会获得更好的性能。 (如果不能像使用存储过程那样有效地缓存查询计划,可能需要做更多的工作)
为了获得更好的性能,您需要将一些工作转移到应用程序层,例如,您可以在应用程序服务器上缓存数据吗?在不访问数据库的情况下进行查找或验证检查?
If your business logic is still in SQL statements, the database will be doing as much work as before, and you will not get better performance. (may be more work if it is not able to cache query plans as effectivily as when stored procedures were used)
To get better performance you need to move some work to the application layer, can you for example cache data on the application server, and do a lookup or a validation check without hitting the database?
诸如此类的架构论点通常需要考虑许多权衡,单独考虑性能,或者仅考虑性能的一个方面(例如响应时间)往往会忽略更大的情况。
显然,在数据库层中执行逻辑与将数据传送回应用层并在那里进行处理之间存在一些权衡。 数据传输成本与处理成本。 正如您所指出的,业务逻辑的成本和复杂性将是一个重要因素,而要传输的数据的大小将是另一个因素。
可以想象,如果数据库层变得繁忙,即使单个响应时间增加,将处理卸载到另一层也可能允许更大的总体吞吐量。 然后我们可以扩展应用程序层以处理一些额外的负载。 您现在会说性能得到了改善(总体吞吐量更大)还是恶化了(响应时间有所增加)。
现在考虑应用程序层是否可以实现有趣的缓存策略。 也许我们获得了非常大的性能胜利——对于某些请求,数据库根本没有负载!
Architectural arguments such as these often need to consider many trades-off, considering performance in isolation, or ideed considering only one aspect of performance such as response time tends to miss the larger picture.
There clearly some trade off between executing logic in the database layer and shipping the data back to the applciation layer and processing it there. Data-ship costs versus processing costs. As you indicate the cost and complexity of the business logic will be a significant factor, the size of the data to be shipped would be another.
It is conceivable, if the DB layer is getting busy, that offloading processing to another layer may allow greater overall throughput even if the individual responses time are increased. We could then scale the App tier in order to deal with some extra load. Would you now say that performance has been improved (greater overall throughput) or worsened (soem increase in response time).
Now consider whether the app tier might implement interesting caching strategies. Perhaps we get a very large performance win - no load on the DB at all for some requests!
我认为这些决定不应该用建筑教条来证明是合理的。 数据会更有意义。
像“所有业务逻辑都属于存储过程”或“一切都应该在中间层”这样的说法往往是由那些知识仅限于数据库或对象的人分别提出的。 判断时最好将两者结合起来,并根据测量结果进行判断。
例如,如果您的一个过程正在处理大量数据并返回少量结果,则有一种说法认为它应该保留在数据库中。 将数百万行放入中间层的内存中,对它们进行处理,然后再进行一次往返更新数据库,这是没有意义的。
另一个考虑因素是数据库是否在应用程序之间共享。 如果是这样,逻辑应该保留在数据库中,以便所有人都可以使用它。
中间层往往会来来去去,但数据却永远存在。
我自己也是个有目标的人,但我会小心行事。
这是一个复杂的问题。 我认为非黑即白的陈述并不适用于所有情况。
I think those decisions should not be justified using architectural dogma. Data would make a great deal more sense.
Statements like "All business logic belongs in stored procedures" or "Everything should be on the middle tier" tend to be made by people whose knowledge is restricted to databases or objects, respectively. Better to combine both when you judge, and do it on the basis of measurements.
For example, if one of your procedures is crunching a lot of data and returning a handful of results, there's an argument that says it should remain on the database. There's little sense in bringing millions of rows into memory on the middle tier, crunching them, and then updating the database with another round trip.
Another consideration is whether or not the database is shared between apps. If so, the logic should stay in the database so all can use it.
Middle tiers tend to come and go, but data remains forever.
I'm an object guy myself, but I would tread lightly.
It's a complicated problem. I don't think that black and white statements will work in every case.
正如其他人已经说过的,这取决于很多因素。 但从你的问题来看,架构师似乎建议将存储过程从数据库内部转移到应用程序内部的动态 SQL。 这对我来说听起来很可疑。
SQL是一种面向集合的语言,需要处理大量数据记录的业务逻辑在SQL中会更好。 考虑复杂的搜索和报告类型功能。 另一方面,使用编程语言进行具有相应业务规则验证的行项目编辑要好得多。 在应用程序层缓存缓慢变化的数据是另一个优点。 如果您有专用的中间层服务作为所有数据的网关,那就更好了。 如果数据直接在不同的应用程序之间共享,那么存储过程可能是一个好主意。
您还必须考虑组织中 SQL 人才与编程人才的可用性/经验。
这个问题确实没有通用的答案。
Well as others have already said, it depends on many factors. But from you question it seems the architects are proposing moving the stored procedures from inside DB to dynamic SQL inside the application. That sounds very dubious to me.
SQL is a set oriented language and business logic that requires massaging of large amount of data records would be better in SQL. Think complicated search and reporting type function. On the other hand line item edits with corresponding business rule validation is much better being done in a programming language. Caching of slow changing data in app tier is another advantage. This is even better if you have dedicated middle tier service that acts as a gateway to all the data. If data is shared directly among disparate applications then stored proc may be a good idea.
You also have to factor the availability/experience of SQL talent vs programming talent in the organisation.
There is realy no general answer to this question.