Saturday, April 25, 2009

What should I use - stored procedures or application code?

I've often thought about using a stored procedure versus using application code to accomplish tasks. The rules I use are pretty simple:
  1. Let the database generate data for itself (from data it already has).
  2. Let the database maintain data security and integrity where appropriate.
  3. Let the database help me administer it.
I've chosen to shy away from asking the database to handle business logic internally for a number of reasons, largely because databases are typically very expensive resources in comparison to application servers. It may also be difficult to revision business logic stored in the database, therefore making it more difficult to track how that code has changed.

I do understand the value of stored procedures reducing network traffic for certain types of queries as well as guaranteeing the effective use of the query cache, however, I find that applications can be designed to use libraries of SQL in such a way that the query cache is used effectively and network traffic is reduced through the proper use of joins, cursors and temporary tables.

Talking with other DBA's, some agree with me, some don't. What are your thoughts about when to use stored procedures versus application code?

No comments:

Post a Comment