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
  1. Kirk
    May 14th, 2009 at 09:04 | #1

    You might want to add that trying to do too much in the database can limit scalability. Also, stored procedures make it difficult to cache in the application layer.

  2. May 14th, 2009 at 12:31 | #2

    The performance argument is especially tricky.

    According to Frans Bouma stored procedures are not per se more performant than dynamic queries:

    http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
    http://www.theserverside.net/news/thread.tss?thread_id=31953#158113

    There might even be cases where dynamic queries are faster than stored procedures (purely the execution of the query).

    In my experience stored procedures mostly imply another design paradigm (i.e. more set-based operations, for instance for batch operations or reports), which obviously has a better performance in certain scenarios. But in those cases the performance boost is not based on using stored procs but on the design paradigm. The same paradigm can be realized with dynamic queries.

  3. Ian Lintner
    May 14th, 2009 at 14:33 | #3

    If stored procedures are implemented right, in many cases, they will reduce chattiness, because you can perform multiple actions *easily* in a single stored procedure, but this is really a performance issue.

  4. Gregory Kornblum
    May 14th, 2009 at 16:03 | #4

    Great article. It is something valuable to both DBAs and developers. Although I love the centralized and performance aspects of SPs but at the same time it needs to stay away from domain logic.

    I have dealt with Agile developers heavily reliant on ORMs say they should never be used and others say that they should always be used. Like you I like to be right in the middle.

    Regards,
    Gregory
    Tensai Labs – The Middle Road of IT
    http://www.tensailabs.com/

  5. satish
    May 14th, 2009 at 15:22 | #5

    Hi,

    Would it not be easier to maintain transaction atomicity, commits and rollbacks in the Stored procedure, compared to Java or C# or worse, ORM technologies ??

  6. Terry
    May 14th, 2009 at 16:10 | #6

    I tend to agree about not using stored procedures for heavy business logic. However, they make good sense for data filtering and transformation. The only situation where I’ve really seen stored procedures really break down is when you need to iterate one by one thru a resultset. The CURSOR construct is lacking to say the least. Managing the opening and closing of the CURSOR reminds me of manual memory management from C/C++ not a pretty picture.

  7. Paul
    May 14th, 2009 at 16:31 | #7

    You wrote “Danger of duplicity of logic (in c# and in t-sql)”

    Did you mean “duplicity” as in deceptive logic or duplicate logic? Both meanings might be applicable.

  8. May 14th, 2009 at 17:37 | #8

    @Paul: I meant duplicate logic. By the way C# was just a placeholder for any other programming language like java, ruby, etc.

  1. No trackbacks yet.