Stored Procedures v ORM/Dynamic SQL

Recently we had a pretty fierce debate internally about the best strategy for data access in architectural design. 

Predictably, I played middle-of-the-field, “it depends”, but one of my co-workers, Omar Besiso was inspired to write this excellent entry.

I’m not going to rehash the same viewpoint I shared last week.

All I’d care to point out is that a database is not necessarily just a backing data store for an application.  Especially large or “enterprise” databases (or applications) and especially given enough time.

It’s important to consider:
– ETL / Loads
– External integration/synchronization (BizTalk/SSIS)
– Replication/fail over (availability)
– Reuse by other applications (particularly web sites, mobile applications etc), and lastly,
– Scalability (linked servers, clustering, partitioning etc)

I’m not stating that Stored Procedures are always the answer, but what I do caution is that any data access design pattern requires “big picture” thought. 

Security should be considered up front, not when 60% of the development is already complete!

There’s a place, I think, for both the Entity Framework/LINQ to SQL, traditional Stored Procedures and other tools like NHibernate (etc), but you should justify and rationalise the choice of technology first, and try to ensure it is appropriate for the present and future needs.

Leave a comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.