Some great points about placing business logic in stored procedures, about portability among DBMSs, about organizational or cultural conflicts, about the potential for minor bugs to affect many applications, and about internal vs external procedures.
It occurs to me that historically the reason some business logic may have been placed in stored procedures was because a solution's architecture called for some logic to be centralized and other logic to be distributed, rather than because of any special functionality unique to stored procedures, or because of any reason at all that was really related to database functionality. Isn't it the case that with the multi-tiered architectures common today, we really don't need to resort to stuffing business logic into stored procedures to accomplish the same goal? Seems like that's an old approach, and never was anything more than a workaround at best.
The question of reducing the number of calls to the DBMS over the network is at least partly solved by having database access code isolated in an application layer that lives on a centralized server; what some people call the 'business tier' or the 'EJB tier'. With that in mind, is this a real reason to resort to stored procedures? It may be a solution to the wrong problem.
The issue of work specialization was mentioned; some people prefer data modeling and others coding, and they may approach things differently. Doesn't the advent of Agile methods partially alleviate that problem? When building a solution that involved object-oriented code development and relational data modeling, you would normally pair with someone who had the other, complementary skill, and between the two of you you'd come up with a solid solution. It may be that some of the answers aren't technical at all.
1. Reduce data traffic between the AppServer and DB Server unless you are only developing a prototype with only 1000 rows in your tables:-)
2. Reduce the concurrent connections required at the Mid-tier, even though most App Server support connection pool, the limit will be very soon reached if you have a lot of users hitting the server.
Obviously, you need good DB developers not DBAs to make these store-procedures very well programmed and maintained, you should think that stored-procedures are programs just like any other Java Programs not some admin scripts.
Flat View: This topic has 17 replies
on 2 pages