07 Nov 2005
Discussion From Work...
Here are just a few articles to back up my statements that the logic _should_ stay out of the database. If you have some feedback you want to provide I'm always open and can be swayed to the Stored Procedure way - but, they do have to be compelling arguments.
Quote(s) straight from MySQL:
"However, stored procedures of course do increase the load on the database server system, as more of the work is done on the server side and less on the client (application) side."
"Triggers and Stored procedures are wonderful tools, but they come with a price. Enforcing the business rules at the database level allows you to be confident that the data conforms. There are a few cautionary notes however.
The first is that you will probably end up creating your own procedural language which will differ from the other procedural language and start to introduce further discrepancies. If possible, using an existing language and creating an interpreter for it would be preferable. Although I am fluent in Oracle PL/SQL and SQL Server 2000 T-SQL, I know that I would really rather not be. It would be an advantage to me to be able to use a standard language so that I could leverage those skills elsewhere. A database where I had to learn yet another proprietary instruction set would make me dubious of wanting to get involved with it.
Performance will degrade and it will take awhile to get it to a satisfactory level. This does not take anything away from the development team, it has been true of every database system when they have done this. DB2 still has a reputation of being slow and a memory hog when you use their triggers and stored procedures. Teradata only recently added the ability to use them and it implemented them so poorly that nobody wants them. Be prepared for the performance hit and the resulting aftermath.
A possible way around this would be to include further support for server software that applies the business rules without actually being on the database. J2EE, for example, could be utilized with Enterprise Java Beans and have the exact same net effect as having stored procedures and triggers, without actually having to modify the database."
--This also brings up a very good point... look at mysql system requirements... less of a system is needed, where as with stored procedures you need a workhorse with loads of RAM to cache all of those stored 'compiled' procedures. Not to mention including the processor time needed to compile the stored procedures in the first place (don't forget page-swapping to handle all the memory requirements, reloading of the stored procs at restart... I could go on...).
http://dotnetjunkies.com/WebLog/johnwood/archive/2005/01/19/46398.aspx
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
http://www.windowsitpro.com/sqlserver/forums/messageview.cfm?catid=1669&threadid=130201
On a lighter note: What happens with more procedure calling than is necessary in JAVA?
http://madbean.com/anim/jarwars/
{"display_name"=>"chris", "login"=>"chris", "email"=>"crmacd@gmail.com", "url"=>"http://www.crmacd.com"}