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?
在我当前的项目中,业务逻辑是在存储过程(1000多个)中实现的,现在他们希望随着业务的增长而扩展它。架构师已决定将业务逻辑移至应用层(.net)以提高性能和可扩展性。但他们并没有重新设计/改写任何东西。简而言之,使用ADO.Net从.net函数触发从SP触发的相同SQL查询。这怎么能产生任何表现?
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.
据我所知,当我们需要数据库独立性时,我们需要将业务逻辑移动到应用程序层,或者有一些业务逻辑可以在OOP语言中比RDBMS引擎更好地实现(如遍历层次结构或某些图像处理,等等..)。在其他情况下,如果没有复杂的业务逻辑要实现,我认为最好将业务逻辑保留在DB本身,至少可以通过这种方式避免应用层和DB之间的网络延迟。
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.
请让我知道你的看法。我是一个开发人员,有点犹豫地看着一些架构决策,原谅我对这个主题的无知。
4 个解决方案
#1
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)
如果您的业务逻辑仍然在SQL语句中,那么数据库将执行与以前一样多的工作,并且您将无法获得更好的性能。 (如果无法像使用存储过程那样有效地缓存查询计划,则可能会更有效)
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?
要获得更好的性能,您需要将一些工作移到应用程序层,例如,您可以在应用程序服务器上缓存数据,并在不访问数据库的情况下执行查找或验证检查吗?
#2
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.
在数据库层中执行逻辑和将数据传送回applciation层并在那里处理它之间显然需要权衡。数据运输成本与处理成本。当您指出业务逻辑的成本和复杂性将是一个重要因素时,要发送的数据的大小将是另一个。
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).
可以想象,如果DB层变得繁忙,即使个体响应时间增加,卸载到另一层的处理也可以允许更大的总吞吐量。然后我们可以扩展App层以处理一些额外的负载。您现在会说性能已经提高(总吞吐量更高)或更差(响应时间增加)。
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!
现在考虑应用层是否可以实现有趣的缓存策略。也许我们获得了非常大的性能胜利 - 对于某些请求,DB根本没有负载!
#3
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.
这是一个复杂的问题。我不认为黑白陈述在每种情况下都会起作用。
#4
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.
正如其他人已经说过的那样,这取决于很多因素。但是从你的问题来看,架构师似乎建议将存储过程从内部DB移动到应用程序内部的动态SQL。这对我来说听起来很可疑。 SQL是一种面向集合的语言和业务逻辑,需要按摩大量数据记录,在SQL中会更好。想想复杂的搜索和报告类型功能。另一方面,使用相应的业务规则验证的行项目编辑在编程语言中要好得多。缓存app层中缓慢变化的数据是另一个优势。如果您有专用的中间层服务作为所有数据的网关,那就更好了。如果数据直接在不同的应用程序之间共享,则存储过程可能是个好主意。您还必须考虑组织中SQL人才与编程人才的可用性/经验。这个问题确实没有一般答案。
#1
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)
如果您的业务逻辑仍然在SQL语句中,那么数据库将执行与以前一样多的工作,并且您将无法获得更好的性能。 (如果无法像使用存储过程那样有效地缓存查询计划,则可能会更有效)
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?
要获得更好的性能,您需要将一些工作移到应用程序层,例如,您可以在应用程序服务器上缓存数据,并在不访问数据库的情况下执行查找或验证检查吗?
#2
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.
在数据库层中执行逻辑和将数据传送回applciation层并在那里处理它之间显然需要权衡。数据运输成本与处理成本。当您指出业务逻辑的成本和复杂性将是一个重要因素时,要发送的数据的大小将是另一个。
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).
可以想象,如果DB层变得繁忙,即使个体响应时间增加,卸载到另一层的处理也可以允许更大的总吞吐量。然后我们可以扩展App层以处理一些额外的负载。您现在会说性能已经提高(总吞吐量更高)或更差(响应时间增加)。
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!
现在考虑应用层是否可以实现有趣的缓存策略。也许我们获得了非常大的性能胜利 - 对于某些请求,DB根本没有负载!
#3
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.
这是一个复杂的问题。我不认为黑白陈述在每种情况下都会起作用。
#4
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.
正如其他人已经说过的那样,这取决于很多因素。但是从你的问题来看,架构师似乎建议将存储过程从内部DB移动到应用程序内部的动态SQL。这对我来说听起来很可疑。 SQL是一种面向集合的语言和业务逻辑,需要按摩大量数据记录,在SQL中会更好。想想复杂的搜索和报告类型功能。另一方面,使用相应的业务规则验证的行项目编辑在编程语言中要好得多。缓存app层中缓慢变化的数据是另一个优势。如果您有专用的中间层服务作为所有数据的网关,那就更好了。如果数据直接在不同的应用程序之间共享,则存储过程可能是个好主意。您还必须考虑组织中SQL人才与编程人才的可用性/经验。这个问题确实没有一般答案。