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.

About Rob Sanders

IT Professional and TOGAF 9 certified Enterprise Architect with nearly two decades of industry experience, 18 years in commercial software development and 11 years in IT consulting. Check out the "About Rob" page for more information.

Leave a comment

Your email address will not be published. Required fields are marked *

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