Summary
This is a one-sided analysis of the downsides of using stored procedures. PLEASE do not take this to mean that I'm in any way against their *judicious* use.

Errors in the stored procedures tend to affect the database globally, even for applications that are not using it

For one of my clients, a procedure originally written and optimized for accessing small record sets (using temporary tables) was never re-optimized. A bug in the database related to large temporary tables crashed the production database. It took a day for the vendor's best database experts to debug and fix the problem.

Version skew becomes more and more likely as the application code and database code are spread over multiple staging environments

Some of the environments may have new functionality in some subsystems and rolled-back functionality in others. Keeping track of the skew between application code and database code is completely manual (no tools exist to automate) and can easily get lost.

Behavior changes in a large codebase are more easily implemented in application code than in database code

As the code base grows and specializes, different parts of the application will begin to require slightly different behavior from the stored procedure. This will require either increased complexity in the procedure or complexity in the API (by forking into a new stored procedure).

Software application environments are much easier to debug

Database debugging tools are severely lacking.

Over time, applications from one group become dependent on stored procedures under the control of another application group

Changes implemented by the primary application group can break the dependent application. See the comment on siloing too.

Application teams and database teams tend to "silo" and politically protect their areas

May not always be an issue, but if the staff grows to where the two areas are separated then human nature will make it more likely as each group tries to minimize effort.

Solving problems using stored procedures versus using application code does not lower application complexity

Since application brittleness and poor application performance are usually due to increasing complexity, faster database access provided by individual stored procedures becomes moot.

Talk Back!

Have an opinion? Readers have already posted 17 comments about this weblog entry. Why not add yours?

RSS Feed

If you'd like to be notified whenever Dale Asberry adds a new entry to his weblog, subscribe to his RSS feed.

Digg | del.icio.us | Reddit

About the Blogger

R. Dale Asberry been hacking since 1978, professionally since 1990. He's certified in Java 1.1 and has a four digit MCP number. He discovered Jini at the 2000 JavaOne and has been building incredibly cool, dynamic, distributed architectures ever since! Over time, he's discovered several principles that have contributed to his success - they are the Princples of: Enabling Others, Simplicity, No Complaining, Least Work, Least Surprise, Least Damage, and "It Just Works".