AKF Partners

Abbott, Keeven & Fisher PartnersPartners In Hyper Growth

Why We Hate Stored Procedures

Here's a little color on our love-hate relationship with 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.


Comments RSS TrackBack 4 comments

  • Tweets that mention Why We Hate Stored Procedures | AKF Partners Blog -- Topsy.com

    in May 19th, 2010 @ 08:18

    […] This post was mentioned on Twitter by Mike Fisher. Mike Fisher said: Why We Hate Stored Procedures: Here's a little color on our love-hate relationship with stored procedures. http://bit.ly/9ziu5R #in […]


  • John R Nash

    in May 22nd, 2010 @ 10:07

    I agree wholeheartedly. When building large scale complex systems, modularity and a clear delineation between the layers of abstraction are key. Otherwise troubleshooting performance and stability issues becomes increasingly difficult as the size and complexity of the system grows.

    And another important aspect of this conversation is, of course, change control and documentation. If you don’t have a good idea where your stored procedures are and their intended function, the cost to track them down and migrate them to your application tier becomes much higher (and more likely not to happen).

    -John


  • Fish

    in May 22nd, 2010 @ 13:41

    John, thanks for the feedback and great additional points.

    Mike


  • Anon

    in June 5th, 2010 @ 14:43

    I like to make my applications as chatty as possible too. That way when the user clicks on something and it takes a few seconds to respond, they think some serious business logic is being executed and they are getting their money’s worth.