Home > Good practices, Software architecture > When to use stored procedures

When to use stored procedures

Recently I discussed with a colleague when to use stored procedures. As exptected it was quite a religious conversation. A few days later I found the following screencast:
The Pros and Cons of Stored Procedures

Based on the discussion and the screencast I tried to summarize my Pros and Cons:

Pros

  • Layering
  • Low-level-Security
  • Protect queries from changing data-access technologies (jdbc, hibernate, toplink, dbcommands, linq to sql, entity framework, nhibernate, etc…)
  • Ivory-Tower-Feature: changing database during project does nearly never happen
  • Coarse-grained Interface approach
  • Performance
  • DBA could tune the queries in a procedure
  • From an integration view, logic could be reuse by several applications

Cons

  • If all logic is in the database, there is a danger that the communication is too chatty (to many calls to the database-server), what is too expensive from a performance perspective
  • Security-Chaos (Too much security-rules on different layers)
  • If you are using the database just as an dump datastore
  • Use the database for what it is strong in it (managing data, not logic)
  • Developer has to maintain the logic in two different languages (java or c# and t-sql)
  • Hard to test (slow, friction with current frameworks, anti-pattern for unit-tests (use of a slow infrastructure))
  • Danger of duplicity of logic (in c# and in t-sql)
  • Bad refactoring support
  • DBA could change the logic in a procedure
  • From an integration view, there could be side-effects, if a stored procedure is used by several applications and one application change the logic

Conclusion
And what is the conclusion? Surprise, surprise: It depends. You shouldn’t use stored procedures for anything and you shouldn’t categorically reject them.
I tend to use stored procedures for performance issues and try to avoid to use them to implement business logic.
If you like this, follow me on twitter…

Share