Why We Hate Stored Procedures
Okay we really don’t hate stored procedures and have actually used them extensively on past projects but we so often tell people to remove them that we’ve been accused of hating them. Here’s the deal, as we mentioned in a past post, two scalability best practices are 1) put as little business logic in the database layer as possible and 2) use as few features of the RDBMS as possible. Both of these are violated by the use of stored procedures, if they contain any business logic. If the stored procedures are simply instantiations of SQL data statements then you’re probably okay.
Expanding on the two reasons for avoiding stored procedures mentioned above, we’ll first discuss why business logic doesn’t belong in the database layer. The biggest reason for this is that the database server is likely the most expensive server in your system and the most difficult to scale. Adding more computational processing on it makes it the most expensive layer to perform these and requires you to upgrade the hardware or split the database sooner than necessary. It is much cheaper from a cost per computation perspective to have this business logic processed on a much cheaper application server.
Avoiding the use of RDBMS features is important when it comes to remaining vendor agnostic and getting the best possible price during negotiations. Using features specific to certain database vendors locks you in to that vendor. While you may never want to switch database vendors it does reduce your negotiating strength. When you are locked in to a vendor your best alternative to a negotiated agreement, BATNA, is not good and their sales staff knows this.
While we don’t hate stored procedures we do seem them as an impediment to scaling. If you already have them in place you may be hesitant to spend the money moving them into the application logic. One method of doing so at minimal incremental cost is to start today with the policy that if any engineer touches a stored procedure they must move it. This way the cost is small, because you’re already touching the code, and spread out because it’s not done all at once.