In my experience, i have come across maintaining some software where the business logic was written in stored procedures (or functions/triggers). Recently i collected some points on why this is not such a good idea. The main reason for the use of stored procedures in one product was to have multiple client types to be able to invoke the same business logic. But then we can achieve the same effect applying MVC (model view controller) pattern and keeping the model/business logic in the application code rather than stored procedure. Here are some other reasons why its not a good idea to write BL in stored procedures:
NOTE: Most of the ideas presented below are excerpted from this article.
1. If there are more than one interfaces and the BL is part in stored procedure and part in presentation tier then it becomes a maintenance headache to keep the different presentation tiers in synch.
2. Stored procedures form an API by themselves. Adding new functionality or new procedures is the "best" way to extend an existing API.This means that when a table changes, or behaviour of a stored procedure changes and it requires a new parameter, a new stored procedure has to be added. When stored proc is changed, the DAL/BL code needs to change too to call the changed/new stored proc whereas, if the SQL is generated on the fly from the DAL/BL code and there is no stored proc then only DAL code will change.
Microsoft also believes stored procedures are over: it's next generation business framework MBF is based on Objectspaces, which generates SQL on the fly.
In Java world, ORM (Object to Relational Mapping) frameworks like Hibernate and TopLink (and now Java Persistence Architecture, JPA) are meant to generate SQL on the fly too.
3.Business logic in stored procedures is more work to test than the corresponding logic in the application. Referential integrity will often force you to setup a lot of other data just to be able to insert the data you need for a test.Stored procedures are inherently procedural in nature, and hence harder to create isolated tests and prone to code duplication. Another consideration, and this matters a great deal in a sizable application, is that any automated test that hits the database is slower than a test that runs inside of the application. Slow tests lead to longer feedback cycles.
4. While stored procedures may run faster, they take longer to build, test, debug and maintain, therefore this extra speed comes at a price.
5. BL in stored procs does not scale - If you have a system with 100's of distributed databases it is far more difficult to keep all those stored procedures and triggers synchronized than it is to keep the application code synchronized.
6. Locked in to the DB for which stored procs are written.
7. Porting the data will be one exercise, but porting the stored procedures and triggers will be something else entirely. Now, if all that logic were held inside the application, how much simpler would it be?
All changes made to the database can be logged without using a single database trigger. How? By adding extra code into the DAO to write all relevant details out to the AUDIT database. This functionality is totally transparent to all the objects in the Business Layer, and they do not need any extra code to make it work.
NOTE: Most of the ideas presented below are excerpted from this article.
1. If there are more than one interfaces and the BL is part in stored procedure and part in presentation tier then it becomes a maintenance headache to keep the different presentation tiers in synch.
2. Stored procedures form an API by themselves. Adding new functionality or new procedures is the "best" way to extend an existing API.This means that when a table changes, or behaviour of a stored procedure changes and it requires a new parameter, a new stored procedure has to be added. When stored proc is changed, the DAL/BL code needs to change too to call the changed/new stored proc whereas, if the SQL is generated on the fly from the DAL/BL code and there is no stored proc then only DAL code will change.
Microsoft also believes stored procedures are over: it's next generation business framework MBF is based on Objectspaces, which generates SQL on the fly.
In Java world, ORM (Object to Relational Mapping) frameworks like Hibernate and TopLink (and now Java Persistence Architecture, JPA) are meant to generate SQL on the fly too.
3.Business logic in stored procedures is more work to test than the corresponding logic in the application. Referential integrity will often force you to setup a lot of other data just to be able to insert the data you need for a test.Stored procedures are inherently procedural in nature, and hence harder to create isolated tests and prone to code duplication. Another consideration, and this matters a great deal in a sizable application, is that any automated test that hits the database is slower than a test that runs inside of the application. Slow tests lead to longer feedback cycles.
4. While stored procedures may run faster, they take longer to build, test, debug and maintain, therefore this extra speed comes at a price.
5. BL in stored procs does not scale - If you have a system with 100's of distributed databases it is far more difficult to keep all those stored procedures and triggers synchronized than it is to keep the application code synchronized.
6. Locked in to the DB for which stored procs are written.
7. Porting the data will be one exercise, but porting the stored procedures and triggers will be something else entirely. Now, if all that logic were held inside the application, how much simpler would it be?
All changes made to the database can be logged without using a single database trigger. How? By adding extra code into the DAO to write all relevant details out to the AUDIT database. This functionality is totally transparent to all the objects in the Business Layer, and they do not need any extra code to make it work.
No comments:
Post a Comment