- Good at performance as all operations are happens inside DBMS system itself. Also nowadays most of DBMS system has mechanism of caching query execution plan so it can perform even faster at future calls. Query execution plan remains same during each round of executions of a stored procedure.
- Provides another layer of abstraction by providing separation between application and underlying DBMS system.
- Reduces network traffic by avoiding repetitive calls to DBMS system.
Cons:
- There are very high chances that business logic gets spread at two places. So it’s difficult to manage the business logic.
- We need versioning of stored procedures as its part of project deliverable and we can not check out a copy of stored procedure from DBMS system. We are left with two copies, one which is present into DBMS system and the other one which is present at SVN. So it’s hard to maintain latest valid copy of a stored procedure at DBMS system.
- Stored procedures are bad at scalability. We can do horizontal as well as vertical scaling of J2EE application server for doing load balancing but we can not do the same for DBMS system as they don’t support clustering. Oracle has some support for clustering. So stored procedure execution can become bottleneck into multi user system with high load.
- Using stored procedure means another layer of abstraction and another language to learn.
- Hard to debug from the same IDE which is used for development. So its affects productivity.
- There are very high chance of java developers writing improper stored procedure like stored procedure with less efficient queries, open cursors, improper error handling, improper state management etc.
- Stored procedure language is procedural language. So it’s very difficult to write a complex logic into it. Even if we write it ships with all disadvantages of procedural language. E.g. If we need to do two operations on a Customer then we need to write down two procedures. Lots of code used in these two stored procedures may be same but we can not reuse it. So we might end up in having lots of code duplication.
- Reduces testability of application.
My comments:
Each and every person in team should understand clear distinction between data modeling operation and business operation for a given functionality. So business logic should go into service layer and the data related operation if it’s too complex then should go into stored procedures. I believe that if we have very big DBMS schema with large number of records in it, we should keep data related operation (E.g. data formatting, data aggregation etc.) into stored procedures. We should have proper process in place for managing this kind clear distinction from the starting of the project itself otherwise it might become very difficult to control it at later point of time.
Most of the above cons can be overcome by having proper processes in place as well as better tooling support.
Personally I don’t like to use stored procedures, if I have other good options at Java space for given requirements. But if stored procedure is the only good option which can fit well to the given requirements then I don’t mind in doing that keeping above distinctions in mind.
I may be wrong in stating above comment. I would like to correct my perceptions if they are wrong. It will really help me going further. Thanks in advance for the same.
Some quotes:
"If you want portability keep logic out of SQL." - Martin Fowler
"Do not use stored procedures to implement business logic. This should be done in Java business objects." - Rod Johnson
References:
http://codebetter.com/blogs/jeremy.miller/archive/2005/07/06/130094.aspx
http://www.martinfowler.com/articles/dblogic.html
http://thinkoracle.blogspot.com/2006/01/plsql-vs-j2ee.html
http://www.codinghorror.com/blog/archives/000117.html
http://www.theserverside.com/discussions/thread.tss?thread_id=32141
