D'Arcy from Winnipeg
Solution Architecture, Business & Entrepreneurship, Microsoft, and Adoption

Has ORM Made Stored Procs Obsolete?

Friday, April 18, 2008 3:15 AM

I had an interesting conversation with Donald and Tom over lunch today around the stored procedures and their relevance to software development today. Their argument is that stored procedures have no value when a solution is utilizing an ORM mechanism (like NHibernate) because the ORM creates/maintains the database SQL queries. You end up with having to maintain and manage an extra layer when you incorporate SP's into the mix in this scenario.

Although they had good points, this seemed to work well only with CRUD based calls (which does tend to be the majority of the database interactions done within a system). I can still see a need for using SP's for complex reporting outputs, and although I'm sure there's an answer to this, I'm not aware of how transactional procedures are carried out within tools like NHibernate as opposed to implementing database trasnactions. (heh, although I'm expecting some comments from Donald and Tom on this post).

Anyway, just some thoughts...what are yours?



# re: Has ORM Made Stored Procs Obsolete?

In NHibernate you can start a distributed transaction using ISession.BeginTransaction. Then you do whatever tasks inside and finally commit the transaction when you are done.

I still think SP's are necessary in many places though, with reporting as you mentioned being a good example. There are just certain complex queries that need to be manually adjusted and optimized to get the best execution path for performance.
4/18/2008 9:35 AM | Dave Harris

# re: Has ORM Made Stored Procs Obsolete?

Totally agreed with Dave Harris. D'Arcy when you get back I would totally look into NHib. It's a great tool. It does not make stored procs obsolete - though it does help immensely (my most recent project has no stored procs at all).
4/18/2008 9:54 AM | Justice~!

# re: Has ORM Made Stored Procs Obsolete?

I talked about these arguments times ago here: http://blog.codiceplastico.com/?p=117 4/19/2008 4:18 AM | Emanuele DelBono

Post a comment